CODEWITHSUNDEEP
excelvba
Sudio
Sudio

Reputation: 155

Powershell Command in VBA

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!

Upvotes: 0

Views: 355

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

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

Upvotes: 1

Related Questions