Forcing MS Query to run before macros
 

My basic question is: when you open a workbook which uses Microsoft Query
from another application (Access), does the automatic update of the query
run? If it doesn't, how can I force the issue before my macros run?

The automatic query appears to not run when having Access open it. If I open
the same file in Excel directly, the query runs.

Background :

A coworker set up a spreadsheet which automatically refresehes its queries
opon opening. At least when you open it in Excel.

My role is to automate the opening of this spreadsheet from an Access macro,
run the two macros in Excel, save and close Excel, back to Access - which
I've done.

The apparent problem is the macros run before Microsoft Query does its
thing. As I am walking the macros and it gets to each worksheet in the
workbook I am now including the code "Selection.QueryTable.Refresh
BackgroundQuery:=False"

Is there a better of doing this or is this acceptable? This appears to work
but I just want to be sure that it is correct.

The whole function (called by a loop) is below.

Function Templates(TemplateName)
Sheets(strTemplateName).Select 'select this worksheet
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A65536").Select 'goto last row possible on sheet
Selection.End(xlUp).Select 'go up to the first non-blank cell
lngRows = ActiveCell.Row 'get the row number of the active cell
If lngRows = 1 Then GoTo errNoRecords 'if the row number is 1 then there
are no records so skip this template and move on
Range(Cells(2, 1), Cells(lngRows, 10)).Select 'select range to copy
Application.CutCopyMode = False 'clear clipboard
Selection.Copy 'copy data
Sheets("Summary").Select 'go back to Summary sheet
Range(strLastRow).Select 'select first data row and column
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False 'paste the values
Range("A65536").Select 'go to last row possible on sheet
Selection.End(xlUp).Select 'go up to the first non-blank cell
lngLastRow = ActiveCell.Row 'get the row number of the active cell
lngLastRow = lngLastRow + 1 'add one which becomes row for next paste on
summary sheet
strLastRow = "A" & lngLastRow 'range address to make next paste on
summary sheet
errNoRecords:
strFNameData = "Number of Records for '" & TemplateName & "' = " &
(lngRows - 1) & vbCrLf 'create string to be written to text file
Print #1, strFNameData 'print to text file
End Function

Forcing MS Query to run before macros
 

I found the command ActiveWorkbook.RefreshAll in the object browser. It
supposed to refresh all of the external data for the workbook.

This appears to do the trick.

"Dkline" wrote in message
...
My basic question is: when you open a workbook which uses Microsoft Query
from another application (Access), does the automatic update of the query
run? If it doesn't, how can I force the issue before my macros run?

The automatic query appears to not run when having Access open it. If I
open
the same file in Excel directly, the query runs.

Background :

A coworker set up a spreadsheet which automatically refresehes its queries
opon opening. At least when you open it in Excel.

My role is to automate the opening of this spreadsheet from an Access
macro,
run the two macros in Excel, save and close Excel, back to Access - which
I've done.

The apparent problem is the macros run before Microsoft Query does its
thing. As I am walking the macros and it gets to each worksheet in the
workbook I am now including the code "Selection.QueryTable.Refresh
BackgroundQuery:=False"

Is there a better of doing this or is this acceptable? This appears to
work
but I just want to be sure that it is correct.

The whole function (called by a loop) is below.

Function Templates(TemplateName)
Sheets(strTemplateName).Select 'select this worksheet
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A65536").Select 'goto last row possible on sheet
Selection.End(xlUp).Select 'go up to the first non-blank cell
lngRows = ActiveCell.Row 'get the row number of the active cell
If lngRows = 1 Then GoTo errNoRecords 'if the row number is 1 then
there
are no records so skip this template and move on
Range(Cells(2, 1), Cells(lngRows, 10)).Select 'select range to copy
Application.CutCopyMode = False 'clear clipboard
Selection.Copy 'copy data
Sheets("Summary").Select 'go back to Summary sheet
Range(strLastRow).Select 'select first data row and column
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False 'paste the values
Range("A65536").Select 'go to last row possible on sheet
Selection.End(xlUp).Select 'go up to the first non-blank cell
lngLastRow = ActiveCell.Row 'get the row number of the active cell
lngLastRow = lngLastRow + 1 'add one which becomes row for next paste
on
summary sheet
strLastRow = "A" & lngLastRow 'range address to make next paste on
summary sheet
errNoRecords:
strFNameData = "Number of Records for '" & TemplateName & "' = " &
(lngRows - 1) & vbCrLf 'create string to be written to text file
Print #1, strFNameData 'print to text file
End Function