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

0 Members and 1 Guest are viewing this topic.

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Civil3D VBA - Excel Parcels
« 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!
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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!
Civil3D 2020

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« Reply #3 on: March 04, 2014, 09:11:22 AM »
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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?
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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?
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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]
  1. Sub PickLwPolyAndGetData()
  2.    
  3.     Dim MyCell As Range
  4.     Dim ACAD As AcadApplication
  5.     Dim LWPoly As AcadLWPolyline
  6.     Dim oParcel As AeccParcel
  7.     Dim ThisDrawing As AcadDocument
  8.     Dim Pt1 As Variant
  9.     Dim LWArea As Double, LWZ As Double
  10.  
  11.     ' Autocad Session handling
  12.     On Error Resume Next
  13.     Set ACAD = GetObject(, "AutoCAD.Application")
  14.     On Error GoTo 0
  15.     If ACAD Is Nothing Then
  16.         Set ACAD = New AcadApplication
  17.         ACAD.Visible = True
  18.     End If
  19.     Set ThisDrawing = ACAD.ActiveDocument
  20.          
  21.    
  22.     ' select LwPolyline
  23.     On Error Resume Next
  24.     Do
  25.         Err.Clear
  26.         ThisDrawing.Utility.GetEntity oParcel, Pt1, "Select a Parcel:"
  27.     Loop While Err
  28.     On Error GoTo 0
  29.    
  30.     'get LWPoly data
  31. '    With LWPoly
  32. '        LWArea = .Area
  33. '        LWZ = .Elevation
  34. '    End With
  35.    
  36.     'get oParcel data
  37.     With oParcel
  38.         LWArea = .Statistics.Area
  39.         LWZ = .Statistics.Perimeter
  40.     End With
  41.    
  42.     ' write LWPoly data on worksheet
  43.     Set MyCell = ActiveCell
  44.     With MyCell
  45.         .Offset(0, 0).Value = "Area:"
  46.         .Offset(0, 1).Value = LWArea
  47.         .Offset(1, 0) = "Perimeter:"
  48.         .Offset(1, 1) = LWZ
  49.     End With
  50.    
  51.     Set ThisDrawing = Nothing
  52.     Set ACAD = Nothing
  53.    
  54.  End Sub
  55.  
  56.  

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

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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.!
Civil3D 2020

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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.
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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.
Civil3D 2020

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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
);
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« Reply #14 on: March 07, 2014, 11:00:51 AM »
Code - vb.net: [Select]
  1. Dim User1 As Variant
  2.     With oParcel
  3.         LWArea = .Statistics.Area
  4.         LWZ = .Statistics.Perimeter
  5.         User1 = .GetUserDefinedPropertyValue("User1")
  6.     End With
  7.  

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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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




Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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.
Civil3D 2020

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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?
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« Reply #19 on: March 07, 2014, 12:09:21 PM »
Can you post your drawing? And which version of C3D are you using?

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« Reply #20 on: March 07, 2014, 12:16:06 PM »
Lets try this... its a 2013. That is what I am working with.
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« Reply #21 on: March 07, 2014, 01:07:14 PM »
OK, like I said before.... :-D Neither of these Site's Parcels properties have the UDP Classification set, so it will fail to get any UDP's.



MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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


Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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


Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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.
Civil3D 2020

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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.
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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.
Civil3D 2020

MSTG007

  • Gator
  • Posts: 2530
  • I can't remeber what I already asked! I need help!
Re: Civil3D VBA - Excel Parcels
« 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. :)
Civil3D 2020

Jeff_M

  • King Gator
  • Posts: 4016
  • C3D user & customizer
Re: Civil3D VBA - Excel Parcels
« 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.