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
– Ansgar Wiechers, Commented Aug 5, 2019 at 8:43
So, the PowerShell script doesn't accept parameters, and the VBA code doesn't pass parameters. — Ansgar Wiechers
– Ansgar Wiechers, Commented Aug 5, 2019 at 8:45
I haven't used powershell script but a quick search reveals how to have parameters in your powershell script. You can then pass the parameters through your VBA code — Zac
– Zac, Commented Aug 5, 2019 at 9:25
An alternative approach would be to write the PowerShell script using VBA and execute it. — user11509084
– user11509084, Commented Aug 5, 2019 at 9:42
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
– Ansgar Wiechers, Commented Aug 5, 2019 at 10:28

Ansgar Wiechers · Accepted Answer · 2019-08-05 11:27:00Z

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 5, 2019 at 11:27

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

2 Comments

YasserKhalil Over a year ago

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)

Ansgar Wiechers Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related