Passing a File Path Variable from Batch to VBA

Question

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!

Excel comes with a built-in debugger, provided the macro editor is installed. For VBScript debugging see here. A good first step is using a MsgBox to display the value of the variable in question. As for your error message: please show the complete error message, including the error number. Which line is raising the error? For debugging I'd also recommend setting xlApp.DisplayAlerts = True and xlApp.Visible = True. — Ansgar Wiechers
– Ansgar Wiechers, Commented Jan 19, 2016 at 20:52
The statement Set placementAttachement = t is likely the problem. Set should only be used an object; you're using it with a string. Try placementAttachement = t instead. — xidgel
– xidgel, Commented Jan 19, 2016 at 21:39

RLH · Accepted Answer · 2016-01-19 23:20:54Z

2

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

edited Jan 19, 2016 at 23:20

answered Jan 19, 2016 at 23:05

RLH

1,60312 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TMY Over a year ago

Awesome, after just a tiny bit of tweaking I had this working with my current email automation code - thank you @RLH! (Also like the use of msgbox for vbs debugging).

RLH Over a year ago

@TMY, It would be good to post the fixed code so others can see it if they are looking at the same issue.

TMY Over a year ago

Your code above works / is the solution (in case anyone else runs into this issue). My tweaks were simply based off changing the pseudo-variable names and paths to merge your code into my existing code, then removing the debug msgboxes so the process can run without human intervention.

Collectives™ on Stack Overflow

Passing a File Path Variable from Batch to VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related