Author Topic: Civil3D VBA - Excel Parcels (Read 14747 times)

MSTG007 · « **on:** March 04, 2014, 08:39:50 AM »

I had a thought... I did not know how hard it was to create a VBA in excel that I could select an object (Civil3d Parcel) and extract or transfer the selected parcels to excel in someway? With that; I wonder if you could extract areas, parcel names, user fields, etc.

Thanks!

Jeff_M · « **Reply #1 on:** March 04, 2014, 08:54:51 AM »

The existing Parcel Reports in C3D can output to csv files for use in Excel. I suspect you are looking for something else?

MSTG007 · « **Reply #2 on:** March 04, 2014, 08:59:42 AM »

Nice. Can I customized the report (CSV) to pull user fields as well?
And of course the classic question. How can I get a command prompt to activate this?

I have a spreadsheet with custom buttons that the users can press to go through steps like a workflow. So If I can VBA the command from excel to cad. boom!

MSTG007 · « **Reply #3 on:** March 04, 2014, 09:11:22 AM »

I did find this... Not sure if this is correct though.

http://adndevblog.typepad.com/infrastructure/2012/08/set-parcel-userdefined-property-from-api.html

Jeff_M · « **Reply #4 on:** March 04, 2014, 12:40:13 PM »

OK, if you are wanting to automate this from Excel, you'd probably have more luck coding it yourself using the API rather than sending commands for the reports. I'd try to help, but I haven't done any VBA coding in 6 years or more. And I'm not entirely sure how the C3D Interops might work outside of the Autocad environment. You might look at the sample files C3D ships with, there are still some for VBA.

MSTG007 · « **Reply #5 on:** March 06, 2014, 02:56:04 PM »

Jeff, RICVBA just left this post for excel VBA.

http://www.theswamp.org/index.php?topic=46487.new;topicseen#new

Is there away to get it to grab civil3d parcels?

Jeff_M · « **Reply #6 on:** March 06, 2014, 03:54:40 PM »

If you add a reference to the Aecc.Interop.Land & Aecc.Interop.UiLand you may be able to work with Parcels.

MSTG007 · « **Reply #7 on:** March 06, 2014, 04:02:03 PM »

Could I ask for an example? To me, the vba to excel makes sense. But how is that different from the VBA of civil3d?

Jeff_M · « **Reply #8 on:** March 06, 2014, 06:13:48 PM »

I can't believe I did this. Haven't worked in VBA in forever, only once before in Excel VBA, and yet this worked....

Code - vb.net: [Select]

Sub PickLwPolyAndGetData()
    
    Dim MyCell As Range
    Dim ACAD As AcadApplication
    Dim LWPoly As AcadLWPolyline
    Dim oParcel As AeccParcel
    Dim ThisDrawing As AcadDocument
    Dim Pt1 As Variant
    Dim LWArea As Double, LWZ As Double
 
    ' Autocad Session handling
    On Error Resume Next
    Set ACAD = GetObject(, "AutoCAD.Application")
    On Error GoTo 0
    If ACAD Is Nothing Then
        Set ACAD = New AcadApplication
        ACAD.Visible = True
    End If
    Set ThisDrawing = ACAD.ActiveDocument
          
    
    ' select LwPolyline
    On Error Resume Next
    Do
        Err.Clear
        ThisDrawing.Utility.GetEntity oParcel, Pt1, "Select a Parcel:"
    Loop While Err
    On Error GoTo 0
    
    'get LWPoly data
'    With LWPoly
'        LWArea = .Area
'        LWZ = .Elevation
'    End With
    
    'get oParcel data
    With oParcel
        LWArea = .Statistics.Area
        LWZ = .Statistics.Perimeter
    End With
    
    ' write LWPoly data on worksheet
    Set MyCell = ActiveCell
    With MyCell
        .Offset(0, 0).Value = "Area:"
        .Offset(0, 1).Value = LWArea
        .Offset(1, 0) = "Perimeter:"
        .Offset(1, 1) = LWZ
    End With
    
    Set ThisDrawing = Nothing
    Set ACAD = Nothing
   
 End Sub
 
 

As noted, need to add the correct references for your version, this shows them for 2013:

