Data From Excel to Block Attributes

[Jeff Bowlin]

I have some soil boring data captured, in the field, to an Excel spreadsheet; basically, a bore site ID and coordinates.

 

I'd like to be able to have the coordinate attributes of blocks inserted into my bore site plans populate automatically, perhaps based off of the value of a bore site ID attribute which is manually entered for each bore site.

 

Alternatively, if there were a way to insert the bore site blocks based off the actual Excel records, populating all attribues and locating the block by the actual X,Y,Z coordinate, that might be even better.

 

Is there an easy way? Is there any lisp to be shared?

 

Newbie

[spiff88]

This is some VBA code you can use from Excel to insert blocks into AutoCAD. You should have a block already created into AutoCAD. If the block has attributes, you can use the data from Excel to populate the attributes.

 

If you know how VBA and the VBE works, you should have no problem with this.

 

Just make sure to set the 'reference' to AutoCAD. In the VBE - Tools / References - with AutoCAD 2010 it is called AutoCAD 2012 Type Library.

 

 

Sub InsertBlockWithData()

Dim objApp As AcadApplication: Dim objDoc As AcadDocument
Dim blockRefObj As AcadBlockReference: Dim varAttributes As Variant
Dim InPt(2) As Double
   
' Excel worksheet name with data to insert into AutoCAD
' X / Y / Z / Layer Name / Block Name / Boring ID / Block Size / Block Rotation
   DataSheet = "WorksheetWithData"
   
' Create the link to AutoCAD - the currently open drawing
   Set objApp = GetObject(, "AutoCAD.Application"): Set objDoc = objApp.ActiveDocument

' Loop through the data - set StartRow (usually 2) and EndRow (last row of data)
   For Row = StartRow To EndRow

' get coordinates and layer name for text insertion
       InPt(0) = Worksheets(DataSheet).Cells(Row, 1)
       InPt(1) = Worksheets(DataSheet).Cells(Row, 2)
       InPt(2) = Worksheets(DataSheet).Cells(Row, 3)
       Layer = Worksheets(DataSheet).Cells(Row, 4)
       BlockName = Worksheets(DataSheet).Cells(Row, 5)
       BoringName = Worksheets(DataSheet).Cells(Row, 6)
       Size = Worksheets(DataSheet).Cells(Row, 7)
       Rotation = Worksheets(DataSheet).Cells(Row,  ' in radians
       
' Insert Borings Block object and change attributes
       Set blockRefObj = objDoc.ModelSpace.InsertBlock(InPt, BlockName, Size, Size, Size, 0)
       blockRefObj.Layer = Layer
       blockRefObj.Color = acByLayer
' set attributes - you can have more than one...
       varAttributes = blockRefObj.GetAttributes
           varAttributes(0).TextString = BoringName

   Next Row

End Sub

[fiddleback143]

I think I'm doing something wrong I get a run error when I try this on Inpt(0).

I changed out "w2orksheetwithdata" to "Creek" which is the tab name.

What alse do I need to do?

I can send the file or post it somewhere if I need to.

[satishrajdev]

I have some soil boring data captured, in the field, to an Excel spreadsheet; basically, a bore site ID and coordinates.

 

I'd like to be able to have the coordinate attributes of blocks inserted into my bore site plans populate automatically, perhaps based off of the value of a bore site ID attribute which is manually entered for each bore site.

 

Alternatively, if there were a way to insert the bore site blocks based off the actual Excel records, populating all attribues and locating the block by the actual X,Y,Z coordinate, that might be even better.

 

Is there an easy way? Is there any lisp to be shared?

 

Newbie

 

I also used to insert blocks from X Y Z coords, but just want to have a look how you want to insert blocks, can you attached example drawing and csv for that, So i can modify my lisp as per your requirement....