Welcome to DLL Hell :-(
The way to make your references version-independent is to remove the
reference and use late binding. This means that all your object variables
are declared "As Object" instead of using the named object type (eg
"Excel.Application"), and all objects must be created using the CreateObject
function, instead of the New keyword. Also, any constants that you are
using from the object library need to be declared explicitly.
Now, this creates a huge pain in the fleshy parts when you are developing,
because you no longer have the advantages of intellisense and complile-time
type checking. The solution is conditional compilation.
Declare a conditional compilation constant at the start of your module:
#Const EarlyBinding = 1
Then conditionally declare any constants and enums you are using - for
example:
#If EarlyBinding Then
Public Const xlCenter = -4108
Public Enum XlPageOrientation
xlLandscape = 2
xlPortrait = 1
End Enum
#End If
Now, in any declaration block where you declare named object variables, add
an alternative section declaring plain objects:
#If EarlyBinding Then
Dim xlApp As Excel.Application
Dim xlWkb As Excel.WorkBook
Dim xlSht As Excel.WorkSheet
#Else
Dim xlApp As Object
Dim xlWkb As Object
Dim xlSht As Object
#End If
Make sure that any use of the New keyword is changed:
Set xlApp = New Excel.Application
becomes
Set xlApp = CreateObject("Excel.Application")
Now, you can continue developing using intellisense and compiler checking.
When you are ready to distribute the app, remove the reference, change the
EarlyBinding constant to 0, and recompile.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
faberk said:
I have written a module that dumps a DAO.Recordset into an excel workbook,
formats columns, adds totals to columns and rows, etc. It was created
using
MS Acess 2003. The routine works flawlessly for users using Office 2003,
however, for other users using Office 2000, they receive an error message
that indicates the reference object "Microsoft Excel 11.0 Object Library"
is
missing.
How do/should I make this library availble to my Office 200 users?. Any
suggestions?
