Macro export to excel error

[Andy_d]

Hi Internet,

I have tried to create a macro that would export all the data from one of my tables to excel , but keep running into an error message saying 'Cannot modify table structure. Another user had the table open'.

Of course I checked that nothing was open, even shuttind down the computer and going straight to the macro, but the error message keeps popping up.

Im not sure how much of difference it will mke but im using office 2003, but my database is in 'Access 2000 file format'.

Can anyone point to me a google result that ive missed, or offer some advice?

Thanks Andy_d

[ajetrumpet]

have you tried:

• copying the table
• exporting the copied table
• imported the table into fresh database and use that one
• export it out by right-clicking on it
• compacting your db
• check to see if there is not an .LDB file in the same folder that the db is in

[Andy_d]

I have now tried a copy from within the same db and from a entirely new database, and have gotten the same message back.
I can export using right click, but this database has to work for some not very computer literate people, so I was hoping a macro would solve that problem.

Althought im not very experience with DB's myself, so while i searched for a .ldb file i dont know what it is. Also, my compacting do you mean compressing, or is that a different function?

[ajetrumpet]

compact and repair, bud. It's a menu item. In 07 and later, it's in the ribbon somewhere.

try exporting with code as well. see if that works. as an alternative to the macro that is.

[Andy_d]

Right, ive dome the compact and repair, and whilst the error message has disapeared, no data is getting onto my spreadsheet. Both the source and the destination are right, and ive saved, closed and opened both the db and the spreadsheet in a comination of ways.

Also, ive never done any coding before, and im reluctant to start messing around with something i know nothing about when im already ahving problems with something that i know a small amount about.
However if you could point me at a 'the be all and end all guide to coding' ill have a browse and experiment or a while.

[ajetrumpet]

However if you could point me at a 'the be all and end all guide to coding' ill have a browse and experiment or a while.

there isnt one of those. is there ever???

i cant help you past this point unless i see the file. no data coming out of an export operation makes no sense

[Andy_d]

Right, well good news, and bad.
The macro does apparently work. One of my macro's did export some data. But having just retired all of them, none now seem to work. Am getting quite confused by this.
I will attach a zip of both the db and the destination spreadsheet.
*please note that some fields are empty because they would otehrwise contain confidential information*

Thanks
Andy_d

[ajetrumpet]

so when you say nothing is happening, what do you mean?? plenty of things happened for me. ISAM error to begin with...

are you aware that the macro exporter creates a new sheet in your wkbk for the exported table?? the things I changed to make it work:

• spreadsheet type = ms excel 97 - 2003 type
• filename = c:\dblink.xls

you had the type set at 3.0 and the file path set without the extension on the file name. I doubt that would work.