Help! Query Refreshing

Hi All,

I have a excel workbook with a number of tabs, each with an MSQuery from
Access. Each one takes seconds to refresh. I need to refresh most of these
once and then cycle through a list of names, refreshing two more for each
name (these have a parameter looking at an cell to obtain the name), the
workbook is then saved and the process loops round again.

When running the loop once, it works no problem, the data refreshes and has
finished refreshing but once i use the loop properly, the code stops at
'Worksheets("TTP").QueryTables("TTPQuery").Refresh' and tells me it cannot do
this because it is refreshing in the background.

Please can someone tell me either what is wrong with my code OR how to pause
the code until the refresh finishes (but i don't think it is actually the
problem).

Thanks
Emma

Sub CycleThroughPlanners()
Dim iCount As Long
Dim strPlanner As String
Dim strPath As String

Worksheets("Data").QueryTables("DataPlannerQuery1").Refresh
Worksheets("Data").QueryTables("DataPlannerQuery2").Refresh
Worksheets("Figures").QueryTables("FiguresPlannerQuery").Refresh
Worksheets("AMFPData").QueryTables("AMFPQuery1").Refresh
Worksheets("AMFPData").QueryTables("AMFPQuery2").Refresh
Worksheets("AMFPData").QueryTables("AMFPQuery3").Refresh
iCount = 2

Do While Worksheets("Data").Cells(iCount, 12) <> ""
strPlanner = Worksheets("Data").Cells(iCount, 12)
Worksheets("Data").Range("C14").Value = strPlanner

'Workbooks(1).RefreshAll

Worksheets("TTP").QueryTables("TTPQuery").Refresh
Worksheets("Pending").QueryTables("PendingQuery").Refresh

strPath = Worksheets("Data").Range("B16")
Sheets("One To One").Select
Range("A2:AN2").Select
Sheets("One To One").Select
Sheets("One To One").Copy
ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
iCount = iCount + 1
Loop

End Sub

Despite your skepticism it is possible that the 1st query is not yet done to
give the parameters for the second one (it may work fine for the first
iteration due to some overhead time setting up the connection and reading the
table structure and query definition, the Access driver may save some of this
info to help speed the queries the 2nd time around and thus the problem
occurs only if you try to repeat the queries).

Two things to try:
1) To make sure one query finishes before the next begins, either manually
set the querytables to not allow background processing (right click on the
querytable and set the properties) or in code set the .BackgroundQuery
property to false.
2) To wait until a query is done, use a loop to check its .Refreshing
property. But if you do this it is advisable also to have a time check
within the loop to avoid potential problems if the database doesn't respond;
here is an example:

CheckTime = Now() + TimeValue("00:01:00")
While MyQueryTable.Refreshing and CheckTime > Now()
DoEvents
' Add any other processing you want done during the wait time; e.g.
updating a status bar message, etc.
WEnd

--
- K Dales


"Emma Hope" wrote:

> Hi All,
>
> I have a excel workbook with a number of tabs, each with an MSQuery from
> Access. Each one takes seconds to refresh. I need to refresh most of these
> once and then cycle through a list of names, refreshing two more for each
> name (these have a parameter looking at an cell to obtain the name), the
> workbook is then saved and the process loops round again.
>
> When running the loop once, it works no problem, the data refreshes and has
> finished refreshing but once i use the loop properly, the code stops at
> 'Worksheets("TTP").QueryTables("TTPQuery").Refresh' and tells me it cannot do
> this because it is refreshing in the background.
>
> Please can someone tell me either what is wrong with my code OR how to pause
> the code until the refresh finishes (but i don't think it is actually the
> problem).
>
> Thanks
> Emma
>
> Sub CycleThroughPlanners()
> Dim iCount As Long
> Dim strPlanner As String
> Dim strPath As String
>
> Worksheets("Data").QueryTables("DataPlannerQuery1").Refresh
> Worksheets("Data").QueryTables("DataPlannerQuery2").Refresh
> Worksheets("Figures").QueryTables("FiguresPlannerQuery").Refresh
> Worksheets("AMFPData").QueryTables("AMFPQuery1").Refresh
> Worksheets("AMFPData").QueryTables("AMFPQuery2").Refresh
> Worksheets("AMFPData").QueryTables("AMFPQuery3").Refresh
> iCount = 2
>
> Do While Worksheets("Data").Cells(iCount, 12) <> ""
> strPlanner = Worksheets("Data").Cells(iCount, 12)
> Worksheets("Data").Range("C14").Value = strPlanner
>
> 'Workbooks(1).RefreshAll
>
> Worksheets("TTP").QueryTables("TTPQuery").Refresh
> Worksheets("Pending").QueryTables("PendingQuery").Refresh
>
> strPath = Worksheets("Data").Range("B16")
> Sheets("One To One").Select
> Range("A2:AN2").Select
> Sheets("One To One").Select
> Sheets("One To One").Copy
> ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlNormal, Password:="",
> WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
> ActiveWorkbook.Close
> iCount = iCount + 1
> Loop
>
> End Sub

