Excel Reference

[Nick I]

Hello -

I have an application that uses a form and some buttons to open Microsoft
Excel. I am currently using functions from the Microsoft Excel 11.0 Object
Library.

A few of my users are experiencing problems b/c they don't have that library
on their machines. Generally, they have the Microsoft Excel 10.0 Object
Library. To fix the problem, all I would have to do is go to their machine,
unclick "MISSING: Microsoft Excel 11.0 Object Library", and re-click on
"Microsoft Excel 10.0 Object Library".

Is there a way I can programmatically check to see if the Microsoft Excel
11.0 Object Library exists, and if it doesn't, to use the Microsoft Excel
10.0 Object Library instead?

Thanks for your help!

- Nick

 

[Graham Mandeno]

Hi Nick

The best way to avoid trouble with this is to use "late binding". This
means that the code loads the object model from the library when the object
is created at runtime, not when the code is being compiled.

To do this, you must declare all the Excel objects "As Object" rather than
as their specific types. For example,
Dim oXLApp As Excel.Application
becomes
Dim oXLApp As Object

Of course, you cannot use the New keyword, so any instance of:
Set oXLApp = New Excel.Application
should be changed to
Set oXLApp = CreateObject("Excel.Application")

Also, if you use any built-in constants from the object library then you
must declare them explicitly. For example:
Const xlCenter = -4108

Now the disadvantage of this is that you lose the intellisense and the
compile-time type checking which makes the code development so much easier.

So what I do is use conditional compilation blocks:

#Const ExcelRefLoaded = True
....
#If Not ExcelRefLoaded Then
Const xlCenter = -4108
#End If
....
#If ExcelRefLoaded Then
Dim oXLApp As Excel.Application
Dim oXLSht As Excel.Worksheet
#Else
Dim oXLApp As Object
Dim oXLSht As Object
#End If

When you are developing, load the reference and set the compile-time #Const
to True.

When you want to distribute your application, remove the reference and set
the #Const to False.

 

[Nick I]

Graham -

Here's the small segment of my Excel code that I'm talking about. Would all
I need to do is change the xlApp, xlSheet, and xlWorkbook variables to
objects? Or would I need to change something else?
---------------------------------------------------------------------
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open("O:\CC Managers\metrics\" & TeamName &
" CSR.xls")
Set xlSheet = xlWorkbook.Sheets(1)

Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
-------------------------------------------------------------------------------

Thanks a ton for your help!

Hi Nick

The best way to avoid trouble with this is to use "late binding". This
means that the code loads the object model from the library when the object
is created at runtime, not when the code is being compiled.

To do this, you must declare all the Excel objects "As Object" rather than
as their specific types. For example,
Dim oXLApp As Excel.Application
becomes
Dim oXLApp As Object

Of course, you cannot use the New keyword, so any instance of:
Set oXLApp = New Excel.Application
should be changed to
Set oXLApp = CreateObject("Excel.Application")

Also, if you use any built-in constants from the object library then you
must declare them explicitly. For example:
Const xlCenter = -4108

Now the disadvantage of this is that you lose the intellisense and the
compile-time type checking which makes the code development so much easier.

So what I do is use conditional compilation blocks:

#Const ExcelRefLoaded = True
....
#If Not ExcelRefLoaded Then
Const xlCenter = -4108
#End If
....
#If ExcelRefLoaded Then
Dim oXLApp As Excel.Application
Dim oXLSht As Excel.Worksheet
#Else
Dim oXLApp As Object
Dim oXLSht As Object
#End If

When you are developing, load the reference and set the compile-time #Const
to True.

When you want to distribute your application, remove the reference and set
the #Const to False.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello -

I have an application that uses a form and some buttons to open Microsoft
Excel. I am currently using functions from the Microsoft Excel 11.0 Object
Library.

A few of my users are experiencing problems b/c they don't have that
library
on their machines. Generally, they have the Microsoft Excel 10.0 Object
Library. To fix the problem, all I would have to do is go to their
machine,
unclick "MISSING: Microsoft Excel 11.0 Object Library", and re-click on
"Microsoft Excel 10.0 Object Library".

Is there a way I can programmatically check to see if the Microsoft Excel
11.0 Object Library exists, and if it doesn't, to use the Microsoft Excel
10.0 Object Library instead?

Thanks for your help!

- Nick

 

[Graham Mandeno]

Hi Nick

Absolutely! If the three xl... variables are all declared "As Object" then
you have no need for the reference.

However, I really recommend you go the conditional compilation way and then
you can enjoy the best of both worlds

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham -

Here's the small segment of my Excel code that I'm talking about. Would
all
I need to do is change the xlApp, xlSheet, and xlWorkbook variables to
objects? Or would I need to change something else?
---------------------------------------------------------------------
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open("O:\CC Managers\metrics\" & TeamName
&
" CSR.xls")
Set xlSheet = xlWorkbook.Sheets(1)

Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
-------------------------------------------------------------------------------

Thanks a ton for your help!

Hi Nick

The best way to avoid trouble with this is to use "late binding". This
means that the code loads the object model from the library when the
object
is created at runtime, not when the code is being compiled.

To do this, you must declare all the Excel objects "As Object" rather
than
as their specific types. For example,
Dim oXLApp As Excel.Application
becomes
Dim oXLApp As Object

Of course, you cannot use the New keyword, so any instance of:
Set oXLApp = New Excel.Application
should be changed to
Set oXLApp = CreateObject("Excel.Application")

Also, if you use any built-in constants from the object library then you
must declare them explicitly. For example:
Const xlCenter = -4108

Now the disadvantage of this is that you lose the intellisense and the
compile-time type checking which makes the code development so much
easier.

So what I do is use conditional compilation blocks:

#Const ExcelRefLoaded = True
....
#If Not ExcelRefLoaded Then
Const xlCenter = -4108
#End If
....
#If ExcelRefLoaded Then
Dim oXLApp As Excel.Application
Dim oXLSht As Excel.Worksheet
#Else
Dim oXLApp As Object
Dim oXLSht As Object
#End If

When you are developing, load the reference and set the compile-time
#Const
to True.

When you want to distribute your application, remove the reference and
set
the #Const to False.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello -

I have an application that uses a form and some buttons to open
Microsoft
Excel. I am currently using functions from the Microsoft Excel 11.0
Object
Library.

A few of my users are experiencing problems b/c they don't have that
library
on their machines. Generally, they have the Microsoft Excel 10.0 Object
Library. To fix the problem, all I would have to do is go to their
machine,
unclick "MISSING: Microsoft Excel 11.0 Object Library", and re-click on
"Microsoft Excel 10.0 Object Library".

Is there a way I can programmatically check to see if the Microsoft
Excel
11.0 Object Library exists, and if it doesn't, to use the Microsoft
Excel
10.0 Object Library instead?

Thanks for your help!

- Nick