A macro to open files, refresh data, and close file in background?

  • Thread starter Thread starter denisecook
  • Start date Start date
D

denisecook

Hello, I have a Master Data Collection sheet which pulls external dat
from several worksheets which also pull external data from othe
worksheets. The problem is that none of the worksheets refres
themselves unless they are opened, so the Master is never fully curren
unless I sit and open each of 13 linked worksheets to refres
themselves first, THEN open the Master. Ideally, I would like to onl
open the Master and then run a macro that will open each of the 1
linked sheets, refresh their data, and then close them. I could se
the Master to manual refresh, then I could run the macro first, the
refresh the Master. Any ideas? :) Thanks in advance
 
AFAIK you must open (but it does not have to be active) the workbook first
Sub refreshquery()
Workbooks("Yahoobasic.xls").Sheets("Sheet1").QueryTables(1).Refresh
End Sub

I just recorded this
Sub Macro14()
'
' Macro14 Macro
' Macro recorded 5/3/2004 by Don Guillett
'

'
Workbooks.Open Filename:="C:\yourfolder\yahoobasic.xls"
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

so now you can make a loop to do a for/each
UNTESTED
for each c in selection' list of typed workbook names
Workbooks.Open Filename:=":\yourfolder\" & c & ".xls"
Workbooks(c &".xls").Sheets("Sheet1").QueryTables(1).Refresh
BackgroundQuery:=False
ActiveWorkbook.Save
ActiveWindow.Close
next c
end sub
 
Back
Top