MSTG007 · « **Reply #9 on:** March 07, 2014, 07:13:00 AM »

You my man.... are awesome and so is Ricvba too for the idea... Way cool. Way cool! Cant wait to test this out.!

MSTG007 · « **Reply #10 on:** March 07, 2014, 08:12:13 AM »

Could you show me where all the i.e. (.Statistics.Area / .Statisitics.Perimter) are located or what they are called if I am looking for (Description / User Defined Properties [Unclassified] {User1} {User2} {User3})

Those User1, 2, 3 are custom fields that we manually place in the parcel.

Jeff_M · « **Reply #11 on:** March 07, 2014, 09:18:32 AM »

http://docs.autodesk.com/CIV3D/2012/ENU/API_Reference_Guide/com/AeccXLandLib__IAeccParcel.htm

MSTG007 · « **Reply #12 on:** March 07, 2014, 10:22:53 AM »

I have found the link for the User Data;

http://docs.autodesk.com/CIV3D/2012/ENU/API_Reference_Guide/com/AeccXLandLib__IAeccParcel__SetUserDefinedPropertyValue@[in]_VARIANT@[in]_VARIANT.htm

Code: [Select]

I would like to replace the [code]LWArea = .Statistics.Perimeter
to

Code: [Select]

[ id(AECCXLAND_DISPID_PARCEL_SETUSERDEFINEDPROPERTYVALUE), helpcontext(IDH_AECCXLAND_PARCEL_SETUSERDEFINEDPROPERTYVALUE), helpstringcontext(AECCXLAND_HELPSTR_PARCEL_SETUSERDEFINEDPROPERTYVALUE) ]
HRESULT SetUserDefinedPropertyValue(
    [in] VARIANT userDefinedProperty, 
    [in] VARIANT newValue
);

I am not sure how to place this in the above code.

MSTG007 · « **Reply #13 on:** March 07, 2014, 10:37:54 AM »

I think I gave the wrong code above.

docs.autodesk.com/CIV3D/2012/ENU/API_Reference_Guide/com/AeccXLandLib__IAeccParcel__GetUserDefinedPropertyValue@[in]_VARIANT@[out,_retval]_VARIANT_.htm

Code: [Select]

[ id(AECCXLAND_DISPID_PARCEL_GETUSERDEFINEDPROPERTYVALUE), helpcontext(IDH_AECCXLAND_PARCEL_GETUSERDEFINEDPROPERTYVALUE), helpstringcontext(AECCXLAND_HELPSTR_PARCEL_GETUSERDEFINEDPROPERTYVALUE) ]
HRESULT GetUserDefinedPropertyValue(
    [in] VARIANT userDefinedProperty, 
    [out, retval] VARIANT* pVal
);

Jeff_M · « **Reply #14 on:** March 07, 2014, 11:00:51 AM »

Code - vb.net: [Select]

Dim User1 As Variant
    With oParcel
        LWArea = .Statistics.Area
        LWZ = .Statistics.Perimeter
        User1 = .GetUserDefinedPropertyValue("User1")
    End With
 

Note that if the Parcel doesn't have this UDP assigned to it this will throw an error, so be sure to trap for it.

MSTG007 · « **Reply #15 on:** March 07, 2014, 11:13:56 AM »

Ok I have done my best to intergrate this together

The parcel I have has that UDF (CN) and we manually input the value.

I am erroring out here:

Code: [Select]

CN = .GetUserDefinedPropertyValue("CN")

Code: [Select]

Sub PickLwPolyAndGetData()

Dim MyCell As Range
Dim ACAD As AcadApplication
Dim LWPoly As AcadLWPolyline
Dim oParcel As AeccParcel
Dim ThisDrawing As AcadDocument
Dim Pt1 As Variant
Dim LWArea As Double, LWZ As Double
Dim CN As Variant


' Autocad Session handling
On Error Resume Next
Set ACAD = GetObject(, "AutoCAD.Application")
On Error GoTo 0
If ACAD Is Nothing Then
Set ACAD = New AcadApplication
ACAD.Visible = True
End If

Set ThisDrawing = ACAD.ActiveDocument


' select LwPolyline
On Error Resume Next
Do
Err.Clear
ThisDrawing.Utility.GetEntity oParcel, Pt1, "Select a Parcel:"
Loop While Err
On Error GoTo 0

    
    'get LWPoly data
