Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account



Photo

Calling VBA macro from VBScript

- - - - -

  • Please log in to reply
1 reply to this topic

#1
aerospatiale

aerospatiale
  • Member
  • 1 posts
  • Joined 27-May 11
  • OS:XP Pro x86
  • Country: Country Flag
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



How to remove advertisement from MSFN

#2
gunsmokingman

gunsmokingman

    MSFN Master

  • Super Moderator
  • 2,421 posts
  • Joined 02-August 03
  • OS:none specified
  • Country: Country Flag
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
Posted Image


GunSmokingMan






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users