CODEWITHSUNDEEP
excelvbabatch-filevbscriptenvironment-variables
TMY
TMY

Reputation: 469

Passing a File Path Variable from Batch to VBA

Let me answer the first question in everyone's mind first...

Why use batch to call a VBA macro?

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.)

My Issue

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 & 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).

My Code

Passing variable from Batch -> VBS

set ArchiveFullName=\\myArchivePath\myFile.rtf
cscript //NoLogo "\\myEmailAutomationFolder\myEmailAutomation.vbs"  /attachment:"%AttachmentFullName%"

**Idea for using this method of 'cscript' (line 3 of code) to pass the variable from Batch to VBS came from: Pass variable from Batch to VBS

Passing Variable from VBS to VBA

  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

**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: Pass Argument from VBS to VBA

Accepting variable in VBA

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

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).

Error Message

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:

Compile error: Object required

Troubleshooting the Code

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).

Any help would be greatly appreciated!

Upvotes: 1

Views: 3273

Answers (1)

RLH
RLH

Reputation: 1593

What do you get if you implement these changes? You should see the value pass through from Batch>VBS>VBA.

Your batch file:

No changes.

set ArchiveFullName=\\myArchivePath\myFile.rtf
cscript //NoLogo "\\myEmailAutomationFolder\myEmailAutomation.vbs"  /attachment:"%AttachmentFullName%"

Your VBS file:

Comment out the Exit Sub as there was not sub declared.

Add a msgbox showing the value of the attachmentFullName (for testing).

Dim xlApp 
Dim xlBook 
Dim attachmentFullName
attachmentFullName = WScript.Arguments.Named("attachment")
msgbox attachmentFullName,vbokonly,"Variable value in VBS"        'added this

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 
'Exit Sub                         'removed this

Your VBA file:

Comment out the set commands.

Added a msgbox for diagnosis.

Sub Email_Received_Files(t As String)
    Dim placementAttachment As String
    msgbox t,vbokonly,"Variable value in VBA"

    'Grab attachment path from .vbs (which grabbed it from .bat)
    'Set placementAttachement = t
    'Set Range(C6) = placementAttachement
End Sub

On the commented out set lines, they require an object. The 't' line just needs the set removed. It's just a string. placementAttachement = t

On the Set Range line, if you are trying to store the file path in cell C6, then just lose the placementAttachement variable and set it directly from the passed value.

Range("C6").Value = T

Upvotes: 2

Related Questions