Pass variable from PowerShell to VBA macro?

Question

I have a PowerShell script that automates some report processing, and I have numerous separate macros to perform very similar autofilter functions, but on different criteria.

Is it possible to pass this criteria from PowerShell into the macro? I could then just have the 1 macro.

ColNum = Application.Match("*header", Range("A1:Z1"), 0)

If Not IsError(ColNum) Then
    ActiveSheet.Range("A1").AutoFilter Field:=ColNum, Criteria1:="$criterafromPowerShell", Operator:=xlAnd
End If

I currently do a similar thing but the other way round, the output from these macros I insert to the workbook and extract back to PowerShell like so:

$counts = $workbook.worksheets.item(2)
$xRows = $counts.cells.item(1,1).Text
$yeRows = $counts.cells.item(1,2).Text

I concede I could possibly do this the other way round, and insert the text I want to use in a worksheet after the file is opened and before the macros are run, then pick it up inside the macro... but it seems messy.

Any suggestions?

You'd have to make a UDF that takes an argument, make it public scope, and pass the variable using the Application.Run method. - I think... — SierraOscar
– SierraOscar, Commented Oct 1, 2015 at 15:26
So create a function inside Excel/VBA and call it from PowerShell with Application.Run? I'll do some research — Chris
– Chris, Commented Oct 2, 2015 at 9:22
Yup, iirc the .Run() method is part of the Excel App class so you would do something like $xlApp.Run("'myWorkbook.xlsm'!Module1.MyUDF()", $arg1, $arg2) - Syntax may not be correct but should give you an idea of what I'm saying — SierraOscar
– SierraOscar, Commented Oct 2, 2015 at 9:37
Application.Run (Excel) - MSDN article should explain better than I'm doing! — SierraOscar
– SierraOscar, Commented Oct 2, 2015 at 9:39

SierraOscar · Accepted Answer · 2015-10-02 10:24:34Z

4

Example:

$xlApp = New-Object -ComObject "Excel.Application"
$xlApp.Workbooks.Open("C:\Users\MacroMan\Documents\MyMacroWorkbook.xlsm")
$returnValue = $xlApp.Run("'MyMacroWorkbook.xlsm'!GenerateString", 6)
Echo $returnValue
FOOBAR

$returnValue = $xlApp.Run("'MyMacroWorkbook.xlsm'!GenerateString", 3)
Echo $returnValue
FOO

In the "MyMacroWorkbook" (VBA):

Public Function GenerateString(strLength As Integer) As String
    GenerateString = Left("FOOBAR_SOMETHING", strLength)
End Function

answered Oct 2, 2015 at 10:24

SierraOscar

17.7k6 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Where were you about 10 minutes ago I've just worked it out :) thank you for including this example, I'll likely come back and reference it! My particular example the function has the variable in quotes, took me a while to work out the " + varName + " etc. I'll add it on as well.

SierraOscar · Accepted Answer · 2015-10-02 09:40:25Z

I use the below code to pass two arguments from my VBscript to open a prticular workbook and launch required macro, you could pass the variable you want as a variable to the sub, i.e. Sub test(passedvariablehere) and make the sub to account for it:

'Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

If (Wscript.Arguments.Count < 2) Then 
  Wscript.Quit 
End If 

'retrieve the arguments

Dim strWorkerWB 
strWorkerWB = Wscript.Arguments(0) 

Dim strMacroName
strMacroName = Wscript.Arguments(1) 

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application") 

myExcelWorker.Application.Visible = True

' Open the Workbook specified on the command-line 
Dim oWorkBook

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)

on error resume next 
' Run the calculation macro
myExcelWorker.Run strMacroName
if err.number <> 0 Then
   ' Error occurred - just close it down.
    oWorkBook.Close
    Set oWorkBook = Nothing
    myExcelWorker.Quit
    Wscript.Quit 
End If
err.clear

oWorkBook.Close
Set oWorkBook = Nothing

myExcelWorker.Quit

Set myExcelWorker = Nothing
Set WshShell = Nothing

on error goto 0

First argument is the fullpath/name to workbook, second argument is the sub name

So how I would call this from within the PowerShell script? I don't tend to run it from the command line but directly from the ISE. I'll have a play and see how far I can get.

Collectives™ on Stack Overflow

Pass variable from PowerShell to VBA macro?

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related