Hey, Scripting Guy! How Do I Run an Office Excel Macro on Multiple Workbooks?

Avatar

Hey, Scripting Guy! Question

Hey, Scripting Guy! I don’t know about you, but I love Microsoft Office Excel macros. Come Valentine’s Day, my computer gets a card. Office Excel macros save me so much time, it is just unbelievable. There is just one problem, and that is that I have to open Office Excel and manually run the macro. While this is not normally a problem, it just became one. My pointy-headed boss (PHB) just decided that he wants a new cover sheet for the TPS report—not literally, but figuratively. Let me explain: There is a folder with hundreds of Office Excel workbooks in it, and he wants this macro to be run on all of them. The macro itself is a company macro and it automatically exists in all workbooks. The PHB wants me to open up all the workbooks, click the Macro button, and run this stupid macro. It will take me hours and hours to do it. I told him we should hire a temporary worker to do it, and he said, and I quote, “No rush. You can do it in your spare time. As long as it is done before the end of the quarter, we will be fine.” The end of the quarter is next week, and I have no spare time! Can you please help me?

– GG

Spacer Hey, Scripting Guy! Answer

Hi GG,

As much as I would like to help, I am not going to come over and help you open a bunch of workbooks, click a macro button, and run the macro. I think I would die of boredom in less than 15 minutes. You see, one reason I became a scripting guy is because I have a low tolerance for banality, and I despise time-wasting, mind-numbing activities. So rather than coming over and helping you click buttons, would it be okay if I write you a script?

Believe it or not, we can access Office Excel macros from a script. We already know that we can use Windows PowerShell to find particular types of files, so all we need to do is write a script that will search your folder for Office Excel workbooks, and then we can use the Office Excel automation model to open the workbooks and run the macro. Pretty cool huh?

The Office Excel automation model is documented here on MSDN. Here are previous “Hey, Scripting Guy!” articles that illustrate using VBScript to automate Microsoft Excel. The Office space archive also has numerous examples of using VBScript with Office Excel. You can also find lots of examples of automating Office Excel in the Script Center Repository. Perhaps I also saved the best for last. The Community-Submitted Scripts Center has a ton of scripts that automate Office Excel. Here is the direct link to that gold mine of information. Look in the Microsoft Office section. If you are new to using Microsoft PowerShell, you can get a jump-start on it with the resources in the Windows PowerShell hub.

Here is today’s script:

$excel = new-object -comobject excel.application
$excelFiles = Get-ChildItem -Path C:\fso -Include *.xls, *.xlsm -Recurse
Foreach($file in $excelFiles)
{
 $workbook = $excel.workbooks.open($file.fullname)
 $worksheet = $workbook.worksheets.item(1)
 $excel.Run("CreateChart")
 $workbook.save()
 $workbook.close()
}
$excel.quit()

The script begins by creating an instance of the Excel.Application object. This is the main object that you use when you are working with automating Office Excel. From the Excel.Application object, we gain access to all the other items that are needed for automation. To create an object in Windows PowerShell, we use the New-Object cmdlet. This cmdlet has an option of creating a COM object, and this is what we need here. This is the same object you would use if you were working in VBScript. Because we will need to use the Excel.Application object later in our script, we store the object in a variable we call $excel. This is seen here:

$excel = new-object -comobject excel.application

Next we need to obtain a list of all the .xls and .xlsm files in the folder, and to do this we use the Get-ChildItem cmdlet. If you are not familiar with it, a file with an .xlsm extension is a macro-enabled Office Excel workbook. This file type was introduced in Microsoft Office 2007. To limit our search to only these two types of files, we use the include switch with the Get_ChildItem cmdlet. The include switch is allowed to receive an array of file types, and we can therefore use more than one file type separated by commas. The recurse parameter is used to tell Get-ChildItem to burrow its way down through the folder. The resulting collection of file objects is stored in the $excelFiles variable. This line of code is shown here:

$excelFiles = Get-ChildItem -Path C:\fso -Include *.xls, *.xlsm –Recurse

We then need to work our way through the collection of files stored in the $excelFiles variable. Whenever you hear the word collection you should think foreach, or in VBScript you should think foreach next. The variable $file is used to allow us to access a single file at a time from the collection. This line of code is seen here:

Foreach($file in $excelFiles)

Now we need to open the Office Excel workbook. To do this, we use the open method from the workbooks collection. We get the workbooks collection by using the workbooks property from the Excel.Application object. When we use the open method, we need to supply the path to the workbook we wish to open. The open method returns a workbook object, which we store in the variable $workbook as seen here:

$workbook = $excel.workbooks.open($file.fullname)

We then use the worksheets property from the workbook object to obtain a collection of worksheets. We then use the item method to return the first worksheet in the collection. We store that worksheet in the variable $worksheet. This is seen here:

$worksheet = $workbook.worksheets.item(1)

After we have selected a specific worksheet, we use the run method from the Excel.Application object to run a specific macro. The macro is seen here:

Image of the macro being run

 

The run method can be used to run either a macro or a function. This line of code is seen here:

$excel.Run("CreateChart")

When you run the script, you will not see any output on the screen. But if you open your Office Excel workbook, you will see that the macro ran and created a nice chart. This is seen here:

Image of the results of the macro having been run

 

Now that we have run the macro, we want to save the workbook and then close it out:

$workbook.save()
$workbook.close()

We continue looping through the collection of workbooks until we are done. When we are done, we call the quit method to end the Office Excel process:

$excel.quit()

Well, GG, that is about it. I sincerely hope this script will ease your workload and help ease some of the tension you have at work. Sorry I do not have a script to transform a pointy-headed boss into a normal human being. See you tomorrow. Until then, peace!

1 comment

Leave a comment

  • Avatar
    Abdelkader Saheb

    Hello

    I have an excel file with different worksheets, whose content is regularly updated with a single macro.
    The user have to click on each worksheet in turn and execute the macro. The macro asks for a different csv-file for each worksheet, which have to be selected from file Folder manually.

    Here is what the script have to do in background:
    – read the workbook from a csv-file “excelfile1.csv”
    – read the worksheets from a csv-file “worksheets.csv” with list of worksheets (company1, company2, company3, etc…)
    – activate worksheet named “company1”, start macro, macro asks for file named “accounting-company1.csv”. Macro then reports that worksheet with the name “Company1” has been updated.
    – activate worksheet named “company2”, start macro, macro asks for file named “accounting-company2.csv”. Macro then reports that worksheet named “Company2” has been updated.
    – activate worksheet named “company3”, macro start, macro asks for file named “accounting-company3.csv”. Macro then reports that worksheet named “Company3” has been updated. Etc….

    I am grateful for any support 😉

    Thank you

    AEK

    Here my script who Need to be corrected:

    # Open Excel file
    $excel = new-object -comobject excel.application
    $filePath = “u:\Test_CompanyAccouting.xlsm”
    $workbook = $excel.Workbooks.Open($FilePath)
    $excel.Visible = $true
    # Get Names of existing Worksheets

    $worksheets = Get-ChildItem -Path u:\List_WorksheetsNames.csv | ForEach-Object {$_.name}

    ForEach ($Worksheet in $Worksheets) {
    $WS = $Workbook.Worksheets.Item($Worksheet)
    $WS.Activate()
    $excel.Run(“MacroAccounting”)
    ##Ask for file to choose from File Folder ???? HOW
    } #End ForEach

    $workbook.save()
    $workbook.close()
    $excel.quit()
    Write-Host “Closed Excel”