Calling Excel sub with parameters (VB6 and Excel)

Topic

New Reply

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

Reply | Quote

Replies

Try

appExcel.Run "RunWorkbook", ConstLogin, ConstPass

Note the comma after the macro name.

Reply | Quote

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″‘

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote