Setting references in project

[Tony Burba]

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.

[Jeff Mishler]

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.

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!

--
Jeff
check out www.cadvault.com
"Tony Burba" <tburba@mediaone.net> wrote in message
news:41b78865$1_1@newsprd01...

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

[rwilkins]

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.

[Tony Burba]

Jeff -

This works great. Thank you.

Tony


"Jeff Mishler" <jeff_m@cadvault.com> wrote in message
news:41b78ef4_1@newsprd01...

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.

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!

--
Jeff
check out www.cadvault.com
"Tony Burba" <tburba@mediaone.net> wrote in message
news:41b78865$1_1@newsprd01...
Anybody got any ideas on how to deal with this situation? Any help will
be greatly appreciated.

[Tony Burba]

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.



"rwilkins" <nospam@address.withheld> wrote in message
news:20598092.1102607702018.JavaMail.jive@jiveforu m2.autodesk.com...

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.

[rwilkins]

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.

[Mike Tuersley]

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...