Export Points from AutoCAD® to CSV file using VBA

by Will on May 11, 2011

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:

Screenshot Of Points

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

Enter Your Mail Address

Be Sociable, Share!

{ 12 comments }

john coon May 11, 2011 at 2:08 pm

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 May 12, 2011 at 8:44 am

Thanks very much John – quite right!

Takashi December 22, 2011 at 7:32 am

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 January 5, 2012 at 8:41 am

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 January 10, 2012 at 6:03 am

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 January 16, 2012 at 8:12 am

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 January 19, 2012 at 7:30 am

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 January 30, 2012 at 10:04 am

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

Giovanni September 19, 2012 at 9:23 pm

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 October 2, 2012 at 11:16 am

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 October 25, 2012 at 2:17 pm

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 November 8, 2012 at 8:10 am

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

Comments on this entry are closed.

Previous post:

Next post: