Powershell Command in VBA

Question

I'm trying to run a powershell command to unzip some files, but running in to some issues figuring out the correct syntax. The following doesn't error out, and even when I run the command in powershell itself it doesn't display an error (but also doesn't work). Does anyone know what I'm doing wrong?

Dim command As String: Set wsh = VBA.CreateObject("WScript.Shell")
Dim wsh As Object
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 7
Dim pdfPath As String

pdfPath = ThisWorkbook.Path & "\PDFTemp\"

command = "Powershell -Command" & Chr(32) & "{Expand-Archive -LiteralPath" & Chr(32) & _
"'" & frmMerge.txtBoxFile2.Value & "'" & Chr(32) & "-DestinationPath" & Chr(32) & "'" & pdfPath & "'" & "}"

wsh.Run command, windowStyle, waitOnReturn

Thanks so much for your help!

Tim Williams · Accepted Answer · 2021-02-03 19:46:10Z

1

You can remove a lot of parts from that concatenation.

This worked for me (also adjusted the command a little):

Sub Unzipper()
    Dim command As String, wsh As Object, waitOnReturn As Boolean, windowStyle As Integer
    Dim pdfPath As String, zipPath As String
    
    waitOnReturn = True
    windowStyle = 7
    
    zipPath = "C:\Tester\PDF_files.zip"   'frmMerge.txtBoxFile2.Value
    pdfPath = "C:\Tester\PDFTemp\"        'ThisWorkbook.Path & "\PDFTemp\"
    
    command = "Powershell Expand-Archive -LiteralPath " & _
              "'" & zipPath & "' -DestinationPath '" & pdfPath & "'"
    
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run command, windowStyle, waitOnReturn
End Sub

edited Feb 3, 2021 at 19:46

answered Feb 3, 2021 at 19:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

Sudio Over a year ago

Thanks, this is a much cleaner way of doing it (plus it works!)

Collectives™ on Stack Overflow

Powershell Command in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related