External Macros in Excel

[RelapseWizard]

I have a monthly report I run, the process is I have about 10 excel sheets each with its own macro I go into each excel sheet to refresh the data through running the macros.

I'm looking at speeding this process up by having one excel sheet that opens all the excel sheets refreshes the macros and saves the excel sheets.... I'm still learning VB coding in excel so know some basic stuff however this would be quite useful....in the long run as always your help is much appreceiated.

 

[DCrake]

The good thing about Excel that does not come with Access is the ability to record a macro. Try and record a macro and edit the code to see what it is doing. Then customise it to suit your needs.

David

 

[RelapseWizard]

I tried recording the macro initialy but for some reason when you go to another excel sheet the macro recording does not acknowledge the other opened sheet so there in lies my predicament......

 

[DCrake]

Can't you just set the automatic updates on when you first open the workbook?

 

[RelapseWizard]

Workbooks.Open Filename:="S:\DIM~8\test.xls"
Application.Run ("test.xls!Refresh_test")
ActiveWorkbook.Close

I think I have found a way of doing it, I've set up a seperate excel sheet, then put this code so the macro "code" opens the file in its location runs the macro within that excel file and then closes the file I'm sure I've cracked it its quite useful...