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

[msanger]

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.

[danp129]

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.