By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

Open Multiple Excel Sheets - Automatic Save and Close - Data Updatation

P: 1
Hi,

I have several excel sheets in Office 2007, which has all kinds of standard calculations, and a consolidated Excel Sheet with totals and rollup of all the other excel files. The issue I am facing is that in order to get the updated totals in the Rollup File, I have to open all the primary excel files one by one, click on update, save and then close them. Once this is done, the rollup file shows the calculations correctly. Is there a VB script or a batch file I can use which will automatically open, update, save and close all the file in one go and the all I have to do is open the consolidated sheet to take the rollup of all the files.
May 18 '07 #1
Share this Question
Share on Google+
1 Reply


danp129
Expert 100+
P: 321
Hi,

I have several excel sheets in Office 2007, which has all kinds of standard calculations, and a consolidated Excel Sheet with totals and rollup of all the other excel files. The issue I am facing is that in order to get the updated totals in the Rollup File, I have to open all the primary excel files one by one, click on update, save and then close them. Once this is done, the rollup file shows the calculations correctly. Is there a VB script or a batch file I can use which will automatically open, update, save and close all the file in one go and the all I have to do is open the consolidated sheet to take the rollup of all the files.
You should start by recording a macro.
While recording:
Open an existing file
*calculate (F9?)
Save the file
Close the file
stop the macro

If excel is set to recalc before save (default in 2003) you may not have to calculate manually before you save and close.

Try to write what you need using the code the macro recorded as an example then post again if you run into an issue.

If the filenames you need to open change on a regular basis and you can't hard-code which files to open then give some examples of the filenames. Include the code you just recorded and cleaned up, and in CODE tags (# sign in message toolbar) as well.
May 18 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.