command line not working when run from vba

Question

I'm trying to unzip a file via my VBA code. I'm using 7z command line to unzip the file. However the command works when run from normal command prompt but the same command is not working when run via VBA code.

Command:
"C:\Program Files\7-Zip\7z.exe" x "C:\Users\Public\AppData\Local\Temp\Sample.zip"

For further understanding, I'm trying to extract a docx file and that is why I am renaming it to .zip and then extracting.

Sub tst()
Dim MyFile As String, Outdir As String, Cmdstr As String
MyFile = Chr(34) & "c:\TMP\ratings.gz" & Chr(34)
Outdir = Chr(34) & "c:\tmp\0" & Chr(34)
Cmdstr = "c:\Program Files\7-Zip\7z.exe" & " e " & MyFile & " -o" & Outdir
Debug.Print Cmdstr
Call Shell(Cmdstr, 1)
End Sub

I've used this type of function (similar) to unzip the file.

Answer 1

Missing proper quoting, should be

Cmdstr = """c:\Program Files\7-Zip\7z.exe""" & " e " & MyFile & " -o" & Outdir

The Command Line Version of 7-Zip is 7za.exe rather than 7z.exe.

I'm not sure about running CLI applications with Shell method but next could work:

Cmdstr = "cmd /D /C " & """full path to 7za\7za.exe""" & " e " & MyFile & " -o" & Outdir

Edit. To retain folder structure, use 7z with x command rather than e command. While the e command copies all extracted files to one directory, the z command extracts files from an archive with their full paths in the current directory, or in an output directory if specified. So you could define Cmdstr as follows:

Cmdstr = """c:\Program Files\7-Zip\7z.exe""" & " x " & MyFile & " -o" & Outdir