Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Query Table causing debug error with Background Refresh pr
I have query tables in a worksheet that I wish to get refreshed each time a
macro is executed. The query tables are set to refresh data from SQL database every minute (via Data...Connections...properties). However, when the macro is executed, there is an error whenever the background refresh process is taking place. Is there some way of getting around this eg. knowing whether the background refresh process is taking place and starting the refresh process after? Is there a better refresh macro that will only refresh the query table and not the entire worksheet? Macro: sub Rfresh() ActiveWorkBook.RefreshAll end sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Query Table causing debug error with Background Refresh pr
hi
it has been my experence never to have a query refresh in the background perticularly if a macro is running and very perticularly if the macro may be using the data being refreshed. you didn't say how many queries you have and i think the refreshall command may be the source of your problems.(i've had problems with that) refreshing every minute may also be a contributing factor if you have a large number of queries. i would increase the amout of time between refreshes if possible and refresh each query 1 at a time and NOT in the background. sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false you would need a refresh line like above for each query. Regards FSt1 "Gum" wrote: I have query tables in a worksheet that I wish to get refreshed each time a macro is executed. The query tables are set to refresh data from SQL database every minute (via Data...Connections...properties). However, when the macro is executed, there is an error whenever the background refresh process is taking place. Is there some way of getting around this eg. knowing whether the background refresh process is taking place and starting the refresh process after? Is there a better refresh macro that will only refresh the query table and not the entire worksheet? Macro: sub Rfresh() ActiveWorkBook.RefreshAll end sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Query Table causing debug error with Background Refresh pr
If you haved an SQL error (the command text line) you will always get the
error on the refresh line. There is no error checking until you execute the refresh. Here is how you debug the problem. There are two tricks 1) Record a macro while performing the query manually. Start macro recorder by going to worksheet Tools - Macro - Start Recording. The perform the query manually from menu Data - Import External Data - then select the type of query you want to perfrom. Then stop recording and use the recorded macro. 2) The recorded macro may not give you all the options you require. So here is the 2nds trick. Do a query with no options. Just get the query to work by using the worksheet menu Data - Import External Data. Now edit the query. Click on a cell where the query returned data. Then go back to the Menu Data - Import External Data. Now there is an option to Edit Query. Get the query editor and press the SQL button. The command text is the SQL statments. Put the SQL options in one at a time. The SQL editor will give you good error messages to help you isolate your problems. Note: When you perform a manual query the last wizard menu give you the option of editing the query. The edit option get you to the same tool as Edit Query option in the menu Data - Import External Data. "Gum" wrote: I have query tables in a worksheet that I wish to get refreshed each time a macro is executed. The query tables are set to refresh data from SQL database every minute (via Data...Connections...properties). However, when the macro is executed, there is an error whenever the background refresh process is taking place. Is there some way of getting around this eg. knowing whether the background refresh process is taking place and starting the refresh process after? Is there a better refresh macro that will only refresh the query table and not the entire worksheet? Macro: sub Rfresh() ActiveWorkBook.RefreshAll end sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refresh background query in Excel 2007 | Excel Programming | |||
File Properties Location retrieve to refresh background query? | Excel Programming | |||
How can I 'Enable Automatic Refresh' for Query Refresh by default | Setting up and Configuration of Excel | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming | |||
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False) | Excel Programming |