Export Points from AutoCAD® to CSV file using VBA

Today I'm going to show you how to use a bit of simple VBA to export data to a CSV file. It's really simple, and although there are other ways of achieving the same thing, this way allows great flexibility, and is very versatile.

Firstly, I'll set up a drawing with a few points in it. These points will be what we will export:

The next step is to open the VBA editing window using the VBAIDE command. Of course, if you're using AutoCAD® 2010+ you'll need to download the VBA add-on. Once open, right click in the project explorer window and create a new module.

Now for the code. I was going to explain the code, but as I've commented it pretty well, I don't really think I need to. Take note of the part about the FileSystemObject, as you'll need to follow the instructions before the code will work. Do ask if you have any other questions.

Option Explicit

Sub ExportPoints()
    'Declare variables
    Dim currentSelectionSet As AcadSelectionSet
    Dim ent As AcadEntity
    Dim pnt As AcadPoint
    Dim csvFile As String
    Dim FSO As FileSystemObject
    Dim textFile As TextStream

    'Create a reference to the selection set of the currently selected objects
    Set currentSelectionSet = ThisDrawing.ActiveSelectionSet

    'Check if anything is selected, and give exit with a warning if not
    If currentSelectionSet.Count = 0 Then
        ThisDrawing.Utility.Prompt "There are no currently selected objects. Please select some points to export, and run this command again." & vbNewLine
    End If

    'Use a For Each statement to look through every item in CurrentSelectionSet
    For Each ent In currentSelectionSet
        'In here, ent will be one of the selected entities.

        'If ent is not a point object, we should ignore it
        If TypeOf ent Is AcadPoint Then
            'Only points will make it this far

            'Now that we know we are dealing with a point,
            'we can use the specific AcadPoint type of variable.
            Set pnt = ent
            'You'll notice that after doing this, you have more
            'intellisense methods when you type "pnt."

            'Add a line to the string variable csvFile.
            'We are concatenating two numbers together with a comma in between,
            'and adding a new line character at the end to complete the row.
            csvFile = csvFile & pnt.Coordinates(0) & "," & pnt.Coordinates(1) & vbNewLine

            'Saying that csvFile = csvFile & whatever is a useful
            'way to repeatedly add to the end of a string variable.

        End If

    Next

    'Write the contents of the csvFile variable to a file on the C:\ with the same name

    'FileSystemObjects are really useful for manipulating files
    'But, you'll need a reference to the Microsoft Scripting Runtime in your VBA project.
    'Go Tools>References, and select the Microsoft Scripting Runtime.

    'Create a new File System Object
    Set FSO = New FileSystemObject

    'Using FSO.CreateTextFile, create the text file csvFile.csv,
    'and store a reference to it in the variable textFile
    Set textFile = FSO.CreateTextFile("C:\csvFile.csv")

    'Write the string variable csvFile to textFile
    textFile.Write csvFile

    'Close textFile, as we are finished with it.
    textFile.Close

    'Alert the user that the file has been created
    ThisDrawing.Utility.Prompt "Points have been exported to C:\csvFile.csv" & vbNewLine

End Sub

And there we have it. You could of course add more conditions in there - say, nest another IF statement in the middle that filters out say only red points. It doesn't have to be points either - this process will work with any properties of any AutoCAD® entity.

Have fun, and if you haven't already, please do subscribe below!

Will

Comments

john coon
2011-05-11 14:08:10

Will,

one of the most powerful parts of a routine like this is that you can also pull the z value and of other data attached to the point. also changing the point to a blockrefence in the drawing you can get all the x,y,z, data as well as attributes.

well done

Sub getatts_Extract()
Dim Excel As Excel.Application
Dim ExcelSheet As Object
Dim ExcelWorkbook As Object

Dim RowNum As Integer
Dim Header As Boolean
Dim elem As AcadEntity
Dim Array1 As Variant
Dim Count As Integer

' Launch Excel.
Set Excel = New Excel.Application

' Create a new workbook and find the active sheet.
Set ExcelWorkbook = Excel.Workbooks.Add
Set ExcelSheet = Excel.ActiveSheet
ExcelWorkbook.SaveAs "c:\temp\Attribute.xls"

