Open, Update, and Close files automatically

[EZ]

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.

 

[Bob Phillips]

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.

 

[EZ]

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.

 

[john]

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

 

[ryguy7272]

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

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

HTH,
Ryan---

 

[EZ]

Thank you both John and "ryguy7272".

I will try your methods and get back with you.

Thanks.