Open, Update, and Close files automatically
 

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.

Open, Update, and Close files automatically
 

Just do it with the macro recorder on and it will record the macro for you.
For just 14, inline code seems sufficient to me.

--
__________________________________
HTH

Bob

"EZ" wrote in message
...
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.

Open, Update, and Close files automatically
 

Thanks Bob.
I thought of a macro, but I thought may be a hard-coded process would be
better. My preference, since all files are on the network, to have the
process execute automatically at a certain time without me having to click on
a button to run a macro which will drain my station resources!. We currently
have these files on manual calcualtion because each will take more than 5
minutes to complete...

But if there's no better way, I will try a macro... any other suggestions or
directions?

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


"Bob Phillips" wrote:

Just do it with the macro recorder on and it will record the macro for you.
For just 14, inline code seems sufficient to me.

--
__________________________________
HTH

Bob

"EZ" wrote in message
...
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.

Open, Update, and Close files automatically
 

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.

Open, Update, and Close files automatically
 

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.

Open, Update, and Close files automatically
 

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.