topic Re: Coming from VBA and new to VB.NET - Connect to Excel in .NET Forum

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

joaosantos — Thu, 19 May 2011 16:52:22 GMT

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

End Using

End Sub

End Class

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

michael_robertson — Thu, 19 May 2011 17:48:08 GMT

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

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

Hallex — Sat, 21 May 2011 17:12:56 GMT

    <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

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

joaosantos — Mon, 23 May 2011 22:21:50 GMT

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