Calling Excel from a Word with VBA

[joes]

Hello

I am newcomer to VBA. I have to open from Word an Excel file and to
call a custom function from it. It would be great if someone could show
me here some example code or provide me some useful links.

Many thanks
Mark Egloff

 

[Doug Robbins]

See the article "Control Excel from Word" at:

http://word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[joes]

Many thanks looks good. I have one futher question. How can I call
acustom function from this opened Excel?

thanks
Mark

Dim oWB As Excel.Workbook

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

 

[Doug Robbins]

Not quite sure what you mean by a custom function, but via that method, you
will have access to the Excel object model.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[joes]

Sorry for my confusion. I have a vb function in my Excel called
"myFunc". How can I call that from Word? simple like this example ?

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=W­orkbookToWorkOn)
oWB.myFunc("foo")

thanks

regards
Mark

 

[Helmut Weber]

Hi Joes,

with security level set to low for both Word and Excel,
and having saved all, shut both programms, and opened them again,
like this:

' Word

Sub Macro7()
Dim l As Long
Dim oExc As Excel.Application
Set oExc = GetObject(, "Excel.Application")
' excel already running
' and the workbook containing the function is open

l = oExc.Run("test")
MsgBox l
End Sub

' Excel
' function in the open workbook

Public Function test() As Long
test = 12345
End Function


HTH


Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[joes]

Many thanks. That is what I have looked for, great.
Mark

 

[Dawn Crosier]

Mark -

oXL.Application.Run ("MacroName")

Should work for you. If you need to pass arguments to the
Function, intellisense should be able to help prompt you for your
arguments.

From Help:
Example
This example shows how to call the function macro My_Func_Sum,
which is defined on the macro sheet Mycustom.xlm (the macro sheet
must be open). The function takes two numeric arguments (1 and 5,
in this example).
mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)
MsgBox "Macro result: " & mySum

--
Dawn Crosier
Microsoft MVP
"Education Lasts a Lifetime"

This message is posted to a newsgroup. Please post replies and
questions to the newsgroup so that others can learn as well.

Sorry for my confusion. I have a vb function in my Excel called
"myFunc". How can I call that from Word? simple like this example
?

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=W­orkbookToWorkOn)
oWB.myFunc("foo")

thanks

regards
Mark