Friday, April 9, 2010

VBScript - How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found?

VBScript - How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?

strComputer = "tvsfrank"

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

Set colFileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='C:\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("BoldfaceHeadings")

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

objExcel.Quit

[Via]