'    With LWPoly
'        LWArea = .Area
'        LWZ = .Elevation
'    End With

'get oParcel data
With oParcel
LWArea = .Statistics.Area
LWZ = .Statistics.Perimeter
CN = .GetUserDefinedPropertyValue("CN")
End With


' write LWPoly data on worksheet
Set MyCell = ActiveCell
With MyCell
.Offset(0, 0).Value = "Area:"
.Offset(0, 1).Value = LWArea
.Offset(0, 2).Value = User1
.Offset(1, 0) = "Perimeter:"
.Offset(1, 1) = LWZ
.Offset(1, 2) = CN
End With

Set ThisDrawing = Nothing
Set ACAD = Nothing


End Sub

Jeff_M · « **Reply #16 on:** March 07, 2014, 11:33:05 AM »

I have found that while you can change the value of UDP's in a parcel's properties, if the Site's Parcels UDP Classification Property is not set then it can't be retrieved.

MSTG007 · « **Reply #17 on:** March 07, 2014, 11:37:59 AM »

Ok. The (CN) is [Unclassified].

As for the Sites. I have several Sites. (1,2,3) with parcels under each.

MSTG007 · « **Reply #18 on:** March 07, 2014, 12:08:15 PM »

Just thinking about this alittle more. Is there a way we can hard code the UDP to be (Unclassified) within the script?
That way it show not error out on the objects that it will select. Or am I not quit following?

Jeff_M · « **Reply #19 on:** March 07, 2014, 12:09:21 PM »

Can you post your drawing? And which version of C3D are you using?

MSTG007 · « **Reply #20 on:** March 07, 2014, 12:16:06 PM »

Lets try this... its a 2013. That is what I am working with.

Jeff_M · « **Reply #21 on:** March 07, 2014, 01:07:14 PM »

OK, like I said before....

Neither of these Site's Parcels properties have the UDP Classification set, so it will fail to get any UDP's.

MSTG007 · « **Reply #22 on:** March 07, 2014, 01:33:11 PM »

Working! I just saw RICVBA revise his script to do multiple parcels at one time. I have tried to intergrate your code into his; but I can not select anything. Everything else seemed to match up.

Code: [Select]

Sub PickLwPolysAndGetData()
    
'for Excel sheet managing purposes
Dim MySht As Worksheet
Dim MyCell As Range

'for Autocad application managing purposes
Dim ACAD As AcadApplication
Dim ThisDrawing As AcadDocument
Dim LWPoly As AcadLWPolyline
Dim oParcel As AeccParcel
'Dim Pt1 As Variant
Dim CN As Variant

' for selection set purposes
Dim ssetObj As AcadSelectionSet
Dim gpCode(0) As Integer
Dim dataValue(0) As Variant

'for general variables managing purposes
Dim iRow As Long
Dim LWArea As Double, LWZ As Double


' Autocad Session handling
    On Error Resume Next
    Set ACAD = GetObject(, "AutoCAD.Application")
    On Error GoTo 0
    If ACAD Is Nothing Then
        Set ACAD = New AcadApplication
        ACAD.Visible = True
    End If
    Set ThisDrawing = ACAD.ActiveDocument
      

' selecting LwPolylines on screen by selelection set filtering method
    ' managing potential selection set exsistence
    On Error Resume Next
    'Set ssetObj = ThisDrawing.SelectionSets.Item("LWPolySSET")
    Set ssetObj = ThisDrawing.SelectionSets.Item("oParcelSSET")
    
    'ThisDrawing.Utility.GetEntity oParcel, Pt1, "Select a Parcel:" <------- This is from the Code you did
    
    'If Err Then Set ssetObj = ThisDrawing.SelectionSets.Add("LWPolySSET")
    If Err Then Set ssetObj = ThisDrawing.SelectionSets.Add("oParcelSSET")
    On Error GoTo 0
    ssetObj.Clear
    
    'setting filtering critera
    gpCode(0) = 0
    dataValue(0) = "Parcel"
    
    'selecting LWPolylines
    ssetObj.SelectOnScreen gpCode, dataValue

