Passing Parameters from VBScript to VBA

  • 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



    It errors out telling me I have a Type Mismatch and identifies the line causing the error as this line:

    Code
    objExcel.Application.Run "'X:\M1Docs\Tools\Multi-Level BOM (HB).xlsm'!NewQuery", BoM, Qty


    My VBA code in the Excel workbook that I am linking to starts like this...




    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:

    Code
    If Len(BoM) > 0 Then


    Does anyone have any suggestions to try?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Passing Parameters from VBScript to VBA


    Hi Norie,


    Not sure if I fully understand what you mean... but I added the Optional variables (Optional BoM As String, Optional Qty) in


    Code
    Sub NewQuery(Optional BoM As String, Optional Qty)


    and I added the IF/Else/End IF so that if I open the workbook directly I can use it with manual inputs for Assembly and Quantity.


    The value that should be passed to BoM is something like 1BC111


    Is this what you are asking?


    If I keep the BoM variable as String and I open the workbook in Excel and click by command button linked to the macro, then I get the input boxes and can successfully enter in my parameters and run it. It's only when passing the parameters from the VBScript that I have the issue.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Passing Parameters from VBScript to VBA


    Hi Norie,


    Yes I did try that.


    However, I did some more googling, and discovered that I can use CStr() in the Application.Run in the VBScript as per:


    Code
    objExcel.Application.Run "'X:\M1Docs\Tools\Multi-Level BOM (HB).xlsm'!NewQuery", Cstr(BoM), Qty


    That seems to have done the trick :)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!