Not knowing the exact process you use to refresh the data, I can only give
you a rough outline.
First, I would go into Excel, start the Macro Recorder and then perform the
refresh, then stop the Macro Recorder. Hopefully this will capture the
programmatic steps to perform this.
Next, go to Access and create a reference to Excel in your References
(Tools>References in any code window).
Lastly, create an Event Procedure for a button. In this subroutine, create
an Excel Automation object in code, then paste the code recorded in the
macro there. The code won't work by itself until you use the Excel object
as a prefix.
For an example, let me show code that just opens then saves the file as a
different name:
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
' open a workbook
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\the\path\to\your\File.xls")
'PASTE YOUR CODE HERE
objXLBook.SaveAs ("C:\the\path\to\your\File2.xls")
objXLBook.Close
Now, when I say add a prefix, I mean you'll want to add objXLBook before
each command. For instance, if you record the SaveAs in the Macro Recorder,
it will look something like this:
ActiveWorkbook.SaveAs Filename:="I:\Book1.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
To make it work as I did above, I had to replace ActiveWorkbook with the
Excel object:
objXLBook.SaveAs Filename:="C:\the\path\to\your\File.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
(Actually, as you can see I used a shorthand version, but the one above
would have worked just as well.)
Does this help?
--
--Roger Carlson
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L