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!
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.
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
Thanks very much John - quite right!
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
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.
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.
You must have some points in your drawing to export, and you must have them selected before you run the routine. Does that help?
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!
The code as it stands works with points only. If you want to support polylines you'll need to add that functionality.
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
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.
But why should the end user struggle so much to export autocad to csv. It just means the designers of autocad must do something.
Indeed, but in the absense of such an ideal solution we have to find out own way!
Thanks for sharing the code. This is very handy.
- 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.
- 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.