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

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

  • Comments 9
  • Likes

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

SpacerHey, 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!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • I'm trying to do this as well - except I want to run a macro stored in my personal.xls ?? Anyone offer any insite onto this?

  • Hi Scripting Guy - following the first comment: if we have one macro stored in our personal folder - hot to change the code so it would apply the macro from the personal macro file to all excel spreadsheets? Do they need to be placed in one folder, or can be placed in subfolders?

  • I solved this problem a few years ago.  I create an "on time" macro which executes and exits the Excel workbook if the escape button is not pressed in 15 seconds.  That way I just execute the workbook_open macro every time the workbook is started and exit if the escape button is pressed.  The command line is simply "abc.xls"  Of course I also disable screenupdating and some error popups and stuff ...

  • Hi Scripting Guy!  I am working on a similar script for our sales dept.  I have the script open the spreadsheet and run the macro just fine, but my macro prompts for the RSM number, once entered it refreshes the data and then saves a copy into a folder for that sales person. The script then repeats the same process until it has ran for every RSM.  Right now I am using a combination of autoit and powershell to accomplish this.  Is there a way to answer the prompts in powershell instead of having to rely on autoit?

  • Hi,

    Your tutorial above worked well. However, I've encountered a problem with my macro.

    The macro that I'm running performs some intense statistical calcultions. If I execute the macro "manually" it works 100% however when I try execute it via a powershell script ... it doesn't work completely.

    I suspect the problem lies in the way the excel object is launched. Is there a way to compare the excel environments to see what's different? Is there another way of calling an excel macro from powershell?

  • I've used this script as a base for something I'm creating and ran into similar issues as other commentors; John and Olgierd as my macro was in personal.xlsb. Here is what I came up with, hopefully it will help others:

    $excel = new-object -comobject excel.application

    $excelFiles = Get-ChildItem -Path [FOLDER] -Include *.xlsx, *.xlsm -Recurse

    $macrobook = $excel.workbooks.open("C:\Users\[USERNAME]\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB")

    Foreach($file in $excelFiles)

    {

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

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

    $excel.Application.Run("PERSONAL.XLSB![MACRO]")

    $workbook.save()

    $workbook.close()

    }

    $macrobook.close()

    $excel.quit()

  • After I run the script I am left with excel.exe in task manager. Any idea how I persuade Excel to close properly?

  • thanks

  • Peter: This might help with getting rid of the Excel processes:
    http://technet.microsoft.com/en-us/library/ff730962.aspx