Running shell command from Access

jdhackett used Ask the Experts™
I'm trying to zip some items using Shell.
These are the two commands
    1) pkzipc.exe -add C:\temp\ c:\temp\test.doc
    2) pkzipc -add -passphrase=@c:\temp\secret.txt -cryptalgorithm=aes c:\temp\ c:\temp\test.doc

So I've tried this code, that I saw elsewhere:
Dim strProgramName As String
    Dim strArgument As String

    strProgramName = "pkzipc.exe"
    strArgument = "-add c:\temp\ c:\temp\test.doc"

    Call Shell("""" & strProgramName & """ """ & strArgument & """", vbNormalFocus)

Open in new window

But it doesn't work. Not only that, but the command window closes too quickly, so I can't see why it failed.
So two questions:
a) What is the correct syntax for the Shell command to run both of the pkzip commands above
b) How do I keep the command window open so that I can see the results?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Rather than PKZip, I use 7-Zip (free) to zip and unzip files from Access.

Here is some code I used to zip ACCDE files that you can customize:
Sub ZipAccdeFile(psPathFile As String, psPathFileZip As String)
   On Error GoTo Proc_Err

   Const PATHFILE_7zip = "C:\Program Files\7-Zip\7z.exe"
   Dim sPath As String
   sPath = "D:\DATA\UserDatabases\"
   'strip ACCDE from End
   'add _ACCDE.Zip to end
   psPathFileZip = Left(psPathFile, Len(psPathFile) - 6) & "_ACCDE.ZIP"
   'delete zip file if it already exists
   If Dir(psPathFileZip) <> "" Then
      Kill psPathFileZip
   End If
'   For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(SOURCE).Files
      Shell PATHFILE_7zip & " a -tzip " & psPathFileZip & " " & psPathFile
'   Next
   On Error Resume Next
   Exit Sub
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ZipAccdeFile"

   Resume Proc_Exit

End Sub

Open in new window

> "b) How do I keep the command window open so that I can see the results?"

Better than trying to do that is to open a Windows Explorer window to the directory you are working with.  Press F5 to refresh the files.  The first time you WRITE a new file, it may not show up right away due to Windows just being slow.  That happened to me and I thought my zipping code didn't work ... well it did!
You can try this:

Dim strYourCommand As String
strYourCommand = """" & strProgramName & """ """ & strArgument & """"
Shell "cmd -k " & strYourCommand' it might be /k rather than -k...don't have OS access at the moment to try it

Open in new window

This should "k"eep the window open (-k) which can later be changed to -c to "c"lose the window. (it might be / rather than -, like /k or /c)
It should also show you the command which is being run, which I think will be something like "pkzipc.exe" "-add C:\temp\ c:\temp\test.doc"

What I suggest it to try to get the command to run from the command window first, then migrate the correct result back into your VB window (replacing quotes as needed.)
In some cases, you will need to send a "start" to get the command to run through a shell.  I think it's "cmd /k start pkzip..."


The commands I listed actually do work from the command window.
The /k was the key, as it let me see the errors. There was no need for the extra quotes around the -add part.
My work code is below, thanks for the help

    Dim strProgramName As String
    Dim stArg1 As String
    Dim stArg2 As String
    Dim stArg3 As String
    Dim stCommand As String

    strProgramName = "pkzipc.exe"
    stArg1 = "-add"
    stArg2 = "c:\temp\"
    stArg3 = "c:\temp\test.doc"

    stCommand = "cmd /k " & """" & strProgramName & " " & stArg1 & " " & stArg2 & " " & stArg3 & """"
    Call Shell(stCommand, vbNormalFocus)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial