Hi guys,
I am trying to create a VBScript function from my ERP system to open an Excel Workbook and use passed parameters, BoM and Qty to run the Excel macro in the opened workbook....
This is my current VBScript
Code
Dim BoM
Dim Qty
Function ucmdPrintBoM_Click()
BoM =Trim(controls("txtPartID").value)
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("X:\M1Docs\Tools\Multi-Level BOM (HB).xlsm")
objExcel.Application.Visible = True
BoM =Trim(controls("txtPartID").value)
Qty = Int(controls("txtProdQty").Value)
objExcel.Application.Run "'X:\M1Docs\Tools\Multi-Level BOM (HB).xlsm'!NewQuery", BoM, Qty
End Function
Display More
It errors out telling me I have a Type Mismatch and identifies the line causing the error as this line:
My VBA code in the Excel workbook that I am linking to starts like this...
Code
Sub NewQuery(Optional BoM As String, Optional Qty)
Dim sConn As String
Dim sSql As String
Dim Assembly
Dim Project As String
Dim Obj As Object
Dim i As Long
Dim Quantity
'Sheets("BOM").Range("F1").ClearContents
Application.ScreenUpdating = False
ActiveSheet.Unprotect
If Len(BoM) > 0 Then
Assembly = BoM
Quantity = Qty
Else
Assembly = InputBox(Prompt:="Enter an Assembly ID", Title:="Assembly ID")
Quantity = InputBox(Prompt:="Enter Number of Assemblies Desired", Title:="Quantity", Default:=1)
End If
.
.
.
.
End Sub
Display More
I understand that in VBScript variables are always identified as Variant, So I tried playing around with the variable in Excel to try to make the key variables BoM, Qty, Assembly and Qty as Variant to match.... but now it is giving me a similar type mismatch error in the Excel workbook highlighting line:
Does anyone have any suggestions to try?