Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
programmatically refreshing data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
programmatically refreshing data
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 "Jon C. Munson II" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
programmatically refreshing data
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. -- Regards, Tom Ogilvy "Jon C. Munson II" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
programmatically refreshing data
yup, that did it...thanks much!
jon "Dick Kusleika" wrote in message ... 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 "Jon C. Munson II" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with refreshing data | Excel Discussion (Misc queries) | |||
trying to programmatically change chart data range with vb.net | Charts and Charting in Excel | |||
copying data from an unopened file programmatically | Excel Discussion (Misc queries) | |||
Refreshing Data In Excel / VBA | Excel Discussion (Misc queries) | |||
Help with data importing from txt file to excel programmatically | Excel Programming |