This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.
In this last post of the series, I am going to mix Excel, PowerShell and VBA. If you weren’t using PowerShell to manage Excel files before, you might have used VBA (Visual Basic for Applications) to do so. Excel is a powerful tool and even this area can be improved a little bit with PowerShell.
For this scenario, we are going to prepare an empty worksheet – as we do not need data at the moment. There will be one minor change comparing to other blog posts. The Excel file extension is going to be XLSM instead of XLSX. The XLSM format support macros, so we need that in order to work with the VBA scripts.
# set location for the files - I am using the temporary folder in my user profile Set-Location $env:TEMP # that is the location of our files #Invoke-Item $env:TEMP # cleanup any previous files - this helps in case the example has been already ran $excelFiles = "ImportExcelHowTo013.xlsm" Remove-Item $excelFiles -ErrorAction SilentlyContinue # create an empty worksheet $null | Export-Excel -Path $excelFiles -WorksheetName HowToVBA
Adding Excel user defined functions with PowerShell
Microsoft Excel has a number of functions. Even though it is so many of them, sometimes the goal we need to achieve requires something more, better suited for our needs. At this point, we can add custom user-defined functions.
In this example it will be a FileName() function that returns a full path of the Excel workbook.
' the VBA code for the user-defined function Public Function FileName () As String FileName = ThisWorkbook.FullName End Function
Let’s go back to our Excel workbook. There is no function in the ImportExcel module to add macros, but we can work directly on the Excel package object. Firstly, open it and create the VBA Project, as currently, VbaProject.Modules returns nothing. Using CreateVBAProject() method we will add default Modules: one for the workbook, and the other the worksheet.
# open the package $excelPackage = Open-ExcelPackage -Path $excelFiles # list VBA modules is empty $excelPackage.Workbook.VbaProject.Modules.Name # create an empty VBA project $excelPackage.Workbook.CreateVBAProject() # once the project is created we can see separate modules: for the workbook and for the worksheet $excelPackage.Workbook.VbaProject.Modules.Name <# ThisWorkbook HowToVBA #>
Next, we are going to add a custom module caled MyModule:
# add new module $module = $excelPackage.Workbook.VbaProject.Modules.AddModule("MyModule") # list modules $excelPackage.Workbook.VbaProject.Modules.Name <# ThisWorkbook HowToVBA MyModule #>
After we’ve created the project and the module, we can now add our user-defined function to the Code property:
# VBA code to be added to the module $vba = @" Public Function FileName () As String FileName = ThisWorkbook.FullName End Function "@ # add the code $module.Code = $vba # save and open the workbook Close-ExcelPackage $excelPackage -Show
Once we open the Excel file we can use the function right away.
This is how it looks like in the VBA Project Exlporer:
Run macro when the file is open (PowerShell edition)
In this example, I am going to show you how to add a macro that starts when you open the Excel workbook. This is possible with Workbook_Open() event.
First, define what is our action: Select a range of cells, and change its background colour, add X to each cell in the range and apply auto size columns involved. The macro is Private, so it will not be visible on the list of Macros from the Excel interface.
$vba2 = @" Private Sub Workbook_Open() Range("A1:R29").Select With Selection.Interior .Color = 65535 End With Selection.FormulaR1C1 = "X" Selection.EntireColumn.AutoFit End Sub "@
Unlike before we are going to use a built-in module called ThisWorkbook to store the VBA code, so it is always in the workbook.
# open the package $excelPackage = Open-ExcelPackage -Path $excelFiles # select the worksheet $excel = $excelPackage.Workbook.Worksheets['HowToVBA'] $module = $excelPackage.Workbook.VbaProject.Modules['ThisWorkbook'] $module.Code = $vba2 Close-ExcelPackage $excelPackage -Show
One note, my environment is setup to prompt before enabling macros hence I need to click the Enable Content button. Once the macros are enabled the one at the startup kicks off.
Being able to add VBA code to the Excel workbook with PowerShell seems like a brain-melting idea, but it is possible and if you even need to add macro to your spreadsheets – you no longer need to click it through.