Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Excel sub with parameters (VB6 and Excel)

    In a VB6 application I'm developing, I need to open a number of Excel spreadsheets and kick off the VBA within them. As they also stand alone, they do this by opening a Login form to gather userid and password, then start a macro (always the same name). Currently, this multiple opening etc is performed via another spreadsheet, which gathers the userid and password, then loops to open the spreadsheets and execute them thus:

    Workbooks.Open ThisWorkbook.ActiveSheet.Cells(intCellsDown, 2), False, False, , , , True
    ActiveWorkbook.RunWorkbook ConstLogin, ConstPass
    ActiveWorkbook.Close True

    where RunWorkbook is the standard subroutine in each spreadsheet, and ConstLogin and ConstPass are the parameters. This works fine.

    But in VB6....? I can see how to execute RunWorkbook; various sites suggest either:
    appExcel.Run "RunWorkbook"
    or
    appExcel.ExecuteExcel4Macro "RunWorkbook"

    but nowhere can I find a way of passing the parameters into the spreadsheets from the VB6 application. Adding them to the end of the methods above doesn't work, usually provoking a "too many arguments" error, and I can't think of another way! Any suggestions?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    Try
    <code>
    appExcel.Run "RunWorkbook", ConstLogin, ConstPass
    </code>
    Note the comma after the macro name.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    Another syntax is (note that here everything is wrapped in a string, surrounded by single quotes.):

    AppExcel.Run "'RunWorkbook ""MyLogin"",""MyPass""'"

    You can use this very same syntax from the macro window to call a sub with arguments:

    'SubName "ArgString1"'
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    Sorry for tardy response, but I didn't get the usual email for some reason and only found your answer when I looked back at the site.

    Sorry, Hans, but I think I confused you a bit - though the truth is in there! I said first 'macro' and then 'subroutine', and the truth is that 'RunWorkbook' is a subroutine not a macro, so appExcel.Run doesn't work because it only executes macros, right?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    We still have intermittent problems with sending out e-mails from the Lounge server, so it's best to check the website from time to time for replies.

    The Run method can be used to execute procedures (i.e. subroutines, including macros) and functions. The example I posted shows how to specify arguments for a subroutine.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    Thanks, Hans - got your email that time! Yes, you're right, I think now. In the short time between posting the reply and getting your new reply, I tried putting the RunWorkbook routine in a Module rather than where it was before in 'ThisWorkbook' - and it made all the difference! Now Excel can find the routine and execute it without a problem.

    So the problem was purely one of location; in a Module, fine; in ThisWorkbook, no go. Thanks again.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    Sorry for late response; didn't get usual email about your reply.

    You're right in what you say, and it was something I'd tried, with no success. However, I have now discovered that the reason that RunWorkbook couldn't be found from VB6 was that it was in 'ThisWorkbook' within the spreadsheet and not in a Module. Once it was moved into a Module, it was found correctly and executed with parameters. Thanks for the reply though.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    ThisWorkbook is a very special kind of module - more like a class module than a standard module. It is meant for workbook events such as Workbook_Open and Workbook_BeforeClose, not for "ordinary" procedures and functions.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Excel sub with parameters (VB6 and Excel)

    Yes, obviously it is a special kind! Unfortunately, our present usage is calling it from another spreadsheet, and from another spreadsheet you CAN find RunWorkbook in ThisWorkbook. And because of a happy(?) accident some years ago, we now have lots of spreadsheets with exactly the same construct. Now we want to execute them from a VB environment not Excel, all of them will have to be changed. Not too difficult really, but I've a feeling we're going to be tripping over this for some time......

    Thanks.

Posting Permissions

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