Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another query about hiding columns with Macros | Excel Discussion (Misc queries) | |||
Strip workbook of macros, VBA, MS Query, etc. | Excel Discussion (Misc queries) | |||
Forcing users to enable macros | Excel Programming | |||
Exporting macros to a different users PC query | Excel Programming | |||
Forcing Macros to Run (Else Deny Access to Workbook) | Excel Programming |