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("FiguresPlannerQ uery").Refresh
Worksheets("AMFPData").QueryTables("AMFPQuery1").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery2").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery3").R efresh
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("FiguresPlannerQ uery").Refresh
Worksheets("AMFPData").QueryTables("AMFPQuery1").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery2").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery3").R efresh
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("FiguresPlannerQ uery").Refresh
Worksheets("AMFPData").QueryTables("AMFPQuery1").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery2").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery3").R efresh
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