Take a look at this:
http://www.rondebruin.nl/copy4.htm

Your code needs to replace the code that is tinted red.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"john" wrote:

As an idea, basic code below will go through specified folder and open each
workbook where you can add more code to perform required action. when done,
it closes & saves file.

Also, If you want to make the operation a scheduled task, what about using
the scheduled tasks utility in the desktop control panel???

Just some ideas - hope helpful

Sub OpenFiles()
Dim wbk As Workbook
Dim strFolder As String


strFolder = "C:\myfolder" '<< change as required

strfile = Dir(strFolder & "*.*", vbNormal)

Do While strfile < ""


Set wbk = Workbooks.Open(strfile)

'do your stuff here


wbk.Close True



strfile = Dir

Loop
End Sub

--
jb


"EZ" wrote:

Hi All,

I'm looking for a piece of code that will open up 14 Excel files that each
file has a 'Data' sheet linked to an Access query plus many other sheets,
Refresh the data sheet and subsequently all the other sheets, then close the
file. I want this for all 14 files. All files are stored in the same folder
on a network drive.

Setup the files on auto refresh everytime it's open is not an option as
these are large models with many complex formula and the data gets updated
once a week, while the users use thses models files every day. So we don't
want our users to wait for a few minutes for the files to recalculate every
time they open a file. I'd rather have it done once a week over night or
early morning.

Can anyone help?

Thanks.
--
when u change the way u look @ things, the things u look at change.

Thank you both John and "ryguy7272".

I will try your methods and get back with you.

Thanks.
--
when u change the way u look @ things, the things u look at change.


"ryguy7272" wrote:

Take a look at this:
http://www.rondebruin.nl/copy4.htm

Your code needs to replace the code that is tinted red.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"john" wrote:

As an idea, basic code below will go through specified folder and open each
workbook where you can add more code to perform required action. when done,
it closes & saves file.

Also, If you want to make the operation a scheduled task, what about using
the scheduled tasks utility in the desktop control panel???

Just some ideas - hope helpful

Sub OpenFiles()
Dim wbk As Workbook
Dim strFolder As String


strFolder = "C:\myfolder" '<< change as required

strfile = Dir(strFolder & "*.*", vbNormal)

Do While strfile < ""


Set wbk = Workbooks.Open(strfile)

'do your stuff here


wbk.Close True



strfile = Dir

Loop
End Sub

--
jb


"EZ" wrote:

Hi All,

I'm looking for a piece of code that will open up 14 Excel files that each
file has a 'Data' sheet linked to an Access query plus many other sheets,
Refresh the data sheet and subsequently all the other sheets, then close the
file. I want this for all 14 files. All files are stored in the same folder
on a network drive.

Setup the files on auto refresh everytime it's open is not an option as
these are large models with many complex formula and the data gets updated
once a week, while the users use thses models files every day. So we don't
want our users to wait for a few minutes for the files to recalculate every
time they open a file. I'd rather have it done once a week over night or
early morning.

Can anyone help?

Thanks.
--
when u change the way u look @ things, the things u look at change.