Automate excel macro in all worksheets 1

[DanDevNull]

I have a simple script that opens an excel instance, runs a macro and then saves and closes the workbook. Currently it only does it on the first worksheet and I need it to go though the workbook and run the macro on all worksheets. Here's the code:

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colFileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='C:\temp\Test'} Where " _
& "ResultClass = CIM_DataFile")

Set objExcel = CreateObject("Excel.Application", strComputer)
objExcel.DisplayAlerts = False

For Each objFile In colFileList
If objFile.Extension = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Run("Macro1")

objWorkbook.SaveAs objFile.Name
objWorkbook.Close
End If
Next

objExcel.Quit


Thanks for the help.

 

[PHV]

Replace this:
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Run("Macro1")
With this:
For Each objWorksheet In objWorkbook.Worksheets
objWorksheet.Activate
objExcel.Run("Macro1")
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DanDevNull]

Thank you, PHV. That did the trick. And it was much simpler than the route I thought I'd have to take.