Hi
I searched this group but didn't find the answer for this problem.
I want to export the layers names to Excel, as I used in VBA. I'm using AutoCAD 2011 (64b), VB Express 2010, Office 2003 (32b). I ran this routine in XP 64b and W7 64 with the same problem.
No problem in building the DLL, or loading it to AutoCAD. When running the command, Excel opens, but no workbook is created and I get this Unhandled exception error:
System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
at Microsoft.Office.Interop.Excel.Workbooks.Add(Object Template)
at Ligacao_Excel.Class1.Proj1() in d:\documents and settings\joão santos\visual studio 2010\Projects\Ligacao-Excel\Ligacao-Excel\Class1.vb:line 22
Line 22 is : folhaExcel = objExcel.ActiveSheet
Any help will be mostly appreciated. To program in VBA was much easier, but I recognize the .NET power, as soon as this can be dominated 🙂
João Santos
Portugal
Imports Autodesk.AutoCAD.ApplicationServices
Imports Autodesk.AutoCAD.DatabaseServices
Imports Autodesk.AutoCAD.EditorInput
Imports Autodesk.AutoCAD.Runtime
Public Class Class1
<CommandMethod("LAY2XLS")> _
Public Sub Proj1()
Dim db As Database
Dim bt As BlockTable
Dim ms As BlockTableRecord
Dim myDWG As Document
myDWG = Application.DocumentManager.MdiActiveDocument
Dim ed As Editor = myDWG.Editor
Dim objExcel As Microsoft.Office.Interop.Excel.Application
Dim folhaExcel As Microsoft.Office.Interop.Excel.Worksheet
objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add()
folhaExcel = objExcel.ActiveSheet
Using trans As Transaction = myDWG.TransactionManager.StartTransaction()
db = Application.DocumentManager.MdiActiveDocument.Database
bt = trans.GetObject(db.BlockTableId, OpenMode.ForRead)
ms = trans.GetObject(bt(BlockTableRecord.ModelSpace), OpenMode.ForWrite)
Dim layerTB As LayerTable
layerTB = trans.GetObject(db.LayerTableId, OpenMode.ForRead)
Dim contador As Integer = 1
For Each objecto As ObjectId In layerTB
Dim MyLayer As LayerTableRecord
MyLayer = trans.GetObject(objecto, OpenMode.ForRead)
folhaExcel.Cells(contador, 1).value = MyLayer.Name
contador = contador + 1
Next
End Using
End Sub
End Class
Solved! Go to Solution.
Solved by Hallex. Go to Solution.
The 32bit Office & 64bit AutoCAD combination can cause problems with COM.
If your Excel files are in xlxs format you can totally skip using COM and requiring Excel to be installed on the machine by using the OpenXML.SDK from Microsoft. It allows you to read/write Excel and Word files independent on if the app is on the machine.
Here's the link to the Microsoft info: http://msdn.microsoft.com/en-us/library/bb448854.aspx
<CommandMethod("LAY2XLS")> _ Public Sub Proj1() Dim db As Database Dim bt As BlockTable Dim ms As BlockTableRecord Dim myDWG As Document Dim layersList As List(Of String) myDWG = Application.DocumentManager.MdiActiveDocument Dim ed As Editor = myDWG.Editor layersList = New List(Of String)(New String() {}) Dim oldCult = CultureInfo.CurrentCulture() '' This line is very important! Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US") '<-- change culture on whatever you need Try Using trans As Transaction = myDWG.TransactionManager.StartTransaction() db = Application.DocumentManager.MdiActiveDocument.Database bt = trans.GetObject(db.BlockTableId, OpenMode.ForRead) ms = trans.GetObject(bt(BlockTableRecord.ModelSpace), OpenMode.ForRead) Dim layerTB As LayerTable layerTB = trans.GetObject(db.LayerTableId, OpenMode.ForRead) For Each objecto As ObjectId In layerTB Dim MyLayer As LayerTableRecord MyLayer = DirectCast(trans.GetObject(objecto, OpenMode.ForRead), LayerTableRecord) layersList.Add(MyLayer.Name) Next End Using Dim objExcel As Microsoft.Office.Interop.Excel.Application Dim folhaExcel As Microsoft.Office.Interop.Excel.Worksheet objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Add() folhaExcel = objExcel.ActiveSheet folhaExcel.Name = "Layers" Dim contador As Integer = 1 For Each layrName As String In layersList folhaExcel.Cells(contador, 1).value = layrName contador = contador + 1 Next Catch ex As System.Exception MsgBox(ex.Message & ex.StackTrace) If ex.InnerException IsNot Nothing Then MsgBox("Inner excepyion: " & vbLf & ex.InnerException.Message) End If Finally Thread.CurrentThread.CurrentCulture = oldCult End Try End Sub
Try again
Thanks a lot Hallex. It worked perfectly. Now I will dig to understand this solution, never saw some of these instructions. Anyway I'm quite new to VB.
Also thanks to Michael for previous post. I went to the site but didn't had the time to search. But I will also study this clue.
Cheers
João Santos