' processing LWPolylines

    If ssetObj.Count > 0 Then
    
        ' writing sheet headings
        Set MySht = ActiveSheet
        Set MyCell = MySht.Cells(3, 2) 'Where to Start the Excel Cell Input X, Y
        With MyCell
            '.Offset(0, 0).Value = "LWPoly nr"
            .Offset(0, 1).Value = "Area"
            .Offset(0, 0) = "Z"
        End With
        
        'clearing previous written data
        iRow = MySht.Cells(MySht.Rows.Count, 1).End(xlUp).Row
        If iRow > 1 Then MyCell.Offset(1, 0).Resize(iRow - 1, 3).Clear
        
        'retrieving LWPolys data and writing them on worksheet
        iRow = 1
        For Each oParcel In ssetObj
            'retrieving LWPoly data
            With oParcel
                LWArea = .Statistics.Area
                'LWZ = .Statistics.Perimeter
                CN = .GetUserDefinedPropertyValue("CN")
            End With
            
            ' writing LWPoly data
            With MyCell
                '.Offset(iRow, 0).Value = "LWPoly nr." & iRow
                .Offset(iRow, 1).Value = LWArea
                .Offset(iRow, 0) = LWZ
            End With
            iRow = iRow + 1
        Next oParcel
        
    End If

' cleaning up before ending
    ssetObj.Delete
    Set ssetObj = Nothing
    Set ThisDrawing = Nothing
    Set ACAD = Nothing

End Sub

Jeff_M · « **Reply #23 on:** March 07, 2014, 02:05:27 PM »

In C3D select a Parcel (one with just a few sides, as it lists all the segments) and LIST it. The name to be used for dataValue will be shown.

Hint: AECC_PARCEL

MSTG007 · « **Reply #24 on:** March 07, 2014, 02:35:09 PM »

Got it. Got to love all this stuff. So with the revised code, I can select things but it seems the T.C. value will not filter thru on the drawing you have. I can populate the name but not the tc for some reason.

Code: [Select]

Sub PickLwPolysAndGetData()
    
'for Excel sheet managing purposes
Dim MySht As Worksheet
Dim MyCell As Range

'for Autocad application managing purposes
Dim ACAD As AcadApplication
Dim ThisDrawing As AcadDocument
Dim LWPoly As AcadLWPolyline
Dim oParcel As AeccParcel
'Dim Pt1 As Variant
Dim CN As Variant
Dim TC As Variant
Dim Name As Variant

' for selection set purposes
Dim ssetObj As AcadSelectionSet
Dim gpCode(0) As Integer
Dim dataValue(0) As Variant

'for general variables managing purposes
Dim iRow As Long
Dim LWArea As Double, LWZ As Double


' Autocad Session handling
    On Error Resume Next
    Set ACAD = GetObject(, "AutoCAD.Application")
    On Error GoTo 0
    If ACAD Is Nothing Then
        Set ACAD = New AcadApplication
        ACAD.Visible = True
    End If
    Set ThisDrawing = ACAD.ActiveDocument
      

' selecting LwPolylines on screen by selelection set filtering method
    ' managing potential selection set exsistence
    On Error Resume Next
    'Set ssetObj = ThisDrawing.SelectionSets.Item("LWPolySSET")
    Set ssetObj = ThisDrawing.SelectionSets.Item("oParcelSSET")
    'Set ssetObj = ThisDrawing.SelectionSets.Item("AECC_PARCEL")
    
    'ThisDrawing.Utility.GetEntity oParcel, Pt1, "Select a Parcel:"
    
    'If Err Then Set ssetObj = ThisDrawing.SelectionSets.Add("LWPolySSET")
    If Err Then Set ssetObj = ThisDrawing.SelectionSets.Add("oParcelSSET")
    'If Err Then Set ssetObj = ThisDrawing.SelectionSets.Add("AECC_PARCEL")
    On Error GoTo 0
    ssetObj.Clear
    
    'setting filtering critera
    gpCode(0) = 0
    dataValue(0) = "AECC_PARCEL"
    
    'selecting LWPolylines
    ssetObj.SelectOnScreen gpCode, dataValue

