Macro to refresh data from different files

[Cam]

Hello,

I have a standard chart and data on several files, 1 file for each product.
The data for each file is imported from Access. I created a master file which
link the data from each product file. How do I create a macro to go to all
the product files, to refresh all the data which in turn will update the
master file? Thanks

 

[Dick Kusleika]

I have a standard chart and data on several files, 1 file for each product.
The data for each file is imported from Access. I created a master file which
link the data from each product file. How do I create a macro to go to all
the product files, to refresh all the data which in turn will update the
master file? Thanks

Are they all in the same directory? Do they all have similar names? How
would you identify which files to open?

 

[Cam]

Hi Dick,

All files are in a same folder on the same network directory, but they all
have a different file name. So, instead of having to open all the files then
click a fresh all button on each file, I want a master macro to go in each
file and refresh the pivot tables, etc..

 

[Dick Kusleika]

Hi Dick,

All files are in a same folder on the same network directory, but they all
have a different file name. So, instead of having to open all the files then
click a fresh all button on each file, I want a master macro to go in each
file and refresh the pivot tables, etc..

Cam: This isn't tested, so let me know if you run into troubles with it.
Change sFldr to point to the right place.

Sub RefreshAllFiles()

Dim sFldr As String
Dim ws As Worksheet
Dim pt As PivotTable
Dim sFile As String
Dim wb As Workbook

sFldr = "\\Server1\MyFolder\*.xls"

sFile = Dir(sFldr) 'get the first xls file

Do Until Len(sFile) = 0
Set wb = Workbooks.Open(sFile)
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
wb.Save
wb.Close False
sFile = Dir 'get the next xls file
Loop

End Sub