Automated export to excel and save

[TheEnigma]

Hi there, I hope someone can help. I need to create an event procedure on timer that will automatically take a query, and save it in excel format to a folder on our system. It should save over the exisiting excel document each time it runs. The query name is Intro_Total_Daily_qry.

I know how to set the timer, is there a way to run this so it will action it at specific times only? E.g. 8am, 10am, 12pm, 2pm, 4pm, 6pm.

I am using MS Access 2000.

 

[DrSimon]

I use the scheduler (plus macro) to carry out a similar task daily. There's no reason why you couldn't set up a scheduler entry for each event.
Simon Rouse

 

[TheEnigma]

Hi. I have used the OutputTo macro, but it doesn't give me an option to save over the exisiting document. Any ideas how I get around this? I won't be using the excel document, so I would otherwise be relying on someone else needing to always delete that other document after they have used it, which I would rather avoid.

 

[DrSimon]

I call VBA code from a macro. The VBA code contains the following code where TableName is a table containing the data and FullExcelName is the spreadsheet including the full path. The code in bold deletes the spreadsheet if it already exists. I use SetWarnings to stop any annoying messages from RunSQL.
[green]SQLStr = {SQL to enter data into Tablename}
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FullExcelName) Then fs.Deletefile FullExcelName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TableName, FullExcelName, True
[/green]
Simon Rouse

 

[TheEnigma]

Hi there. I guess I am doing something wrong. Sorry but I don't know VB really, so it is probably something very simple I am missing. It is actually coming from a query, nit a table. Does that make a difference? The qry name is Intro_Total_Daily_qry, and the path that it needs to save to is O:\Intro_TimeToCall.xls. VB editor didn't like the \ in the path, so I removed that, but that still didn't help.


Private Sub Command2_Click()
SQLStr = {SQL to enter data into Intro_TimeToCall_qry}
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(O:\Intro_TimeToCall.xls) Then fs.Deletefile , True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Intro_TimeToCall_qry, O: Intro_TimeToCall.xls , True
End Sub

 

[DrSimon]

You know, I've been giving you a much more complicated answer then you need. I answered the first bit correctly, then I got sidetracked without really considering your problem and reading your response.
I don't use macros much anymore so I missed that you said you had been using 'OutputTo'. You should be using 'TransferSpreadsheet' with 'Intro_Total_Daily_qry' as the Table name. This will create and should overwrite your existing spreadsheet.
Once this macro has been created you can schedule it. You'll need to put something like this into the scheduler to call the macro (look at 'Startup command-line options'
in Help for an explanation):
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\Database Folder\Database_Name.mdb" /x "Macro_Name"
If you use the advanced setting on the scheduler you will be able to run the macro at the times you want. Obviously the PC you run it on has to be running and I've had this running on a server.
Hope this helps
Simon Rouse

 

[TheEnigma]

Thank you so much for your help. I couldn't actually get it running with that whole path above for the scheduler (I have never used the scheduler till today, so probably something silly I am not doing), but there was an easy work around for that. I created a form that on load runs the macro, and then set the autoexec macro to that form!

Thanks that has helped me out heaps!