' processing LWPolylines

    If ssetObj.Count > 0 Then
    
        ' writing sheet headings
        Set MySht = ActiveSheet
        Set MyCell = MySht.Cells(3, 2) 'Where to Start the Excel Cell Input X, Y
        With MyCell
            '.Offset(0, 0).Value = "LWPoly nr"
            .Offset(0, 1).Value = "Area"
            .Offset(0, 0) = "Perimeter"
            .Offset(0, 2) = "CN"
            .Offset(0, 3) = "TC"
            .Offset(0, 4) = "Name"
        End With
        
        'clearing previous written data
        iRow = MySht.Cells(MySht.Rows.Count, 1).End(xlUp).Row
        If iRow > 1 Then MyCell.Offset(1, 0).Resize(iRow - 1, 3).Clear
        
        'retrieving LWPolys data and writing them on worksheet
        iRow = 1
        For Each oParcel In ssetObj
            'retrieving LWPoly data
            With oParcel
                LWArea = .Statistics.Area
                Perimeter = .Statistics.Perimeter
                CN = .GetUserDefinedPropertyValue("CN")
                TC = .GetUserDefinedPropertyValue("T.C.")
                Name = .DisplayName
            End With
            
            ' writing LWPoly data
            With MyCell
                '.Offset(iRow, 0).Value = "LWPoly nr." & iRow
                .Offset(iRow, 1).Value = LWArea
                .Offset(iRow, 0) = Perimeter
                .Offset(iRow, 2) = CN
                .Offset(iRow, 3) = TC
                .Offset(iRow, 3) = Name
            End With
            iRow = iRow + 1
        Next oParcel
        
    End If

' cleaning up before ending
    ssetObj.Delete
    Set ssetObj = Nothing
    Set ThisDrawing = Nothing
    Set ACAD = Nothing

End Sub

Jeff_M · « **Reply #25 on:** March 07, 2014, 03:20:48 PM »

You are overwriting the TC in the cell with the name:

.Offset(iRow, 3) = TC
.Offset(iRow, 3) = Name

MSTG007 · « **Reply #26 on:** March 07, 2014, 03:24:49 PM »

ok. Guess what. I am done! Thank you so so much! I learned a lot today from you. Thank you for taking the time to go thru this with me as well.

MSTG007 · « **Reply #27 on:** June 11, 2014, 12:31:52 PM »

Sorry to Bother this post again. I gotta ask; if I can set the VBA to send information to the parcels in Civil3d from the Excel Spreadsheet. So, basically reverse what we wrote. Is that hard to do?
thx

Jeff_M · « **Reply #28 on:** June 11, 2014, 07:33:16 PM »

Yes, you can write back to the UDP's with oParcel.SetUserDefinedPropertyValue(propname, value). But getting the Parcel to write to will be difficult unless you include the Parcel's Handle when you write the data to Excel.

MSTG007 · « **Reply #29 on:** June 12, 2014, 07:38:09 AM »

So I would guess you would have to originally import the parcels (UDP) into excel w/ it pulling the handle? Then to export from excel to civil I would need it to match the handle and transfer the requested fields over. Geesh.

Jeff_M · « **Reply #30 on:** June 12, 2014, 08:43:33 AM »

If not the Handle, then the Site name would need to be provided since Parcels live within Sites. You could hard code that, providing you always use teh same Site name for the hydrology parcels.

MSTG007 · « **Reply #31 on:** June 12, 2014, 08:48:15 AM »

I was talking with an engineer. They said it would be nice to interchange exchange the information. Either the way we designed was to keep all living current data in the dwg and extract it, but they would like the flexibility to do calcs then transfer some of the data over to dwg. It seems like it might be hard to do that. But as for the Sites would be hard coded the parcel names would not.

MSTG007 · « **Reply #32 on:** September 02, 2015, 01:05:27 PM »

I hate to bring up this old old topic. But I am now toying around with getting general parcel number, address, tax id.

How would this VBA line be switched?

Code: [Select]

.GetUserDefinedPropertyValue
Thanks again for all your brilliance.

Jeff_M · « **Reply #33 on:** September 02, 2015, 01:35:07 PM »

The Parcel General UDP's are not exposed in any API. You need to create & use your own.

News:

Author Topic: Civil3D VBA - Excel Parcels (Read 14747 times)