Macro to refresh data from different files
 

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

Macro to refresh data from different files
 

On Tue, 18 Mar 2008 06:36:03 -0700, Cam
wrote:
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?
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Macro to refresh data from different files
 

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" wrote:

On Tue, 18 Mar 2008 06:36:03 -0700, Cam
wrote:
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?
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Macro to refresh data from different files
 

On Wed, 19 Mar 2008 11:24:08 -0700, Cam
wrote:

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..

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

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
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com