Hi K.

I tried Step 1. the background refresh = false thing and it worked
perfectly! Thanks so much for your help.

Thanks
Emma

"K Dales" wrote:

> Despite your skepticism it is possible that the 1st query is not yet done to
> give the parameters for the second one (it may work fine for the first
> iteration due to some overhead time setting up the connection and reading the
> table structure and query definition, the Access driver may save some of this
> info to help speed the queries the 2nd time around and thus the problem
> occurs only if you try to repeat the queries).
>
> Two things to try:
> 1) To make sure one query finishes before the next begins, either manually
> set the querytables to not allow background processing (right click on the
> querytable and set the properties) or in code set the .BackgroundQuery
> property to false.
> 2) To wait until a query is done, use a loop to check its .Refreshing
> property. But if you do this it is advisable also to have a time check
> within the loop to avoid potential problems if the database doesn't respond;
> here is an example:
>
> CheckTime = Now() + TimeValue("00:01:00")
> While MyQueryTable.Refreshing and CheckTime > Now()
> DoEvents
> ' Add any other processing you want done during the wait time; e.g.
> updating a status bar message, etc.
> WEnd
>
> --
> - K Dales
>
>
> "Emma Hope" wrote:
>
> > Hi All,
> >
> > I have a excel workbook with a number of tabs, each with an MSQuery from
> > Access. Each one takes seconds to refresh. I need to refresh most of these
> > once and then cycle through a list of names, refreshing two more for each
> > name (these have a parameter looking at an cell to obtain the name), the
> > workbook is then saved and the process loops round again.
> >
> > When running the loop once, it works no problem, the data refreshes and has
> > finished refreshing but once i use the loop properly, the code stops at
> > 'Worksheets("TTP").QueryTables("TTPQuery").Refresh' and tells me it cannot do
> > this because it is refreshing in the background.
> >
> > Please can someone tell me either what is wrong with my code OR how to pause
> > the code until the refresh finishes (but i don't think it is actually the
> > problem).
> >
> > Thanks
> > Emma
> >
> > Sub CycleThroughPlanners()
> > Dim iCount As Long
> > Dim strPlanner As String
> > Dim strPath As String
> >
> > Worksheets("Data").QueryTables("DataPlannerQuery1").Refresh
> > Worksheets("Data").QueryTables("DataPlannerQuery2").Refresh
> > Worksheets("Figures").QueryTables("FiguresPlannerQuery").Refresh
> > Worksheets("AMFPData").QueryTables("AMFPQuery1").Refresh
> > Worksheets("AMFPData").QueryTables("AMFPQuery2").Refresh
> > Worksheets("AMFPData").QueryTables("AMFPQuery3").Refresh
> > iCount = 2
> >
> > Do While Worksheets("Data").Cells(iCount, 12) <> ""
> > strPlanner = Worksheets("Data").Cells(iCount, 12)
> > Worksheets("Data").Range("C14").Value = strPlanner
> >
> > 'Workbooks(1).RefreshAll
> >
> > Worksheets("TTP").QueryTables("TTPQuery").Refresh
> > Worksheets("Pending").QueryTables("PendingQuery").Refresh
> >
> > strPath = Worksheets("Data").Range("B16")
> > Sheets("One To One").Select
> > Range("A2:AN2").Select
> > Sheets("One To One").Select
> > Sheets("One To One").Copy
> > ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlNormal, Password:="",
> > WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
> > ActiveWorkbook.Close
> > iCount = iCount + 1
> > Loop
> >
> > End Sub

The main point is that this:

Please Login or Register  to view this content.

no longer works. My implementation includes suggested improvements from many sources (Sleep()) to reduce CPU workload. This, when working (will add finalized solution in edit), will become the answer of the day.
It's not a question, unless someone can solve the last nit. I am still working on it.
I can post in a new thread, but the OP is quite useful as a background.

I opened a new thread on the new issue..
excel-programming-vba-macros/1316362-workbookconnection-refreshing-not-triggering-in-a-while-sleep-1000-loop.html