How to reference Excel 10.0 from Word

[jdw]

I need to check if a user's machine has a reference to Excel before
executing a macro in Word. This is the following code:

If VBE.ActiveVBProject.References("Excel").IsBroken Then

If Excel is referenced, there are no complaints. If I uncheck it from
references, then I get the "Index out of bounds" or something similar.
Can anyone help?

[Åsa Holmgren]

If Excel is not referenced it's not part of the References collection. Try
this code:

Dim obj As Object
Dim bExcelRefOK As Boolean

For Each obj In VBE.ActiveVBProject.References
If obj.Name = "Excel" And obj.IsBroken = False Then
bExcelRefOK = True
End If
Next obj


/Asa

Post by jdw
I need to check if a user's machine has a reference to Excel before
If VBE.ActiveVBProject.References("Excel").IsBroken Then
If Excel is referenced, there are no complaints. If I uncheck it from
references, then I get the "Index out of bounds" or something similar.
Can anyone help?

[James Wagman]

Thanks, that worked. How then can I add a reference to Excel
programmatically if missing?

[Åsa Holmgren]

Add an Excel reference with this code:

VBE.ActiveVBProject.References.AddFromGuid _
"{00020813-0000-0000-C000-000000000046}", 1, 4

/Asa

Post by James Wagman
Thanks, that worked. How then can I add a reference to Excel
programmatically if missing?

[James Wagman]

How did you find the GUID? Will it work regardless of Excel version?

[Åsa Holmgren]

I found the GUID earlier by serching the newsgroups... The GUID works
regardless of Excel version, but changing the "Minor" parameter from "4" to
"3" causes VBA to load Excel 9 instead of Excel 10.

Post by James Wagman
How did you find the GUID? Will it work regardless of Excel version?

[jdw]

Post by Åsa Holmgren
I found the GUID earlier by serching the newsgroups... The GUID works
regardless of Excel version, but changing the "Minor" parameter from "4" to
"3" causes VBA to load Excel 9 instead of Excel 10.

Post by James Wagman
How did you find the GUID? Will it work regardless of Excel version?

I have one more question regarding the referencing. In my code, I have
a sub which returns an Excel worksheet, and the sub is full of
references to excel. It seems that the VBA engine starts looking for
these references before it runs any other code, so my effort to
programmatically add the reference is for naught. Anyone have any
workarounds?

[Åsa Holmgren]

Try to use late binding instead of early binding:

Dim objExcel As Object
Dim objBook As Object
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open("D:\Mybook.xls")

instead of

Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Set objExcel = New Excel.Application
Set objBook = objExcel.Workbooks.Open("D:\Mybook.xls")

Remove all references to Excel when using late binding.

/Asa

Post by jdw

Post by Åsa Holmgren
I found the GUID earlier by serching the newsgroups... The GUID works
regardless of Excel version, but changing the "Minor" parameter from "4" to
"3" causes VBA to load Excel 9 instead of Excel 10.

Post by James Wagman
How did you find the GUID? Will it work regardless of Excel version?

I have one more question regarding the referencing. In my code, I have
a sub which returns an Excel worksheet, and the sub is full of
references to excel. It seems that the VBA engine starts looking for
these references before it runs any other code, so my effort to
programmatically add the reference is for naught. Anyone have any
workarounds?

[James]

thanks, I think that will do the trick.