Export to Excel Problem

[Len Boorman]

I have a database that I originally wrote whilst employed. That employer has now asked me to do some mods which is nice even after 4 years

The database was on a netwrok and to do the mods I have taken a copy and am working at home. No real problems until I tried to export some data to excel

Code as below

Dim stDocName1

stDocName1 = "tbl_Data_Set_01"

DoCmd.TransferSpreadsheet [acExport], , stDocName1, "J:Archive\Access Applications\Defects\Defect_Docket_xl_Outputs\Bad Goods.xlsx", 0


Now I have used this code before and it works but this time I am getting an error message

Run time error 3027
Cannot update Database or Object is read only.

Now I have ferreted about a bit and found
I can add records to database so database cannot be read only. I have repeatedly recreated the table referred to in the code with no problem

Th destination folder "appears" to be read only according to its properties but I have added files to this folder manually without a problem and it appears that the "read only" indicator is a bit of a red herring

Any thoughts as to why the problem exists and how to cure will be appreciated

Thanks

Len B

 

[Len Boorman]

Solved it

Nothing at all to do with read only

It appears that there is a limited range of file types that can be exported.

It appears type xlsx cannot be exported but xls can

Changed file extension to xls and problem went away.

Error messages are not always correct in the error they detect or at best give misleading information.

hmm should have thought of that one

Len B

 

[spikepl]

Not quite. XLSX is just fine, but you have not specified which Excel in the second parameter. AS far as I recall you needto write 10 there.

 

[Len Boorman]

Cheers spikepl

Made a note of your comment. Will give it a try

Error messages huh. need to know what errors you get in error messages

L

edit
Actually got 2003, 2007 and 2010 users to consider at the moment so will stick to xls for the time being until all are upgraded. Will try you info on a suitable user