asked on

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

crystal (strive4peace) - Microsoft MVP, Access

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdhackett

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