How do I refresh external data in Excel via linked table in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel file that pulls external data from our Oracle application via
a web query created by a third-party vendor, Noetix. I have also created an
Access database that has a link to this Excel file. I would like to have
Access refresh the external data in Excel before using that linked table. As
it is right now, the user has to open the Excel file, refresh the data, save
and close the file, open Access and proceed with processing data.
 
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
 
Back
Top