MS Excel Reference Does Not Downgrade

[Clifford Bass]

Hi,

I have an Access 2002/2003 database that I maintain using Access 2007.
Recently I added in a reference to Microsoft Excel 12.0 Object Library so I
could use it in code. When I provide the database to people with Office /
Access 2002 and/or 2003, the reference does not correctly downgrade like the
other references. Such as Microsoft Office 12.0 Object Library which
downgrades correctly to the 11.0 and 10.0 version as appropriate. Because it
does not downgrade correctly it shows for them as missing and they then have
to go into the references, unselect the 12.0 reference, and select the 11.0
or 10.0 reference, as appropriate. This is not good. Please fix this bug in
2002 and 2003.

Thanks,

Clifford Bass

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/communitie...-9161-0d376237812c&dg=microsoft.public.access

 

[Tim Johnson]

Hi Clifford,

As far as I know, you cannot downgrade a reference.

I, too, am dealing with multiple versions of Excel and use VBA to send
reports and the like to Excel. The way that I deal with this very issue is
to remove the Reference altogether and use what is known as Late Binding.

You use late binding by altering the way that you've been declaring your
variables. Instead of referring to Excel specific objects, use the generic
Object declaration. For example:

Instead of -
Dim xlApp as Excel.Application
Dim xlWB as Excel.Workbook
Dim xlWS as Excel.Worksheet

You should use -
Dim xlApp as Object
Dim xlWB as Object
Dim xlWS as Object

This will eliminate any issues you may have with previous versions, assuming
that you are not using objects/functions that are present in the new version
but not in the old.

One issue you may come across using late binding, though, is that you loose
the feature that recognizes and auto-fills with properties/methods (e.g. when
you press "." on your keyboard after typing "xlApp.", there will be no
auto-fill).

If you need the reference to those properties/methods, you can always start
by developing your code the way you have been, and update it to late binding
once the code has been written - JUST BE SURE TO REMOVE THE REFERENCE BEFORE
DEPLOYMENT.

I hope this helps,
Tim

 

[Tim Johnson]

Also, while I think it should be apparent, this is worthy of adding - TEST,
TEST, TEST. While I have had great success using the method I've mentioned,
make sure that it works correctly with late binding before deploying.

I wish I could just edit my already verbose answer instead of having to type
a new reply...but whatever.

 

[Clifford Bass]

Hi Tim,

The downgrading is not my doing--it is Access itself that automatically
does it, depending on the version of Access being run. Well, it does it for
the most part, including as I mentioned for the Microsoft Office nn.n Object
Libaray, but not for the Excel library.

I do like the Intellisense (sp?) that happens when I have reference
defined. Heavens knows that I do not know all the methods and properties of
all the Excel objects, let alone their parameters. Your idea of using the
reference for development and then converting to late binding is a good one.
I expect I will make use of that idea until such time as Microsoft fixes the
problem for users of Access 2002 and 2003.

Thanks for the suggestion!

Clifford Bass

 

[Hans Up]

I do like the Intellisense (sp?) that happens when I have reference
defined. Heavens knows that I do not know all the methods and properties of
all the Excel objects, let alone their parameters. Your idea of using the
reference for development and then converting to late binding is a good one.
I expect I will make use of that idea until such time as Microsoft fixes the
problem for users of Access 2002 and 2003.

I've been looking for a way to deal with those issues. Here's what I
have currently.

Right below Option Explicit, I include:

#Const ProjectStatus = "PROD" 'DEV or PROD

Then a sample subroutine in that module could be:

Public Sub WkBookTest()
'Microsoft Excel Object Library
#If ProjectStatus = "DEV" Then
'early binding; assume Excel reference set ...
'the developer is here to deal with error if
'reference is NOT set
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
#Else 'expect PROD, but anything other that DEV
'late binding; remove Excel reference if set
Dim ref As Reference
On Error Resume Next
Set ref = References!Excel
If Err.Number = 0 Then
References.Remove ref
End If
On Error GoTo 0
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
#End If

Debug.Print "TypeName(objExcel): " & TypeName(objExcel)
objExcel.Quit
Set objExcel = Nothing
End Sub

 

[Clifford Bass]

Hi Hans,

I will have to give that a shot. Of course that adds in remembering to
set the ProjectStatus before releasing the database. Knowing me I will not
always remember. At least for starters.

Thanks much for sharing!

Clifford Bass

 

[Hans Up]

I will have to give that a shot. Of course that adds in remembering to
set the ProjectStatus before releasing the database. Knowing me I will not
always remember. At least for starters.

I hear ya. My original concept included a simple routine which would
use a regular expression to change the project status from DEV to PROD
in all the modules. And include running that routine as part of my
"release checklist". I didn't get that far, though. And now I'm
questioning whether this additional overhead is even worth the effort.
It seems too fiddly.

Thanks much for sharing!

You're welcome, Clifford. That paragraph you wrote neatly summarized my
opinions about references. So if my work-in-progress inspires you to
devise something better, I'll sure appreciate hearing about it.

Regards,
Hans

 

[Clifford Bass]

Hi Hans,

If I have any inspirations, I will indeed post back.

Clifford Bass

:

[snip]