Discussion Locked

Auto 'save as' Excel from Access 97

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

HI,

I have many queries in Access '97 that I would like to export (or save..as) excel spreadsheets - automatically.
I am 'not afraid' of coding some VBA scripts, to aid in this task - but could someone help get me started.

I am assuminmg I need to use a bunch of API calls to do this.... eeek !

Thakns
MB

3 replies

by peyison

Hey Mark,

Try this:

Public Sub SaveToExcel(asQueryName As String, asFileName As String)

DoCmd.OutputTo acOutputQuery, asQueryName, acFormatXLS, asFileName, False

End Sub

'call it like:
Sub Test()
SaveToExcel "Query1", "c:\testquery.xls"
End Sub

All you'll need to do is create a loop that passes your query name and associated excel filename to SaveToExcel.

You can also save reports, tables, etc. Just change the first parameter in the call to DoCmd.OutputTo. Access Help as all the available values and other options.

Hope this helps.

This discussion is closed to new replies.