Problem with VBScript calling VBA macro: macro is called twice...

[bluesea1]

Dear tek-tips people,

maybe you can help me with the following VBScript problem: I am calling an Excel VBA macro from a VBScript, the problem is: The macro is run twice - though I only call it once...!

Here are the details: I am running a batch file which calls a VBScript together with a command line argument (plus a redirect of the output to a log file). The batch file looks like this:

CScript //nologo "C:\Some Path\MyScript.vbs" "VBA Argument" >> "C:\Some Path\My Errlog.log"

Both batch file and VBScript are generated automatically, the batch file is then executed by an external program.
The VBScript assumes a specific Excel workbook is open and calls a VBA macro in that workbook, passing the command line argument ("VBA Argument", above) as an argument to the Excel VBA macro. The VBScript looks like this:

Set XLHandle = GetObject(, "Excel.Application") 
 XLHandle.DisplayAlerts = False 
 XLHandle.Visible = False 
 On Error Resume Next 
 sWbkPath = "C:\MyPath\" 
 sWbkName = "temp 123.xls" 
 sWbkFullName = "'" & sWbkPath & sWbkName & "'!"  
 sMacroName = "Test2" & "(" & Chr(34) & WScript.Arguments(0) & Chr(34) & ")" 
 sMacroName = sWbkFullName & sMacroName 
 'WScript.echo sMacroName 
 XLHandle.Run sMacroName  
 boolRC = Err.Number <> 0 
 On Error GoTo 0 
  If boolRC Then 
  WScript.Echo Time() & ": Run Excel Macro, Failed to call macro | Incorrect or not existing macro" 
 Else 
  WScript.Echo Time() & ": Run Excel Macro, Successfully invoked macro" 
 End If 
  Set XLHandle = Nothing

As stated above: The code basically works fine, but the problem is that the Excel VBA macro is executed twice...
Can you help me... What is going wrong??

Thank you very much!
James

 

[bluesea1]

In the meantime I solved the problem. I'm posting the answer, somebody might be interested...

(1) The Excel macro must be called like this:

XLHandle.Run "MyMacro", 15

(where 15 is an example argument) instead of

XLHandle.Run("MyMacro(15)"

While the latter also works, this is responsible for the macro being called twice.

(2) This solution presented works only with constant arguments, not with dynamic ones. So, this code would still not work:

XLHandle.Run "MyMacro", iMyArgument

What is necessary: Here the VBA macro must be adapted accordingly. This means that the VBA macro must accept a Variant argument, not any other datatype. So while you might be expecting a String, Integer or whatever, you always have to declare the VBA argument as Variant.

Then it works.

Hope this helps
James