Welcome to MSFN

Register now to gain access to all of our features. Once registered and logged in, you will be able to contribute to this site by submitting your own content or replying to existing content. You'll be able to customize your profile, receive reputation points as a reward for submitting content, while also communicating with other members via your own private inbox, plus much more! This message will be removed once you have signed in.


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.