access macro - export report to excel

i am writing a macros in access that exports a report to excel and attaching that macro to a button on a form. because this file in excel will be overwritten each month, I would like to add in the macro a step that saves over the original file without the "do you want to replace the existing file" message... Is this possible.

thanks !
rob kormoski

Reply 1 : access macro - export report to excel

is to delete it before saving it. That can be done in a single macro step before you open and save the report.

Steps:
1. Make a batchfile, for example c:mydatabasedatadelete.bat with just one line in it: del c:mydatabaseexportmytable.xls
Use Notepad to make and save this batchfile.
2. Make a Runapp-step in the macro with full path to batchfile as parameter.

This will execute the batchfile, and the batchfile will delete the workbook.

There must be other - more elegant - solutions, but I just made and tested this (Windows XP, Access 2000) in 5 minutes or so, and I'm content with the way it works. I leave it to others to find something better.

Note that exporting a table as a spreadsheet overwrites an existing one without asking. But as you're talking about a report that's not relevant. By the way, can you tell me the code to do so?

Hope this helps.


Kees

Reply 2 : access macro - export report to excel

I am still trying to figure out the code (I am new to access macros) - however was wondering if you could help me with this... when I export to excel, can I specify a worksheet (since I have multiple worksheets that I do not want to delete) ? right now it seems as if it deletes everything in the file and then exports the report and ONLY that report is in the worksheet, but I need all my original worksheets (I have cell referencing to put my report in the proper format)

thanks again !

Reply 3 : access macro - export report to excel

Rob,

No, you can't export to multiple worksheets in a workbook. Each export creates its own xls-file. That's one of the things your system design needs to take care of.

Kees

Reply 4 : access macro - export report to excel

Two different methods are outlined in http://reviews.cnet.com/5208-7813-0.html?forumID=29&threadID=134445&messageID=1515786

They are for Excel, but I think the Access macro commands cover about the same thing, albeit in a different syntax. Both Sendkey and Setwarnings (I hope I got the correct English terms, but you'll find out) are valid macro commands. The documentation for Setwarnings explicitely states it suppresses all dialog windows. If it works, that's a far superior solution.

Kees

Reply 5 : access macro - export report to excel

thanks! your post got me on the right track for a similar situation, but you can do this without batch file using "cmd.exe" command line parameters:

RunAPP : cmd /c del c:userspatric~1desktopReport.xls

the "/c" switch runs command then exits CMD.. "/k" would run and leave open.

if "cmd.exe" is not in the system path (should be), you could add the path in the command "c:windowssystem32cmd /c del ..."

cheers