Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

access macro - export report to excel

Dec 13, 2005 2:00AM PST

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

Discussion is locked

- Collapse -
My solution ...
Dec 13, 2005 5:57AM PST

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:\mydatabase\data\delete.bat with just one line in it: del c:\mydatabase\export\mytable.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

- Collapse -
..how about this...
Dec 13, 2005 6:18AM PST

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 !

- Collapse -
Re:xporting
Dec 13, 2005 3:58PM PST

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

- Collapse -
Other solutions.
Dec 13, 2005 4:04PM PST

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

- Collapse -
easiest method
Apr 17, 2011 3:39AM PDT

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:\users\patric~1\desktop\Report.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:\windows\system32\cmd /c del ..."

cheers

- Collapse -
(NT) Nice addition!
Apr 17, 2011 3:45AM PDT