Updating multiple spreadsheets

[Famous Mortimer]

My work colleague has about 80 sheets, which he's got set up to update and automatically add data from one large central data source (I'm not sure about that side of it, but anyway).

At the moment, he has to open each one manually, hit yes on "do you want to update?", then save it. I think there must be a quicker way of doing this, but after a few hours of fruitless trial and error and some searching, I find my way, via a recommendation from another forum, to here.

So, I wondered if anyone could help?

 

[Trevor G]

Welcome to the Forum,

First question is which version of Excel are you using?
Are all the sheets in the same workbook?
Are they linked to an external workbook/other application?

 

[Famous Mortimer]

Sorry, I feel like an idiot, I knew there was something I'd missed out.

Office 2003

All the sheets are separate, so he's having to find the folder they're in, open them individually, press the update button (a macro?) and then save again.

They're linked to an external workbook. Like, the macro knows where to look to update the information, and it's the same place every day.

 

[Trevor G]

OK then give this ago.

In a new workbook use Alt + F11 to go into VBA, then use the Insert Menu and Select Module.

Copy and Paste this code in and adjust as necessary.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#007F00">'The follwoing code will look at all workbooks in a folder</SPAN><br><SPAN style="color:#007F00">'Created by Trevor G Jul 2011</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>MyPath = "M:\Access Files\" <SPAN style="color:#007F00">'Change the path</SPAN><br>MyFile = Dir(MyPath)<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN><br>Workbooks.Open MyPath & MyFile<br><SPAN style="color:#00007F">Call</SPAN> CreateProjStatMenubar <SPAN style="color:#007F00">'Change the macro name</SPAN><br>ActiveWorkbook.Save<br>ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>