Calling a procedure in a different workbook (Excel)

[NicolaasH]

Hi,

I am having trouble calling procedures in a different workbook.

From a Sub in Workbook1, I want to run the procedure Public Sub Test() in Module1 of Workbook2. Workbook2 is already open.

What should be the code for this? Thanks!
Nicolaas

 

[combo]

Try either Application.Run method or change standard 'VBAProject' project name to something unique and next add reference to this project.

combo

 

[NicolaasH]

I want to call the workbook filename, since there will be updated versions with different file names but the same VBA Project name. I use a GetOpenFileName / Open procedure to select the right sheet.

I was assuming something like this:
Procedure in "Workbook1"

Sub TestSub1 ()

Workbooks("Workbook2").Module1.TestSub2

End Sub

obviously this does not work...so what is the right way of calling TestSub2?

 

[combo]

So the 'Run' method can be implemented.

combo

 

[lestatdelioncourt]

this seems to work:

Private Sub CommandButton1_Click()

ActiveSheet.Cells(1, 1).FormulaR1C1 = "=Workbook1.xls!Test()"

End Sub

 

[lestatdelioncourt]

this does too:

Run ("workbook1.xls!Test()")

 

[NicolaasH]

thx, I got it to work. Although I do find it cumbersome because the arguments are not passed on, since it is not called like a normal object method.

 

[combo]

Have you checked the help file? Do you mean that excel 'Run' method does not accept arguments?

combo

 

[Rofeu]

Hi,

the run method doesn't allow passing objects, but other than that you can specify all the args you want. And you can always get around the objects by just passing the objectnames and redimensioning them as objects in the called function.

Cheers,

Roel

 

[cresbydotcom]

I may have to start another thread but maybe answers will appear here

How about invoking a marco in Word (normal.dot is assumed here) from Excel? (and vice versa maybe)? Run doesn't do it. Not the way I try.

I run Excel macros in active spreadheets/sheets with other methods (at home anyway) but feel confident to multi app now. So how is it done?

 

[combo]

With early binding from excel:

Dim wdApp As Word.Application
Set wdApp = New Word.Application
' "WordMacro" is a procedure (without arguments) in Normal.dot
wdApp.Run "WordMacro"
wdApp.Quit
Set wdApp = Nothing

combo

 

[cresbydotcom]

I will play with this

Essentially I have an existing word doc loaded (master doc) that I SaveAs then want to run the normal.dot macro and set bookmarked text and then save and close. The SaveAs is there just in case the macro fails. It gets left that way so i can change the macro safely.
But as I ever find - you have to know enough of the answer to ask the question with Help files (and real people sometimes).

there is a tide in the affairs of man that you Canute ignore.................

 

[cresbydotcom]

Nope
Execl 2002 (10.26.....) Win Xp VB 6.3
can't cope with "user defined type" on the line

Dim wdApp As Word.Application

can't see how to make it use the type - help is er no help. Object browser no help either.

Time to upgrade - if the company can afford it.

there is a tide in the affairs of man that you Canute ignore.................

 

[PHV]

Menu Tools -> References ...
Tick Microsoft Word Library


Alternatively, use late binding.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cresbydotcom]

Word Library already ticked

I have Macros in Word and Excel on the company machine and at home - but never tried running from one App to another - only Excel to Excel.

I would expect combo's code to work as posted if it was going to. Can't think of other boxes to check. I will move the code into a module from a sheet and try class modules (not sure of the implications there).

there is a tide in the affairs of man that you Canute ignore.................