Setting references in project

Here's my problem:

I have an AutoCad VBA project that incorporates Excel functions. Our
workstations currently have three different versions of Excel (2000, 2002,
and XP), but only one AutoCad version, actually ADT 3.3.

I repeatedly had problems with the project not finding the proper Excel
reference, so I isolated the Excel-related functions in a separate project,
then created three copies of it, one for each Excel version, with the
references set up in each. I then removed the Excel reference entirely from
my main project. When the user calls a function that uses Excel, the main
program determines the Excel version on the workstation, then loads the
appropriate project and runs the function.

Obviously this is a huge pain. Any changes in the Excel projects have to be
made in three different files, using three different computers (one for each
Excel version) to keep the references straight.

I recently tried determining what Excel version is installed before the main
project loads, then adding the proper Excel reference programatically

application.VBE.vbprojects("projname").references.addfromfile("whatever")

This works fine, except on exiting AutoCad, the user is prompted to save the
changes to the VBA project. The .dvb file is read-only, so there's no danger
of someone messing it up, but the prompt is useless, confusing and annoying.

Anybody got any ideas on how to deal with this situation? Any help will be
greatly appreciated.

 

Yes....Use late binding, which means you don't reference the Excel
Application, and you only need 1 project. The following code works with all
versions of Excel from 97 through XP.

Code:

Function IsExcelRunning() As Boolean
Dim objXL As Object
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set objXL = Nothing
Err.Clear
End Function


'Changed the way Excel is loaded per suggestion by
'Randall Rath - http://www.cadvault.com/
'which also added the "Function IsExcelRunning", found above

Public Sub AddData(DwgFullName As String, vData As Variant)
'***Begin code from Randall Rath******
Dim oXL As Object
Dim blnXLRunning As Boolean
blnXLRunning = IsExcelRunning()
If blnXLRunning Then
Set oXL = GetObject(, "Excel.Application")
Else
Set oXL = CreateObject("Excel.Application")
End If
'***End code from Randall Rath******
Dim oWb As Object
Dim oWs As Object
Dim sXlFile As String

sXlFile = "C:\My Documents\test.xls"

Set oWb = oXL.Workbooks.Open(sXlFile)
Set oWs = oWb.Worksheets("Sheet1")
'the rest of your code goes here

Remember to remove the Reference to Excel.
Enjoy!

 

A cheap trick I've found is that if you save the .dvb file with a reference to the oldest version of Excel, it will automatically reference the next highest version on the system. Works with AutoCAD as well.

For example:
Client has AutoCAD 2002/2004/2005 and Office 2000/2002 (XP)/2003

I would open the .dvb file on a station that has only AutoCAD 2002 and Office 2000 installed and save.

If another computer at the client opens the .dvb in AutoCAD 2004 with Office 2002(XP), the references to AutoCAD and Office type libraries will automatically update to the latest version.


The references will not automatically downgrade to an older type library version.

 

Jeff -

This works great. Thank you.

Tony

 

Yeah, I was doing this for a while, but of course had to make all program
changes on a computer with the oldest version of Excel, which mine didn't
have. And occasionally I'd brain-glitch and make the changes on my computer,
which messed up everybody with the older versions.

Thanks for the suggestion anyway, though.

 

No problem.

Just be aware that when you remove the Reference to the Excel library you are now running your code in Late Binding mode. This incurs a performance penalty due to VB(A) not knowing what kind of object it is working with. So for each dot (.) on an Excel object, the program has to ask COM if the object can actually do the call.

 

If you're just reading/writing data [IOW no Excel formatting] just use an
ado connection to Excel - its faster and easier.

-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...