Run script file from Excel VBA and pass variable to the script file

Question

I have a code that enables me to execute the Windows script file (PowerShell script file) and the code works fine with no problems.

What I am trying to do is to pass a variable to a line in the script file. Example:

This line in the script file

convert Image.png -resize 300x300 -density 300 -quality 100 ImageNew.png

The line has the Image.png but this file name I would like it to be variable and passes a value to it through Excel cell. In other words I have the file name in a cell and I would like to change Image.png to be the value in the cell.

This is the script file:

cd (Join-Path $env:USERPROFILE 'Desktop\Output\')
convert Image.png -resize 300x300 -density 300 -quality 100 ImageNew.png

And this is the VBA code I am using:

Sub TestMe()
    MsgBox ScriptFile
End Sub

Function ScriptFile() As String
    Dim wshShell   As Object
    Dim strCommand As String

    strCommand = "Powershell.exe -File ""C:\Users\Future\Desktop\Sample.ps1"""
    Set wshShell = CreateObject("WScript.Shell")
    wshShell.Run strCommand, 0, True
End Function

Any idea how to do that?

I tried so far In VBA Code I used this line

strCommand = "Powershell.exe -File ""& {'C:\Users\Future\Desktop\Sample.ps1' 001.png}"""

and in the script file I used this line at the top

$image=$args[0]

But I couldn't make it work yet

So, what have you tried so far? Your PowerShell code so far doesn't accept parameters, and I don't see any VBA code. — Ansgar Wiechers, Aug 05 '19 at 08:43
I have updated the question and put all the script and the VBA code — YasserKhalil, Aug 05 '19 at 08:44
So, the PowerShell script doesn't accept parameters, and the VBA code doesn't pass parameters. — Ansgar Wiechers, Aug 05 '19 at 08:45
This is what I am trying to do. But I have no experience at powershell at all. I am totally newbie — YasserKhalil, Aug 05 '19 at 08:55
I haven't used powershell script but a quick search reveals [how to have parameters in your powershell script](https://stackoverflow.com/questions/5592531/how-to-pass-an-argument-to-a-powershell-script). You can then pass the parameters through your VBA code — Zac, Aug 05 '19 at 09:25
An alternative approach would be to write the PowerShell script using VBA and execute it. — , Aug 05 '19 at 09:42
Thanks a lot .. But I tried this approach and I noticed that the results are not correct exactly as expected.. While using the file script, this gives me correct results in more precise result .. I don't know why but this is what happened to me.. — YasserKhalil, Aug 05 '19 at 09:52
Why did you change `-File ""C:\Users\Future\Desktop\Sample.ps1""` to `-File ""& {'C:\Users\Future\Desktop\Sample.ps1' 001.png}""`? That is invalid syntax. Use something like this: `strCommand = "Powershell.exe -File ""C:\Users\Future\Desktop\Sample.ps1"" " & ws.Range("C4").Value` where `ws` is a variable pointing to your worksheet and `"C4"` is the cell with the file name in that worksheet. — Ansgar Wiechers, Aug 05 '19 at 10:28
@Ansgar Wiechers you are awesome. Thank you very much. It works like charm. — YasserKhalil, Aug 05 '19 at 11:20

score 1 · Accepted Answer · answered Aug 05 '19 at 11:27

1

-File "& {'C:\Users\Future\Desktop\Sample.ps1' 001.png}" is invalid syntax. You cannot pass a scriptblock invocation to the parameter -File. You need something like this:

strCommand = "Powershell.exe -File ""C:\Users\Future\Desktop\Sample.ps1"" """ & _
             ws.Range("C4").Value & """"

where ws is a variable pointing to your worksheet and "C4" is the cell with the file name in that worksheet.

answered Aug 05 '19 at 11:27

Ansgar Wiechers

175,025
22
204
278

Thank you very much. One more point please. What if I need to add a second parameter ..? What will I edit in both the script file and the VBA code .. (this is just for more information as I have no need for it now) – YasserKhalil Aug 05 '19 at 11:30
1

@YasserKhalil You'd add that as a second positional parameter in the VBA code (just like the first one) and assign it in the PowerShell code like this: `$second = $args[1]`. – Ansgar Wiechers Aug 05 '19 at 11:36

Run script file from Excel VBA and pass variable to the script file

1 Answers1