Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Running code from a different file (VBA / Excel 2000 / SP3)

    Good day all
    I have a two part problem. Firstly, the following code resides in Procedure B which is Called from Procedure A, and attempts to run code from a workbook that is open, but not ThisWorkbook. The same external procedure is run twice. First successfully with a string of text, then unsuccessfully with a variable that reproduces the same string. On the second attempt to run the external procedure, the program exits Procedure B and falls back to Procedure A with no indication that it has processed the external procedure. The external procedure is little more than a MsgBox.
    Please folks; where am I going wrong?

    <pre>Dim mysub As String
    Application.Run "ISS_GDC1.xls!v1About.About"
    mysub = """" & "ISS_GDC1.xls!v1About.About" & """"
    Application.Run mysub
    </pre>


    In the event that it is possible to call an external procedure with a variable; Can arguments be passed in this manner? Can these arguments include arrays? Are there any Gotchas involved?

    As ever,
    Regards
    Don

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Running code from a different file (VBA / Exce

    The general syntax for adding arguments, as illustrated in the attached, is to tack them on, separated by commas. Are there gotchas? I'm sure there are, but I'm not aware of any collection of tips on this. (Many past threads, of course, but not a compilation.) I think it is at least worth popping up the online help for the .Run method (or any other method you're trying for the first time) and reading through the discussion to see if there are limitations designed in that could affect your application. If you try something that seems as though it should work and it doesn't work, then we can try that out and see if we can find the problem.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Running code from a different file (VBA / Exce

    Incidentally, I get the same results for these; the macro is in a global add-in, which may be while I can omit the workbook name in my example:

    Sub RunExternal()
    Application.Run "wdFileSaveAs", xlWorksheet
    End Sub

    Sub RunExternal()
    Dim strMacro As String, strArgument As String
    strMacro = "wdFileSaveAs"
    strArgument = CStr(xlWorksheet)
    Application.Run strMacro, strArgument
    End Sub

    But it does not work to try to pass a single string containing both the macro name and the first argument:

    Sub RunExternal()
    Dim strMacro As String, strArgument As String
    strMacro = "wdFileSaveAs"
    strArgument = CStr(xlWorksheet)
    Application.Run strMacro & ", " & strArgument 'FAILS!
    End Sub

    Hope this helps.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running code from a different file (VBA / Excel 2000 / SP3)

    This should work:

    <pre>Dim mysub As String, MyArg1 As String, MyArg2 as Integer
    Application.Run "ISS_GDC1.xls!v1About.About"
    mysub = "ISS_GDC1.xls!v1About.About"
    MyArg1 = "Argument1"
    MyArg2 = 1
    Application.Run mysub, MyArg1, MyArg2
    </pre>

    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running code from a different file (VBA / Excel 2000 / SP3)

    Thank you Legare
    My error was in attempting to include quotes as part of the text string. I was more tired than I thought.
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running code from a different file (VBA / Exce

    Thank you Jefferson
    As a self taught VBA user I've had a terrible teacher. But with the type of help provided by folk such as you and other "Lounge Regulars"; it's all beginning to make a lot more sense.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •