Excel 2002 Object Library Location

[windowguy]

I am a novice at LISP and am writing a routine to extract information from
AutoCAD to Excel. I am running AutoCAD 2000i and Excel 2002.

Thanks to the discussion groups I have been able to write the attached
routine, but I cannot get it to run with Excel 2002. I have modified the
routine to run on another workstation which still has Excel 2000 by changing
the Excel.exe extension to Excel9.olb extension, and it runs fine. When I
run it in Excel 2002 I get automation errors.

Is there a special olb file for Excel 2002?


(vl-load-com)
(if
(not excm-open)
(vlax-import-type-library
:tlb-filename "C:/Program Files/Microsoft Office/Office10/Excel.exe"
:methods-prefix "msxl-"
roperties-prefix "msxl-"
:constants-prefix "msxl-"
)
)

 

[xlhelp]

This answer from Chip Pearson:

In Excel 2002, the typelib is in the Excel.exe file. I use the following code to support 97, 2000, and 2002.

Dim TLInfo_XL As TLI.TypeLibInfo
Select Case Val(Application.Version)
Case 8
Set TLInfo_XL = _
TLApp.TypeLibInfoFromFile(Application.Path & "\excel8.olb&quot
Case 9
Set TLInfo_XL = _
TLApp.TypeLibInfoFromFile(Application.Path & "\excel9.olb&quot
Case 10
Set TLInfo_XL = _
TLApp.TypeLibInfoFromFile(Application.Path & "\excel.exe&quot
Case Else
MsgBox "Can't get TypeLib for version: " & Application.Version
Exit Sub
End Select

 

[ara741]

This code below is based on Office11, Excel.exe. for Excel9.OLB how should I refer the object in the program

Thanks,


Public XLApp As Excel.Application
Dim fso As New FileSystemObject, fil As File, ts As TextStream
Dim Jobpath As String
Dim infilenoext As String

'-----------------------------------------
'Main function
'-----------------------------------------
Sub Main()
Dim filname As String
Dim sfilename_input
Jobpath = "W:\Data\Customer\sweep\"
sfilename_input = Dir(Jobpath & "input\*.xls")
infilenoext = sfilename_input
filname = Jobpath & "input\" & infilenoext
Do While infilenoext <> ""

' Call OpenXL("C:\excel.xls")
Call OpenXL(filname)
Call createfile
Call ReadXL
Call CloseXL
infilenoext = Dir
Loop
End Sub


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' OPEN XL
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub OpenXL(ByRef strfilename As String)
Set XLApp = New Excel.Application
'If App.Path = "C:\" Then
' XLApp.Workbooks.Open App.Path + "TestSheet.xls", ReadOnly:=True
'Else
XLApp.Workbooks.Open strfilename, ReadOnly:=True
'End If
'XLApp.Visible = True
End Sub


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CLOSE XL
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub CloseXL()
Dim vFileName As String
' vFileName = Format(Now, "yymmdd")
' XLApp.ActiveWorkbook.SaveAs ("C:\Done\XL" + vFileName + ".xls")
' XLApp.ActiveWorkbook.Close (wdDoNotSaveChanges)
XLApp.Quit
Set XLApp = Nothing

End Sub


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' READ XL DATA
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ReadXL()
'----------------------------------------------------------
' variables for storing Excel data
'----------------------------------------------------------
Dim Var00 As String, Var01 As String, Var02 As String
Dim var03 As String, var04 As String, var05 As String
Dim var06 As String, var07 As String, var08 As String
Dim var09 As String, var10 As String, amt As String
Dim Inrec As String, reccount As Integer
Dim str1 As String
reccount = 0
With XLApp
.Sheets(1).Select
.Range("A1").Select
End With

Do
If Not InStr(XLApp.ActiveCell.Offset(0, 0), "Acc") > 0 Then

With XLApp.ActiveCell
Var00 = .Offset(0, 0).Value ' Assign Column 1
Var01 = .Offset(0, 1).Value ' Assign Column 2
Var02 = .Offset(0, 2).Value ' Assign Column 3
var03 = .Offset(0, 3).Value ' Assign Col 4
var04 = .Offset(0, 4).Value ' Assign Col 5
var05 = .Offset(0, 5).Value ' Assign Col 6
var06 = .Offset(0, 6).Value ' Assign Col 7
var07 = .Offset(0, 7).Value ' Assign Col 8
var08 = .Offset(0, 8).Value ' Assign Col 9
var09 = .Offset(0, 9).Value ' assign col 10
var10 = .Offset(0, 10).Value ' assign col 11
amt = Replace(.Offset(0, 11).Value, "$", " ") ' Assign Col 12
var12 = .Offset(0, 12).Value ' Assign Col 13

' .Offset(1, 0).Select ' Move down one row
End With

'amt = Val(amt)
var11 = Trim(amt)
If InStr(var11, ".") > 0 Then
str1 = Space(13 - Len(var11)) & Trim(var11)
Else
str1 = Space(10 - Len(var11)) & Trim(var11) & ".00"
End If
' Replace(Space(8 - Len(CStr(strServiceMaxUnits)))," ","0")
' forming the record
Inrec = Trim(Var00) & _
Trim(Var01) & Space(31 - Len(Trim(Var01))) & _
Trim(Var02) & Space(14 - Len(Trim(Var02))) & _
Trim(var03) & Space(20 - Len(Trim(var03))) & _
Trim(var04) & Space(20 - Len(Trim(var04))) & _
Trim(var05) & Space(35 - Len(Trim(var05))) & _
Trim(var06) & Space(35 - Len(Trim(var06))) & _
Trim(var07) & Space(25 - Len(Trim(var07))) & _
Trim(var08) & Space(2 - Len(Trim(var08))) & _
Trim(var09) & Space(5 - Len(Trim(var09))) & _
Trim(var10) & Space(4 - Len(Trim(var10))) & _
str1 & _
Trim(var12) & Space(3 - Len(Trim(var12)))
' Replace(Space(13 - Len(var11)), " ", "0") & Trim(var11) & _
' Space(10 - Len(var11)) & Trim(var11) & ".00"
' writing the record to file.
ts.WriteLine Inrec
Inrec = ""
reccount = reccount + 1
End If

With XLApp.ActiveCell
.Offset(1, 0).Select ' Move down one row
End With

Loop Until Trim(XLApp.ActiveCell.Value) = ""
ts.Close ' close text stream
' MsgBox reccount

End Sub

'-----------------------------------------------------
'Creating ouput file for allconnect
'-----------------------------------------------------

Private Sub createfile()
Dim apath() As String, arrpath As String
Set fso = CreateObject("Scripting.FileSystemObject")
' fso.CreateTextFile (App.Path + "C:\NORTH\RECV\outputfile.txt")
apath = Split(App.Path, "\")
' arrpath = apath(0) & "\" & apath(1)
arrpath = Jobpath

fso.CreateTextFile (arrpath & "\RECV\NonMarSwep.txt")
Set fil = fso.GetFile(arrpath & "\RECV\NonMarSwep.txt")
Set ts = fil.OpenAsTextStream(ForWriting)

End Sub