WorksheetFunction with VBA

[Ghislain Marcotte]

Hello,

I'm trying to use Excel 2000 functions in a Word 2000 macro. My
version of Office is the French one.

For example, the following code does work:

Dim excelApp As Excel.Application
Set excelApp = CreateObject("Excel.Application")
Set table_ac = excelApp.Workbooks.Open ("c:\myfile.xls")
MsgBox table_ac.WorksheetFunction.sheets("Feuil1").cells(10,5)

Now the problem is that other (all?) other WorksheetFunction's do work
in an Excel macro, but not in a Word macro, either in their English of
French VBA versions.

For example, ROUND works in an Excel Macro, yet it is not listed in
the available WorksheetFunctions of Excel VBA. (The function which is
listed is its French equivalent, ARRONDI).

So,
Application.WorksheetFunction.Round(5.96,1) works in an Excel macro,

but neither
table_ac.WorksheetFunction.Round(5.96,1) nor
table_ac.WorksheetFunction.Arrondi(5.96,1)
work in a Word macro!

Same goes for VLOOKUP (RECHERCHEV in French), for instance.

Any idea would be very appreciated!

Thanks,
Ghislain.

 

[Jim Cone]

Ghislain,

I have some suggestions, however, I have never tried to use
Excel Worksheet Functions in Word.

1. "WorksheetFunction" in not a property of the
Worksheet, it is a property of the Application object.

2. Therefore...
MsgBox table_ac.WorksheetFunction.sheets("Feuil1").cells(10,5)
should be...
MsgBox table_ac.Sheets("Feuil1").cells(10,5).Value

3. In addition...
table_ac.WorksheetFunction.Round(5.96,1)
should be...
excelApp.WorksheetFunction.Round(5.96,1)

Finally, even if you make the suggested changes,
I don't know whether worksheet functions will work in Word?
Please let us know.

Regards,
Jim Cone
San Francisco, USA

 

[Ghislain Marcotte]

Hi Jim,

Thanks A LOT for your answers, my Word macro now works perfectly! I
don't think I could have found my mistake without your help.

Regards,
Ghislain.