Coming from VBA and new to VB.NET - Connect to Excel

[joaosantos]

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

[michael_robertson]

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

[Hallex · Accepted solution]

    <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

[joaosantos]

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