topic Re: Code that works with different versions of Excel in VBA Forum

Code that works with different versions of Excel

mdhutchinson — Tue, 16 Jan 2007 17:26:22 GMT

I made some lisp code to harvest the excel location on each workstation...

We have some machines that the Excel file is Excel9.olb and others that are Offic10/Excel.exe

C:/Program Files/Microsoft Office/Office/Excel9.olb
C:/Program Files/Microsoft Office/Office10/Excel.exe

On my machine where I am developing my application the reference is set to Microsoft Excel 10.0 Object Library.

How can I develop my app so it will work for either version?
… we have several out-of-house resources that we require to use our program code… what if they have Excel version other than these?

Re: Code that works with different versions of Excel

Anonymous — Tue, 16 Jan 2007 18:17:03 GMT

Here's what I use.....note that for this to work you must remove the
Reference to Excel. I found it's easiest to code WITH the reference, that
way you have the use of Intellisense, and once it's working go back and
change it to be late binding as I show here:

'' Modified 10/8/03 to remove early binding and
'' include late binding. Now should work with
'' any version Excel

Option Explicit
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.vbdesign.net/
'which also added the "Function IsExcelRunning", found above

Public Sub AddData(DwgFullName As String, vData As Variant)
Dim sXlFilNam As String

sXlFilNam = Left(DwgFullName, Len(DwgFullName) - 3) & "xls"
'***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")
oXL.Visible = False
oXL.UserControl = False
End If
'***End code from Randall Rath******
Dim oWb As Object
Dim oWs As Object

Set oWb = oXL.Workbooks.Open(sXlFilNam)

Set oWs = oWb.Worksheets("MySheet")
If oWs Is Nothing Then
MsgBox "The Excel file " & sXlFilNam & " is an old file, please delete
and try again."
GoTo exithere
End If
''''Your code here
exithere:
Set oWs = Nothing

oWb.Save: oWb.Close
Set oWb = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

wrote in message news:5453103@discussion.autodesk.com...
I made some lisp code to harvest the excel location on each workstation...

We have some machines that the Excel file is Excel9.olb and others that are
Offic10/Excel.exe

C:/Program Files/Microsoft Office/Office/Excel9.olb
C:/Program Files/Microsoft Office/Office10/Excel.exe

On my machine where I am developing my application the reference is set to
Microsoft Excel 10.0 Object Library.

How can I develop my app so it will work for either version?
. we have several out-of-house resources that we require to use our program
code. what if they have Excel version other than these?

Re: Code that works with different versions of Excel

mdhutchinson — Tue, 16 Jan 2007 19:39:40 GMT

Thanks you so much... this wil get me a leg up...
I follow what you are doing.

One question though...
With this late binding method (without the reference set)...
is it necessary to do:
Dim oWb As Object
Dim oWs As Object

Why can't the following be used?

Dim oWb As Excel.Workbook
Dim oWs As Excel.Worksheet

Re: Code that works with different versions of Excel

Anonymous — Tue, 16 Jan 2007 20:23:14 GMT

All I can say is "Try it..." 🙂 If it works, then fine, but I think that in
order to use the explict reference of Excel.Workbook you would need to have
the reference.....which will lead back to the "user defined type not
defined" error

wrote in message news:5453303@discussion.autodesk.com...
Thanks you so much... this wil get me a leg up...
I follow what you are doing.

One question though...
With this late binding method (without the reference set)...
is it necessary to do:
Dim oWb As Object
Dim oWs As Object

Why can't the following be used?

Dim oWb As Excel.Workbook
Dim oWs As Excel.Worksheet