Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open and Close a Workbook to Update Links | Excel Discussion (Misc queries) | |||
How to open a list of excel files and get update, then close it? | Excel Discussion (Misc queries) | |||
Macro to run automatically on Open/Close | Excel Programming | |||
automate open/update/close Excel | Excel Programming | |||
automate open/update/close Excel | Excel Programming |