We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


MS Access - VBA SHELL command problem...

careybroph asked
Medium Priority
Last Modified: 2012-05-11
So, after about two hours of searching and testing, I'm stuck.  Hoping someone might have an idea on what to try next!

I have an MS Access app where I zip a file using the SHELL command.  It just doesn't seem to want to work.  I've asked nicely.  I've threatened my PC.  All to no avail.

The code is below.  Notice the following.

1. The hard-coded path to the command line zip utility.
2. It takes two parameters (name of the zip file and the file to be zipped)
3. I'm capturing a return value that seems to change every time I run it.  But my understanding is that anything other than a "0" is a good sign.
4. I display the return value.  I get legit numbers.  Never get a zero.
5. At one point, I had it stuff the string into a text field.  I then copied the resulting text into a command window and ran it manually... it works PERFECT.  So, I'm very confident in the command string.

BUT, when the code is run via my app, it "acts" like it runs,but I don't get the zip file.

Unfortunately, I can't find a way to keep the shell open long enough to see what it's actually doing.

Any thoughts, guidance or ideas would be much appreciated.

Private Sub ZipFile_FX(ZipFileName As String, fileToBeZipped As String)
Dim ReturnVal As Double

    Const ZIPEXELOCATION = "\\fs1\admin\zipexe\7za.exe"

    ReturnVal = Shell(ZIPEXELOCATION & " " & Chr(34) & ZipFileName & Chr(34) & " " & Chr(34) & fileToBeZipped & Chr(34), vbNormalFocus)

    MsgBox ReturnVal

    Notes.Value = ZIPEXELOCATION & " a " & Chr(34) & ZipFileName & Chr(34) & " " & Chr(34) & fileToBeZipped & Chr(34)

End Sub
Watch Question

Top Expert 2016

try the codes from this link

Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
Top Expert 2014
Unlock this solution and get a sample of our free trial.
(No credit card required)
Most Valuable Expert 2014

Something to keep in mind is NTFS permissions and the context in which the shell runs.
What you (username) can do may not be something the app(system) can do.

I've had it happen where permissions came into play.

One way I got around it was to build a batch file line by line using a textstream object and then executing the batch file.
Here is some code that gives the general idea
'Required references
'assumes DAO 3.6
'assumes Windows Script Host Object Model

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

Dim x As Integer
Dim wait As Double
Dim BatFile As TextStream
Dim Success As Boolean

'snag a recordset of the pictures that we want to nail into the report
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct path from tblPictures where jobid = " & Forms!frmJobID!JobID, dbOpenDynaset, dbSeeChanges)

If rs.RecordCount = 0 Then Exit Function

Set BatFile = fs.CreateTextFile("c:\prod\" & ReturnComputerName & ".cmd", True)
If fs.FolderExists("c:\prod\temp") = True Then
    fs.DeleteFolder "c:\prod\temp", True
    fs.CreateFolder ("c:\prod\temp")
    fs.CreateFolder ("c:\prod\temp")
End If

BatFile.WriteLine ("c:")
BatFile.WriteLine ("cd \prod")
'BatFile.WriteLine ("cd " & Chr(34) & Me!Path & Chr(34))
Do Until rs.EOF
    BatFile.WriteLine ("C:\Prod\PhotoResize700O.exe -c" & Chr(34) & "C:\prod\temp\<PATH><NAME>.jpg" & Chr(34) & " " & Chr(34) & Left(rs!Path, Len(rs!Path) - 1) & Chr(34))
BatFile.WriteLine (vbCrLf)
'BatFile.WriteLine ("pause")
Set BatFile = Nothing
Shell "cmd /c c:\prod\" & ReturnComputerName & ".cmd"

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017

To further along what has already been said, see the attached code to wait for a shelled process to finish and below, which is a another sample of what Nick suggested (this is doing a PDF conversion process using Ghost Script).  By doing it this way, you can put a stop on the code before it calls shell, jump to a command window, and execute the batch file manually.

              'Generate file name to use for PDF output and script file.
170           strPDFFileName = GetTempFileName_TSB(AppShortName())
180           strPDFFileName = Replace(strPDFFileName, ".tmp", ".pdf")
190           strPDFScriptFile = strPDFProgPath & AppShortName() & "_MKPDF.bat"

              ' Change printer driver output into a PDF
              ' Script the batch file
200           Open strPDFScriptFile For Output As #1
210           Print #1, "" & left(strPDFProgPath, 2)
220           Print #1, "" & "CD " & Chr(34) & Mid(strPDFProgPath, 3) & Chr(34)
230           Print #1, "" & Chr(34) & strPDFScriptFile & Chr(34) & " " & strPRNFile & " /D /V1.4 /O" & strPDFFileName
240           Close #1

              ' Execute batch file and wait till done.
250           lngHWnd = Shell(Chr(34) & strPDFScriptFile & Chr(34), vbMinimizedNoFocus)
260           WaitWhileRunning (lngHWnd)

270           RunReportAsPDF = strPDFFileName

Open in new window

Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

' Used for wait check.
Const STILL_ACTIVE = &H103

Public Sub WaitWhileRunning(lngHWnd As Long)
        Dim lngExitCode As Long
        Dim lnghProcess As Long

10      lngExitCode = STILL_ACTIVE
20      lnghProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, lngHWnd)

30      If lnghProcess > 0 Then
40        Do While lngExitCode = STILL_ACTIVE
50          Call GetExitCodeProcess(lnghProcess, lngExitCode)
60          DoEvents
70        Loop
80      End If

End Sub

Open in new window


Seeing that I already had a routine that called the zip exe using two parameters, it was extremely easy to create a batch file that called the zip exe with two parameters.

Thanks to others as well.  This was the most straightforward solution.
Most Valuable Expert 2014

That was elegant.
I'll have to steal that.
Camera devices that don't report as drive letters are the bane of my existence.
I've got WIA stuff in a VBScript to take the pictures off the cameras and write the paths.
Getting the code to pause gracefully has been, hmm...a bit clunky.

Thanks for the post

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.