• Announcements

    • xper

      MSFN Sponsorship and AdBlockers!   07/10/2016

      Dear members, MSFN is made available via subscriptions, donations and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. Alternatively, become a site sponsor and ads will be disabled automatically and by subscribing you get other sponsor benefits.
Sign in to follow this  
Followers 0
aerospatiale

Calling VBA macro from VBScript

2 posts in this topic

Hello, I currently have the script (reprinted at the end of this post), which works when calling a VBA macro (which accepts parameters in Excel). The problem however is when I modify the App.Run line to accept the input parameter objects that I have picked up from the command line instead of string literals (commented out in the code below). When I do this, I receive the following error.

Error: 13

Error (Hex): D

Source: Microsoft VBScript runtime error

Description: Type mismatch

How can I correct this (if possible)??

My script:

Dim app ' Application object handle

Dim wb ' Workbook object handle

dataFilePath = WScript.Arguments.Item(0)

macroName = WScript.Arguments.Item(1)

inputFilePath = WScript.Arguments.Item(2)

dataParam1 = WScript.Arguments.Item(3)

' Assign the application object handle to an Excel instance

Set app = CreateObject("Excel.Application")

' Ensure that the user isn't bothered by the temporary instance of Excel

app.Visible = False

app.DisplayAlerts = False

' Open the workbook containing the relevant macros to be run

On Error Resume Next

Set wb = app.Workbooks.Open(dataFilePath)

If Err.Number <> 0 Then

Call reportError

End If

' I would prefer to type the more dynamic: app.Run macroName, inputFilePath, dataParam1

app.Run "literalMacroName", "literalInputFilePath", "literalDataParam1"

If Err.Number <> 0 Then

Call reportError

End If

0

Share this post


Link to post
Share on other sites

This is not the correct way of using the Run Method

app.Run "literalMacroName", "literalInputFilePath", "literalDataParam1"

Would Be more correct.

app.Run "literalMacroName"& "literalInputFilePath"&"literalDataParam1"

VBS Run Method

Problem 1

If you are trying to pass varibles make sure these exists in your script, the below

would only be process as string. They would not be process as Varibles.

"literalMacroName", "literalInputFilePath", "literalDataParam1"

Example Of Varibles With Quotes

VaribleWithQuotes.png

0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.