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
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
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...
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
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?