Run powershell command from excel vba

[YasserKhalil]

Hello everyone

I have this line in powershell

Code: Select all

tesseract.exe Sample.png Output.txt -l eng

And I would like to use excel VBA to run this line

Code: Select all

Sub Test()
Dim retval, pscmd

pscmd = "PowerShell -ExecutionPolicy Bypass -Command ""tesseract.exe " & ThisWorkbook.Path & ""\Sample.png\"" & "" "" & ThisWorkbook.Path & ""\Output.txt\""  -l eng"
retval = Shell(pscmd, vbNormalFocus)

Debug.Print pscmd
End Sub

Or how to execute two command in the same powershell window

Code: Select all

Sub Test()
Dim retval, pscmd$, cdcmd$

cdcmd = "PowerShell.exe -noexit  -ExecutionPolicy Bypass -Command ""cd C:\Users\Future\desktop"""
pscmd = "PowerShell.exe -noexit  -ExecutionPolicy Bypass -Command ""tesseract.exe Sample.png Output.txt -l eng"""

retval = Shell(cdcmd, vbNormalFocus)
retval = Shell(pscmd, vbNormalFocus)

Debug.Print pscmd
End Sub

The line of pscmd appears iun red. I tried to play around and fix it but I couldn't

[HansV]

How about

Code: Select all

pscmd = "PowerShell -ExecutionPolicy Bypass -Command ""tesseract.exe """"" & ThisWorkbook.Path & "\Sample.png"""" """"" & ThisWorkbook.Path & "\Output.txt"""" -l eng"""

(It's just guesswork, I haven't tested it)

[YasserKhalil]

Thank you very much my tutor ..
I have edited it a little and get it work

Code: Select all

pscmd = "PowerShell.exe -noexit -ExecutionPolicy Bypass -Command ""tesseract.exe """ & ThisWorkbook.Path & "\Sample.png"" """ & ThisWorkbook.Path & "\Output.txt"" -l eng"""

But my question : is there a way to make it more flexible .. I don't know how to build it in correct way ..? I am just playing around to make it work

Another point : is there a way to make two command lines in the same powershell window?

[HansV]

What do you mean by "making it more flexible"?

I have no idea about your second question.

[YasserKhalil]

I played around and got it work in that way

Code: Select all

cdcmd = "PowerShell.exe -noexit  -ExecutionPolicy Bypass -Command ""cd C:\Users\Future\desktop"""
pscmd = "PowerShell.exe -noexit  -ExecutionPolicy Bypass -Command ""tesseract.exe Sample.png Output.txt -l eng"""

retval = Shell(cdcmd & vbNewLine & pscmd, vbNormalFocus)

Thanks a lot ...
But I am looking for basic approach to be able to convert any powershell command easily ..

[YasserKhalil]

I mean with flexible is to put the command in normal way and make the code build the quotation marks ..

[HansV]

You could place the command in a cell, or in a text file.
Retrieve the command, and replace Chr(34) with Chr(34) & Chr(34)

[YasserKhalil]

And what if I put the string into a variable .. Will I be able to do the same ..to replace chr(34) with chr(34) & chr(34)?

[YasserKhalil]

I could use some variables to make it easier

Code: Select all

sSource = ThisWorkbook.Path & "\Sample.png"
sTarget = ThisWorkbook.Path & "\Output.txt"
strBasic = "tesseract.exe " & sSource & " " & sTarget & " -l eng"
pscmd = "PowerShell.exe -noexit -ExecutionPolicy Bypass -Command """ & strBasic & """"

Any more suggestions to make it better .. I welcome any more ideas

[Jay Freedman]

If the commands you want to run are just the kind you could put into a Windows command or the Run box (as are all of the ones in this thread), you can use the VBA command Shell to run them without involving PowerShell. For the example in your last post, replace the pscmd line with

Code: Select all

Shell """ & strBasic & """

and you'll get the same program run and the same output.

Documentation at https://docs.microsoft.com/en-us/office ... l-function

The huge advantage of PowerShell is the existence of cmdlets that leverage the .Net libraries. Running simple command lines is the least of its features.

[YasserKhalil]

Thank you very much for the information
I have tried Shell but I got an error "File not found" .. and I am sure of the file paths