title: Passing a File Path Variable from Batch to VBA
body: <p>Let me answer the first question in everyone's mind first...</p>
  
  <p><strong>Why use batch to call a VBA macro?</strong></p>
  
  <p>I currently have a very complex batch that includes a watch-folder trigger, calls of multiple pieces of 3rd party software for file conversion steps, file archival steps, email triggers based on specific steps, etc. Since this batch script already calls two other emails via the Batch -> VBS -> Excel VBA procedure, I'd like to continue with that process so I can reuse code. (The current VBA code makes it easy to add an attachment too.)</p>
  
  <p><strong>My Issue</strong></p>
  
  <p>My issue is the file I'm attaching is procedurally-generated by the original batch. Therefore, I need to pass the full file name (UNC path &amp; file name) as a single string variable from the batch to the VBA script (which means also passing it through VBS since calling a VBA macro from batch requires that intermediary step -- i.e. Batch -> VBS -> VBA -> my email).</p>
  
  <p><strong>My Code</strong></p>
  
  <p><em>Passing variable from Batch -> VBS</em></p>
  
  <pre><code>set ArchiveFullName=\\myArchivePath\myFile.rtf
  cscript //NoLogo "\\myEmailAutomationFolder\myEmailAutomation.vbs"  /attachment:"%AttachmentFullName%"
  </code></pre>
  
  <p>**Idea for using this method of 'cscript' (line 3 of code) to pass the variable from Batch to VBS came from: <a href="https://stackoverflow.com/questions/21013428/pass-variable-from-batch-to-vbs">Pass variable from Batch to VBS</a></p>
  
  <p><em>Passing Variable from VBS to VBA</em></p>
  
  <pre><code>  Dim xlApp 
    Dim xlBook 
    Dim attachmentFullName
    attachmentFullName = WScript.Arguments.Named("attachment")
  
    Set xlApp = CreateObject("Excel.Application") 
    Set xlBook = xlApp.Workbooks.Open("\\myPath\EmailAutomation.xlsm", 0, True) 
  
    xlApp.DisplayAlerts = False
    xlApp.Run "Email_Received_Files", Cstr(attachmentFullName)
    xlApp.Quit 
  
    Set xlBook = Nothing 
    Set xlApp = Nothing 
  
  End Sub 
  </code></pre>
  
  <p>**The bulk of this code works, as its the code I use for two other automated emails. However, lines 3, 4, and 10 are what collect the variable from VBS, and attempt to pass from VBA.  These are based on suggestions from: <a href="https://stackoverflow.com/questions/13876338/pass-argument-from-vbs-to-vba">Pass Argument from VBS to VBA</a></p>
  
  <p><em>Accepting variable in VBA</em></p>
  
  <pre><code>Sub Email_Received_Files(t As String)
      Dim placementAttachment As String
  
      'Grab attachment path from .vbs (which grabbed it from .bat)
      Set placementAttachement = t
      Set Range(C6) = placementAttachement
  End Sub
  </code></pre>
  
  <p>The code above is just the second part of the code provided in the VBS -> VBA thread linked earlier. I've just added a debug step that puts the string on a worksheet so I know if it worked (there is also my email code, but just took a snapshot obviously).</p>
  
  <p><strong>Error Message</strong></p>
  
  <p>This don't tell really help determine if its the Batch -> VBS code or the VBS -> VBA, but I get a Compiler Error when Excel kicks off. The error message:</p>
  
  <blockquote>
    <p>Compile error: Object required</p>
  </blockquote>
  
  <p><strong>Troubleshooting the Code</strong></p>
  
  <p>I don't have Visual Studio on this PC, so there isn't a great way for me to debug the VBS script to see if the variable is being accepted from Batch. As a result, I'm not sure where the variable isn't passing correctly - step 1 (Batch -> VBS), or step 2 (VBS -> VBA).</p>
  
  <p>Any help would be greatly appreciated!</p>