RowNum = 1
Header = False
' Iterate through model space finding
' all block references.
For Each elem In ThisDrawing.ModelSpace
    With elem
        ' When a block reference has been found,
        ' check it for attributes
        If StrComp(.EntityName, "AcDbBlockReference", 1) _
            = 0 Then
            If .HasAttributes Then
                ' Get the attributes
                Array1 = .GetAttributes
                ' Copy the Tagstrings for the
                ' Attributes into Excel
                For Count = LBound(Array1) To UBound(Array1)
                    If Header = False Then
                        If StrComp(Array1(Count).EntityName, _
                              "AcDbAttribute", 1) = 0 Then
                            ExcelSheet.Cells(RowNum, _
                                Count + 1).Value = _
                                Array1(Count).TagString
                        End If
                    End If
                Next Count
                RowNum = RowNum + 1
                For Count = LBound(Array1) To UBound(Array1)
                    ExcelSheet.Cells(RowNum, Count + 1).Value _
                                = Array1(Count).TextString
                Next Count
                Header = True
            End If
        End If
    End With
Next elem
Excel.Application.Quit

End Sub

Will
2011-05-12 08:44:50

Thanks very much John - quite right!

Takashi
2011-12-22 07:32:29

Hi, Will: I tried both two VBA, the result are the report files csvFile.csv/Attribute.xls were created, but without any coordinate data or information inside the sheet 1. I just drawed only two cross with polyline onto layer 0. What is wrong? I tried to run it by both F5 and F8. But no error report showup during VBA is running.Could you please give me some idea? Thanks a lot. My sytem is; Windows 7 32bit Autocad 2012 Microsoft excel 2007

Will
2012-01-05 08:41:55

Hi Takashi,

There should not be an Attribute.xls file - are you sure that was created by the code? The subroutine is only ONE chunk of code - you'll need to use the whole subroutine. Also, make sure you're using VBA from WITHIN AutoCAD®, using VBAIDE, and not VBA in Excel.

Will

Takashi
2012-01-10 06:03:32

Hi Will: Thanks for your relpy. I am new with the VBA. What I did is; started the AutoCAD®, write some polyline onto the layer 0 and then go to the Manage ---> Applications of the AutoCAD® to open a visual basic editor, and copy your code " Sub ExportPoints.....End Sub" and paste it onto the editor. Go to Tools --->References, and select the Microsoft Scripting Runtime. finally run the VBA with push key F5 or F8. I test it again today. it does not shows any error message. And finally onto the command line of the AutoCAD® it report "Points have been exported to C:\csvFile.csv" and the csvFile.csv is created but without any information inside. You said I need to use the whole subroutine. But how can I get a complete subroutine? Regards.

Will
2012-01-16 08:12:29

You must have some points in your drawing to export, and you must have them selected before you run the routine. Does that help?

Will

Takashi
2012-01-19 07:30:14

I just drew some structures with polyline onto layer 0 and selected all off them then run the routine. But there is still no information onto the csvFile. I also confirmed the csvfile is created with the new time. May be there are some things wrong with my AutoCAD® setting!

Will
2012-01-30 10:04:50

The code as it stands works with points only. If you want to support polylines you'll need to add that functionality.

Giovanni
2012-09-19 21:23:39

I get a error of "User defined type not defined" (or something like this, I translated it from Italian) on the line: Dim FSO As FileSystemObject May I have to add a reference or something like this?

I have AutoCAD® 2011 in Italian, Windows 7 64bit. Many thanks, Giovanni

Will
2012-10-02 11:16:43

Yes, you need to create a reference to the Microsoft Scripting Runtimes. It says where to go in a comment above that line of code.

geoffrey
2012-10-25 14:17:55

But why should the end user struggle so much to export autocad to csv. It just means the designers of autocad must do something.

Will
2012-11-08 08:10:56

Indeed, but in the absense of such an ideal solution we have to find out own way!

CH Lee
2015-08-12 01:29:56

Hi Will

Thanks for sharing the code. This is very handy.

My questions:

1. How can we export only those points that have been selected? If i selected 4 of 10 points, I would or should get only 4 coordinates in the CSV, Right? But i got all 10 coordinates. I must have missed some steps here.
2. As this code will only work with point, how/what will we need to change to get it work with polylines and their attributes, such as Vertex X, Vertex Y, elevation, length, and area? Or can you please post us some reference about AutoCAD® variable type, entity or attribute names that work with VBA, such as "AcadPoint", ".Coordinates(0)", ".Coordinates(1)"? So we know where to start, as I find it difficult to look for some sources to learn them.

Again, thanks a lot.

PS: yeah i know it is 2015 now.