How to pass a parameter from PowerShell to Excel

I have an excel macro-enabled worksheet that I am using. Excel is primarily being used to store all the macros and VBA scripts, to process another file (CSV). That CSV fileName is currently hardcoded into an Excel variable, for test. The fileName will not be known at runtime. It's being generated by a PowerSheet script, which will then open the Excel file.

I need to open that worksheet from a PowerShell script and pass the (CSV) fileName parameter to the Excel worksheet. I can open the Excel worksheet just fine, and because the test fileName is hardcoded, for test, that works too. But I need to have the PowerShell script pass a fileName parameter to Excel, so the file doesn't have to be hardcoded.

What is the easiest way to do that?

You could use your script to create a small txt file with the file name written in it, in the same directory as the excel file, and use the fileopen event of the Excel file to read the txt file to get the filename.

Originally Posted by Bernie Deitrick

You could use your script to create a small txt file with the file name written in it, in the same directory as the excel file, and use the fileopen event of the Excel file to read the txt file to get the filename.

Clever but not direct. Does that mean there is no way directly to pass a variable?

I tried the file trick and that worked, but if there is a better way, I'd like to know.

Of course you can - if you can't think of a dozen ways to use Excel to do the same thing, you're not really trying

You need a function or macro inside your Excel workbook that can be passed a variable as an input - and that you can run by using the run method - lots of example code out there. Or you could embed the same logic into your macro to figure out the correct parameter... or use an inputbox if a user needs to be involved... or....

Sorry - my assumption is that anyone automating Excel using anything other than Excel Macros has a higher level understanding of coding.

The first result for "passing variable to excel from powershell"

Example:

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

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

Since you want to pass a string

StringVariable = "WhatEverItIsThatYouWantToPAssToExcel"

$xlApp = New-Object -ComObject "Excel.Application"
$xlApp.Workbooks.Open("C:\Users\MacroMan\Documents\MyMacroWorkbook.xlsm")
$xlApp.Run "'MyMacroWorkbook.xlsm'!GetString", StringVariable

In Excel:
'Ooops!
Public Sub GetString(strVar As String)
    DoSomethingMacro strVar
End Sub