Open, Update, and Close files automatically

  • Thread starter Thread starter EZ
  • Start date Start date
E

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