How to pass a parameter from PowerShell to Excel

[MikeSD]

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?

[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.

[MikeSD]

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.

[Bernie Deitrick]

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....

[MikeSD]

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....

That's a lot of words to say "figure it out yourself", not that that's not the best way.

None of what you have said seems obvious to a non-expert like myself. But maybe there are enough words to give me something to search for.

Currently my parameter is hard coded. I have all the functions to make use of it, when I figure out how to pass the parameter. I've tried accessing the parameter from Excel but no-go. I will figure it out eventually, just trying to save some time. I've searched for this and all the examples I come up with are for ways of passing a parameter to PowerShell from VBA. The other way.

I did try the file trick above and that does work. But that seems more like a crutch than a solution. But a crutch is better than no solution.

[Bernie Deitrick]

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:

Please Login or Register  to view this content.

Since you want to pass a string

Please Login or Register  to view this content.

[MikeSD]

Example:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Since you want to pass a string

Please Login or Register  to view this content.

I did find that example on the internet, but I was confused by the "GenerateString" function inside Excel. The way that appears to work is PowerShell will open the *.xlsm file and call the GenerateString, in the Excel file. I couldn't see how it would give be useful for what I was wanting to do. In fact, the first example doesn't even pass a variable.

But your second example makes it perfectly clear. In fact, that's how I would have expected it to pass a variable. I guess I just didn't try the obvious. I'll give it a try.

Thanks,

[Bernie Deitrick]

The first example does pass a variable - the integer specifying the length of the string to be returned.

Just be careful - I did not run my code using powershell, but I think I got the syntax correct: I've use VB to control Excel extensively, so the structures should be similar - I hope!