title: Access VB - Shell string problems
body: <p>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.</p>
  
  <p>I suspect something is going on in the interpretation of the string version of the code but can't work out what.</p>
  
  <p>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!</p>
  
  <p>Private Sub temp()</p>
  
  <p>Dim strFilePath As String<br>
  Dim strFileName As String<br>
  Dim strZipFilename As String<br>
  Dim strPDFfilename As String<br>
  Dim strShellString As String<br>
  Dim shell As Object<br>
  Dim result As Long  </p>
  
  <pre><code>Set shell = CreateObject("WScript.shell")
  
  strFilePath = "E:\Documents\Excel Spreadsheets\Roz Theremas\Access\PDFs"
  strFileName = "17-03-31temp"
  strZipFilename = strFilePath &amp; "\" &amp; strFileName &amp; ".zip"
  strZipFilename = Chr(34) &amp; strZipFilename &amp; Chr(34)
  strPDFfilename = strFilePath &amp; "\" &amp; strFileName &amp; ".pdf"
  strPDFfilename = Chr(34) &amp; strPDFfilename &amp; Chr(34)
  strShellString = Chr(34) &amp; Chr(34) &amp; Chr(34) &amp; "C:\Program Files\7-Zip\7z.exe" &amp; Chr(34) &amp; Chr(34) &amp; " a -tzip " &amp; Chr(34) &amp; strZipFilename &amp; Chr(34) &amp; " " &amp; Chr(34) &amp; strPDFfilename &amp; Chr(34) &amp; Chr(34)
  </code></pre>
  
  <p>'Non Working shell command</p>
  
  <p>result = shell.Run(strShellString, 0, False)</p>
  
  <p>'Working shell command</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>'Immediate Window output of strShellString</p>
  
  <p>'                      """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"""</p>
  
  <p>End Sub</p>