Access VB - Shell string problems

Question

Trying to create the string for use with the Shell command in Access VB. The string works if written in full but if I try to use variables in the string the command does not work even if the use of variables produces exactly the full string, (in the immediate window), that works.

I suspect something is going on in the interpretation of the string version of the code but can't work out what.

Here's the code, I have used CHR(34) to produce the quotation marks that I want to show in the string. Suggestions would be so much appreciated - I don't have much hair left!

Private Sub temp()

Dim strFilePath As String
Dim strFileName As String
Dim strZipFilename As String
Dim strPDFfilename As String
Dim strShellString As String
Dim shell As Object
Dim result As Long

Set shell = CreateObject("WScript.shell")

strFilePath = "E:\Documents\Excel Spreadsheets\Roz Theremas\Access\PDFs"
strFileName = "17-03-31temp"
strZipFilename = strFilePath & "\" & strFileName & ".zip"
strZipFilename = Chr(34) & strZipFilename & Chr(34)
strPDFfilename = strFilePath & "\" & strFileName & ".pdf"
strPDFfilename = Chr(34) & strPDFfilename & Chr(34)
strShellString = Chr(34) & Chr(34) & Chr(34) & "C:\Program Files\7-Zip\7z.exe" & Chr(34) & Chr(34) & " a -tzip " & Chr(34) & strZipFilename & Chr(34) & " " & Chr(34) & strPDFfilename & Chr(34) & Chr(34)

'Non Working shell command

result = shell.Run(strShellString, 0, False)

'Working shell command

result = shell.Run("""C:\Program Files\7-Zip\7z.exe"" a -tzip ""E:\Documents\Excel Spreadsheets\Roz Theremas\Access\PDFs\17-03-31temp.zip"" ""E:\Documents\Excel Spreadsheets\Roz Theremas\Access\PDFs\17-03-31temp.pdf""", 0, False)

'Immediate Window output of strShellString

' """C:\Program Files\7-Zip\7z.exe"" a -tzip ""E:\Documents\Excel Spreadsheets\Roz Theremas\Access\PDFs\17-03-31temp.zip"" ""E:\Documents\Excel Spreadsheets\Roz Theremas\Access\PDFs\17-03-31temp.pdf"""

End Sub

Roger Currier · Accepted Answer · 2017-04-01 23:09:03Z

My hair is saved - I worked out the answer! So, for posterity here it is.

I modified strFilePath to put quotes around the two word folder names and removed quotations from around the strZipFilename and strPDFFilename lines. I guess Shell was suffering from a surfeit of quotes.

Whilst modifying it I added a password to the zip file and changed the false to true at the end of the string command so that it would return an error code.

Thanks to anyone who spent time trying to work out what I had done.

Public Sub temp()

Dim strFilePath As String
Dim strFileName As String
Dim strZipFilename As String
Dim strPDFfilename As String
Dim strPassword As String
Dim shell As Object
Dim result As Long

Set shell = CreateObject("WScript.shell")

strPassword = "frog"
strFilePath = "E:\Documents\""Excel Spreadsheets""\""Roz Theremas""\Access\PDFs"
strFileName = "17-03-31temp"
strZipFilename = strFilePath & "\" & strFileName & ".zip"
strPDFfilename = strFilePath & "\" & strFileName & ".pdf"

result = shell.Run("""C:\Program Files\7-Zip\7z.exe"" a -tzip " & "-p" & strPassword & " " & strZipFilename & " " & strPDFfilename, 0, True)

Collectives™ on Stack Overflow

Access VB - Shell string problems

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related