Running shell command from Access

Hi
I'm trying to zip some items using Shell.
These are the two commands
1) pkzipc.exe -add C:\temp\test1.zip c:\temp\test.doc
2) pkzipc -add -passphrase=@c:\temp\secret.txt -cryptalgorithm=aes c:\temp\test_e.zip 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\test1.zip 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?

Thanks

Microsoft AccessShell ScriptingSystem Programming

Last Comment

jdhackett

8/22/2022 - Mon

crystal (strive4peace) - Microsoft MVP, Access

1/14/2016

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

http://www.7-zip.org/

Here is some code I used to zip ACCDE files that you can customize:

Sub ZipAccdeFile(psPathFile As String, psPathFileZip As String)
'strive4peace
   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
      DoEvents
   End If
   
'   For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(SOURCE).Files
      Shell PATHFILE_7zip & " a -tzip " & psPathFileZip & " " & psPathFile
'   Next
Proc_Exit:
   On Error Resume Next
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ZipAccdeFile"

   Resume Proc_Exit
   Resume

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!

ASKER CERTIFIED SOLUTION

rspahitz

1/14/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

jdhackett

1/15/2016

ASKER

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\test1.zip"
    stArg3 = "c:\temp\test.doc"

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

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy