Create alignment with VBA from Excel

[mgof]

I know VBA might not be the best option with C3D, but... I have data managed in an Excel file and would like to have all the commands/scripts in that file.

I need to create alignments, surfaces, profiles and profile views from these data. I assume it can still be done using VBA (hope I am not wrong), but I do not find the syntax for the functions listed in the VBA browser: AeccAlignments AddFromPolyline, AeccSurfaces AddGridSurface, AeccProfiles AddFromSurface, AeccProfileViews Add, etc.

Could anybody advice me on this?

Thanks

[lu_an_jie]

It's possible with VBA, but ... 

1. You are better of with using .NET, either VB.NET or C#

2. It's a big task and their maybe more efficient way to archive your goals

I would recommend to start with the surface. How are your surface data handled in Excel? Points with X,Y and elevation?

The easiest way would be to export from excel to cvs and use Point File for your Surface definition.

Next step would be alignments. Do you have a small sample of your alignments in Excel?

[mgof]

Thank you for your reply.

1. Could I launch a .NET routine from Excel?... If not, I stick to VBA... If yes, I should finally get started with .NET

2. Indeed, the idea is to start with Surface, then Alignment, then Profile (from surface) then Profile View. Yes I have Surface (XY and elevations + XY for the boundary polyline) and alignment (XY) data, produced by the VBA/Excel routines.

Honnestly, the workflow seems pretty straightforward (doing it "manually" under C3D is really easy, the way you describe it for instance, exporting a point file and then importing it, adding the boundary, creating the polyline and then the alignment from the polyline, etc.). My goal posting this here (customization, not C3D) is to try to get this workflow automated (I potentially have to do this operation about 20 times a day).

You mention it is possible with VBA (which I hope so...). Good news! So the question remains: what would be the right syntax to call functions for Surface, Alignment, Profile and ProfileView creation.

[lu_an_jie]

If you want to control the process completely  from  Excel (out-of-process) than your first choice is VBA via COM, but passing large amount of data via COM maybe slow.

The steps are:

1. Creating a point group 

2. Creating a surface

3. Adding the points to the surface

The documentation for the COM API you find here

http://help.autodesk.com/view/CIV3D/2018/ENU/?guid=GUID-76E075C0-D3CD-4E6F-8F01-F147815EAAF7

There is also a sample program  in the help files that covers big part of your topic:

\Sample\Civil 3D API\Vba\SurfacePoints\SurfacePointsSample.dvb

Try this solution first and have a look at the performance. f it getting really slow than split it in two parts:

On Excel side (VBA): Export to Comma separated text file and a sendCommand to Civil 3d

On AutoCAD (.NET) Create Surface and add the pointfile directly or create a point group and add the pointgroup to the surface data.

Let's do the alignment after you got your surface's and let me know if you have problem with the code

[mgof]

Thank you for your reply. The samples most probably might cover most of what I need.

I want to give it a try from Excel. The sample codes seem to have pretty much everything I need to create the surface.

From previous steps, I already have a txt file with the points and the boundary polyline.

Before actually getting started, I need to get the AeccApplication (which I make sure is already open before running the script to avoid having to open it everytime). I get trouble acecssing it from Excel as I assume the code should be something like:

Dim oAcadApp as AeccApplication

Set oAcadApp = GetObject (, "AeccXUiLand.AeccApplication.11.0")

which returns runtime error '429': ActiveX component can't create object

Would this be a problem between Microsoft Excel (using 2010) and AutoDesk (using C3D 2018)? Any idea on this?

[norman.yuan]

Disclaimer: I have never done Civil3D COM automation from external app (Excel). To me it seems that you cannot use VB's CreateObject()/GetObject() to get hold of an instance of AeccApplication, whish lives inside AutoCAD process. Since you you should have already had an AutoCAD instance AcadApplication) available in your Excel VBA code, you should use

AcadApplication.GetInterfaceObject("Aeccxxxxx")

to grab the existing Civil3D app inside AutoCAD process. Actually you do the same thing even the VBA code is in AutoCAD VBA.

Extra comment, even you seems already made up your mind of doing it from Excel end: it is not only significantly slower because of "out-process" communication between 2 apps, your user also has to face 2 apps (Excel and AutoCAD) running at the same time and yet he/she may not be able to interact to each of them smoothly, or worse, accidentally interact with the one (AutoCAD) being automated (by Excel). IMO, unless the data stored in Excel must be there for the presentation purpose and user can only understand the workflow because of the sheet presentation, that is, if the Excel sheet mainly acts as data storage) the optimized process would be better done on AutoCAD VBA side (or better yet, with AutoCAD/Civil3D ,NET API). This would not only work faster, but also make the code development easier (easier to debug), and provide extra flexibility to separate AutoCAD/Civil work from data storage, so that in future when the development grows, the data can be from any other source than Excel sheet without having to rewrite the application on AutoCAD/Civil side of work.

[mgof]

@norman.yuan thanks,

just tried it (AcadApplication.GetInterfaceObject("Aeccxxxxx")), but i get the message "-2147221005 (800401f3) problem in loading application". Any idea why?

As for why I try to launch it from Excel: most of the existing scripts for this job are handled from Excel (calculations, importation of data from other sources), and the C3D part (profiles and profile views) are only the end part. The idea is that the user has only one interface. Maybe I am wrong and should try other options. I have not made up my mind yet...

[norman.yuan]

OK, I did a quick try with my Civil3D 2019 and Excel 2013. Following code runs from Excel VBA side, and  successfully:grabs the AeccXUiLandLib.AeccApplication object from running AutoCAD/Civil session:

Option Explicit

Public Sub CivilTest()

    On Error Resume Next
     
    Dim cadApp As AcadApplication
    Set cadApp = GetObject(, "AutoCAD.Application")
    If cadApp Is Nothing Then
        MsgBox "Cannot connect to existing AutoCAD session!", vbCritical
        Exit Sub
    Else
        MsgBox "Connected to running AutoCAD session.", vbInformation
    End If
    
    Dim civil As AeccApplication
    Set civil = cadApp.GetInterfaceObject("AeccXUiLand.AeccApplication.13.0")
    If cadApp Is Nothing Then
        MsgBox "Cannot connect to Civil application inside existing AutoCAD session!", vbCritical
        Exit Sub
    Else
        MsgBox "Connected to Civil application inside running AutoCAD session.", vbInformation
    End If
    
End Sub

Note the ProgID is "AeccXXXXX.13.0" for Civil2019, For Civil2018, it is "xxxx.12.0". I do not have other older versions installed, thus not sure what exactly the trailing number is. Of course in Excel VBA project, I added reference to AutoCAD type library and reference to AeccXUiLandLib type library.

So, the error you got would be because of wrong version number in the "ProgID" and/or wrong AeccXUiLandLib version (if you have more then one version Civil 3Ds installed).

[lu_an_jie]

I think Norman already pointed out your problem, but I also would follow up on his other point. 

Keep the Civil 3D stuff in Civil 3D, write a VBA code or better  a dll with (.NET) and use SendCommand to execute inside Civil 3D.

You need two SendCommand, first Command ensure that your Civil 3D is running and your additional code is loaded

and a second SendCommand to call your Routine inside Civil 3D.

use _vbaload and _vbarun

https://www.myengineeringworld.net/2014/10/send-autocad-commands-from-excel-vba.html

[lu_an_jie]

Do yo still working on this? I may write a tutorial on this issue as I have a similar problem.

[mgof]

I put it on hold for a few weeks, but should resume by end of January. I will anyway post the updates (if I stick to VBA or not and how far I can go).

[mgof]

So now I can grab C3D within AutoCAD.

Trying to create the surface, I use the sample code provided upon installation. The code starts as follows

Dim oSurfaces As AeccSurfaces
Dim MyLayer As AcadLayer
Dim MSpace As AcadModelSpace
Dim oAcadApp As AcadApplication
Set oAcadApp = GetObject(, "AutoCAD.Application")
Dim g_oCivilApp As AeccApplication
Set g_oCivilApp = oAcadApp.GetInterfaceObject("AeccXUiLand.AeccApplication.12.0")
Set g_oDocument = g_oCivilApp.ActiveDocument
Set g_oDatabase = g_oDocument.Database
Set MSpace = g_oDocument.ModelSpace
Set MyLayer = g_oDocument.Layers.Item("0")
g_oDocument.ActiveLayer = MyLayer

Set oSurfaces = g_oDocument.Surfaces

' Get a reference to the existing surface ("EG"). If
' it does not exist, make a new surface.
Dim oSurface As AeccSurface
Dim oTinSurface As AeccTinSurface
'On Error Resume Next
Set oSurface = oSurfaces.Item("EG")
On Error GoTo 0
If (oSurface Is Nothing) Then
Dim oTinCreationData As New AeccTinCreationData
oTinCreationData.Name = "EG"
oTinCreationData.Description = "TIN Surface from TXT file"
oTinCreationData.Layer = g_oDocument.Layers.Item(0).Name
oTinCreationData.BaseLayer = g_oDocument.Layers.Item(0).Name
If (CreateSurfaceStyle(SURFACE_STYLE_NAME) = False) Then
CreateSurfaceByImportTXT = False
Exit Function
End If
oTinCreationData.Style = SURFACE_STYLE_NAME
Set oTinSurface = oSurfaces.AddTinSurface(oTinCreationData)
Else
Set oTinSurface = oSurface
End If

It returns a runtime error 5 (Invalid procedure call or argument) on the line Set oSurface = oSurfaces.Item("EG")

Any idea why?

[norman.yuan]

I see you comment out "On Error Resume Next", which is supposed to handle the case where Surfaces collection does not contain a surface keyed as "EG".

So, the question is, does the surface "EG" exit? If not, the error you got is expected. Have you tried with "On Error Resume Next" NOT BEING COMMENTED OUT?

[mgof]

Thanks. Indeed I commented out and should not.

The script works (creates a surface) if the "EG" surface is already created (but empty).

If not, I get the following message: runtime error 429 (ActiveX cannot create object) on the following line (same code): 

oTinCreationData.Name = "EG"

Any idea why?

Also trying to add the boundary line with the following code,

Set acObj = MSpace.AddLightWeightPolyline(Points)
acObj.Closed = True
' The name of the boundary object.
Dim sName As String
sName = "Sample Boundary"
' The third parameter describes what the boundary does
' to triangles inside it. The fourth parameter is True
' if you want non-destructive boundary or false otherwise.
' The final parameter is the mid-ordinate distance.
Dim oNewBoundary As AeccSurfaceBoundary
Set oNewBoundary = oTinSurface.Boundaries.Add(acObj, sName, aeccBoundaryOuter, True, 10.5)

but returns a failure message ('-2147220978 (8004020e)')

Any idea why?

[mgof]

Apparently, the problem to add the boundary comes from the polyline. If I use that polyline manually on the surface, it cannot be added as a boundary neither (error message in C3D). If I offset it (5 cm), it works. Why? Points from that boundary are also assigned an elevation and are actually part of the input file (txt) used as input for the surface. Is that the reason?

As a workaround, I can do the offset in the code (acceptable for me). Here is the part of the code:

Dim pLine As AcadLWPolyline
Set pLine = MSpace.AddLightWeightPolyline(Points)
pLine.Closed = True

Dim pLineOffset As Variant
pLineOffset = pLine.Offset(0.05)

' The name of the boundary object.
Dim sName As String
sName = "Outer Boundary"
' The third parameter describes what the boundary does
' to triangles inside it. The fourth parameter is True
' if you want non-destructive boundary or false otherwise.
' The final parameter is the mid-ordinate distance.
Dim oNewBoundary As AeccSurfaceBoundary
Set oNewBoundary = oTinSurface.Boundaries.Add(pLineOffset, sName, aeccBoundaryOuter, True, 10.5)

Both the polyline and the offset are drawn correctly, but then I get an error message (runtime error 424 object required) on the last line.

Attached are the input txt file (for the surface), the boundary (points in Excel) and the surface+polylines (both initial and offset)

Any idea?

[lu_an_jie]

Dim pLine As AcadLWPolyline

you may change this to 

Dim pLine As AcadPolyline or

Dim pLine As  Acad3DPolyline

AcadLWPolylines are 2D only, AcadPolyline are 2D or 3D. This may also resolve your first problem and yo do not need to offset.

[mgof]

I tried all options of 3D or 2D polylines, but the result remains the same there is an error message generated, no matter if the process is done from Excel/VBA or manually. The polyline cannot be added as a boundary. It works with the offset poyline (either offset from a AddPolyline or from AddLWPolyline).

The last line (adding the boundary)

Dim oNewBoundary As AeccSurfaceBoundary

Set oNewBoundary = oTinSurface.Boundaries.Add(pLineOffset, sName, aeccBoundaryOuter, True, 10.5)

returns the "runtime error '424' Object required" - and I don't get why.

[lu_an_jie]

Do you give pLineOffset have a value when you call the function without offsetting the line?

Set oNewBoundary = oTinSurface.Boundaries.Add(pLineOffset, sName, aeccBoundaryOuter, True, 10.5)

[lu_an_jie]

The problem is not in your code, it's in your data.

I just dropped same .NET code together (so I can trap more error messages) that reads your boundary file and creates a closed polyline. If I use this polyline, Civil 3D tells me that it fails to add a boundary. That leads to your error as

oTinSurface.Boundaries.Add(pLineOffset, sName, aeccBoundaryOuter, True, 10.5) 

don't return a valid  object.

When I offset this polyline inside or outside I can use it as boundary. So I looked a little bit deeper in to your data.

There a a couple of points where you have minimal 0.001 offset between your boundary points and your surface points (I tried with the  rounded columns too), falling sometime inside and sometime outside and Civil 3D can't resolvethis problem and no boundary is created. By offsetting there all inside or all outside, so it works.

For testing I created a surface with the points in the boundary file and I can use the polyline without offset as boundary. 

It looks like the values in your terrain file and in the boundary file are differently rounded.

There also 3 points (336,338,339) in the terrain file without any hight information, and some points with height 0 (in terrain and boundary file)

[mgof]

I am not sure what you mean with "pLineOffset have a value when you call the function without offsetting the line".

pLineOffset is created as follows (offset from newly created polyline)

Dim pLine As AcadPolyline
Set pLine = MSpace.AddPolyline(Points)
pLine.Closed = True
Dim pLineOffset As Variant
pLineOffset = pLine.Offset(0.05)

About the input data, indeed there are mistakes, which I corrected (just adding a 0 data to the surface). The boundary polyline is anyway created adding a 0# value to the Z coordinates (making it a flat polyline even if not a LW). The result still remains the same: I get an error message when trying to add the offset polyline as boundary (runtime '424' object required on the Boundary.Add function), while the polyline has just been created. If I try to add that polyline as a boundary mnually in C3D, it works. I guess the error is in the syntax, but I still do not get where. Any idea?

Dim Points As Variant
Dim EndLineBuf As Integer
For i = 1 To 3
If EndLineBuf < shBuffer.Cells(1048576, i).End(xlUp).Row Then EndLineBuf = shBuffer.Cells(1048576, i).End(xlUp).Row
Next

ReDim Points(0 To (EndLineBuf - 1) * 3 - 1) As Double
Dim j As Integer

j = 0

For i = 2 To EndLineBuf
Points(j) = shBuffer.Cells(i, 2)
Points(j + 1) = shBuffer.Cells(i, 3)
Points(j + 2) = 0#
j = j + 3
Next i

Dim pLine As AcadPolyline
Set pLine = MSpace.AddPolyline(Points)
pLine.Closed = True

Dim pLineOffset As Variant
pLineOffset = pLine.Offset(0.05)

Dim sName As String
sName = "Outer Boundary"

Dim oNewBoundary As AeccSurfaceBoundary
Set oNewBoundary = oTinSurface.Boundaries.Add(pLineOffset, sName, aeccBoundaryOuter, True, 10.5)