programmatically refreshing data

  • Thread starter Jon C. Munson II
  • Start date
J

Jon C. Munson II

hi folx!

i've got an Access application that calls excel to download a table of data
from an odbc source. the reason, in case you care to know, is that
particluar table has too many indexes for Access to absorb, and excel is
dropping them rather nicely so i can access the data.

here is the call:

Set oApp = CreateObject("Excel.Application")
Set oDoc = oApp.Workbooks.Open(CurrentProject.Path & "Customer.xls")
oDoc.RefreshAll
DoEvents
oDoc.Close SaveChanges:=True
DoEvents
CurrentDb.TableDefs("Customer").RefreshLink

it doesn't work. it functions, sure, but the data is not refreshed and no
errors are reported.

anybody got any clues/suggestions/solutions here?

thanks in advance!

jon
 
D

Dick Kusleika

Jon

How many external data tables are in the Excel workbook? One?

It is likely that the querytable is refreshing in the background because its
BackgroundQuery property is set to True. When this happens, your code
continues to run even though the query hasn't completed its refresh. If you
only have one querytable in the Excel workbook, then change

oDoc.RefreshAll

to

oDoc.Sheets(1).QueryTables(1).Refresh False

assuming that the querytable is on the first sheet. Using the False
argument will suspend code execution until the querytable is refreshed. At
least that's how it works in Excel, I don't know if it works the same via
automation, but I suspect it does.
 
T

Tom Ogilvy

Suspect the backgroundquery property is set to true, so the refresh is not
completed.

Either edit the query to change it to false, or don't use refreshall. Use
the refresh command for the specific table and supply the backgroundquery
argument

oDoc.worksheets("Data").QueryTables(1).Refresh BackgroundQuery:=False

if you have multiple queries, then loop through them.
 
J

Jon C. Munson II

yup, that did it...thanks much!

jon

Dick Kusleika said:
Jon

How many external data tables are in the Excel workbook? One?

It is likely that the querytable is refreshing in the background because its
BackgroundQuery property is set to True. When this happens, your code
continues to run even though the query hasn't completed its refresh. If you
only have one querytable in the Excel workbook, then change

oDoc.RefreshAll

to

oDoc.Sheets(1).QueryTables(1).Refresh False

assuming that the querytable is on the first sheet. Using the False
argument will suspend code execution until the querytable is refreshed. At
least that's how it works in Excel, I don't know if it works the same via
automation, but I suspect it does.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com
Click to expand...
 
Top