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