Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Gum Gum is offline
external usenet poster
 
Posts: 30
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Refresh background query in Excel 2007 F Jones Excel Programming 0 May 1st 08 11:34 AM
File Properties Location retrieve to refresh background query? lireland Excel Programming 1 November 16th 07 03:43 AM
How can I 'Enable Automatic Refresh' for Query Refresh by default Anand Deshpande Setting up and Configuration of Excel 0 December 10th 06 05:47 AM
Timing of automatic query refresh and macro pivot table refresh dutty Excel Programming 2 December 1st 04 08:19 PM
Excel does not close from VB!! (when i refresh Refresh query with BackgroundQuery:=False) Anant[_2_] Excel Programming 1 August 6th 03 04:22 AM


All times are GMT +1. The time now is 05:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"