Using the HEC-RAS Controller to Add Station-Elevation Data From A Spreadsheet使用HEC-RAS控制器从电子表格中添加桩号高

by Paige Brue

When I am manipulating station-elevation data for HEC-RAS, I often do this in Microsoft Excel. However, it can be tedious to copy and paste data for each cross-section into the Geometric Data Editor.

当我为HEC-RAS操作桩号高程数据时，我经常在Microsoft Excel中执行此操作。但是，将每个横截面的数据复制并粘贴到几何数据编辑器中可能会很繁琐。

In order to solve this problem, I used the HEC-RAS Controller to write a macro in Excel that automatically creates an updated HEC-RAS geometry file based on the cross-section data I entered in a spreadsheet. Keep in mind that this tutorial outlines the steps for editing cross-section data rather than creating new data.

为了解决这个问题，我使用HEC-RAS控制器在Excel中编写了一个宏，该宏根据我在电子表格中输入的横截面数据自动创建更新的HEC-RAS几何文件。请记住，本教程概述了编辑横截面数据而不是创建新数据的步骤。

The process for using the HEC-RAS Controller to add station-elevation data from a spreadsheet involves setting up your spreadsheet to reflect your code, accessing the HEC-RAS Controller, and creating a loop that edits the station-elevation data in the HEC-RAS geometry file.

使用HEC-RAS控制器从电子表格中添加桩号高程数据的过程包括设置电子表格以反映代码、访问HEC-RAS控制程序以及创建一个循环以编辑HEC-RAS几何图形文件中的桩号高程信息。

This macro is particularly useful if you are going to be conducting an iterative analysis. I hope you find this tutorial helpful.

如果要进行迭代分析，此宏特别有用。我希望你觉得这个教程有帮助。

What is the HEC-RAS Controller?

什么是Hec-Ras控制器？

Before I describe how I set up my macro, I will briefly explain what the HEC-RAS Controller is and how to use it. The HEC-RAS Controller is an application programming interface (API) which is a collection of functions and subroutines that can be called using code. These functions and subroutines allow the user to manipulate HEC-RAS externally. This tutorial will use Microsoft Excel’s Visual Basic for Applications (VBA). However, others have used programming languages such as Matlab and Python to access the HEC-RAS Controller.

Like many engineering exercises, you have to consider the level of effort required to write code. Will it take more time and effort to write code or to perform a task manually? Learning a new programming language takes time, but the ability to automate tedious tasks can be worth it. If you are going to perform a particular task many times, it may be worth taking the time to automate the task.

Tutorial

Step 1: Setting Up Your Spreadsheet

Before writing any code, it is important to set up your spreadsheet in such a way that it will match with the code we will be writing later. In one tab, add the cross-section station-elevation data. The setup is shown below.

In addition, you will want to set up another worksheet (in a different tab) that will contain the information needed to run the macro.

• XS: HEC-RAS Cross-Section
• Geom: This information must be obtained from the HEC-RAS geometry data file. Note that there may be duplicates (shown in red). See the image below for an example of how to obtain this information.
• Line Number: This value corresponds to the line value where the station-elevation data starts in the HEC-RAS geometry file. I recommend downloading Notepad ++ to view line numbers. See the image below for an example of how to obtain this information.
• Station Col: This refers to the column number of the “New Station” data on the first spreadsheet discussed.
• Elevation Col: This refers to the column number of the “New Elevation” data on the first spreadsheet discussed.

Step 2: Accessing the HEC-RAS Controller

Next, open the HEC-RAS Controller in Microsoft Excel. This process is described in detail in this blog post.

Step 3: Specifying File Paths

After setting up the HEC-RAS Controller macro, define the file paths you are going to use in Module 1. Modules are listed in a box on the left side of the screen.

Your HEC-RAS project may have several geometry files (.g01, .g02, .g03, etc.). Make sure you specify the geometry file that you are going to edit. Open the geometry file (in NotePad or NotePad ++) and save the file with “temp” on the end (e.g., .g01temp).

Private Sub HECRASController()

'Define Filepath - Put Public strRASProject in other module
strRASProject = "C:\filepath\ProjectName.prj"

'HEC-RAS Geometry File
strReadFileName = "C:\filepath\ProjectName.g10"

'Temporary Geometry File Where New Data Will Be Written
strWriteFileName = "C:\filepath\Project Name.g10temp"

End Sub

In Module 2, add your “filepath variables.” Adding this will help avoid errors with the VBA code.

Public strRASProject As String
Public strReadFileName As String
Public strWriteFileName As String

Step 4: Instantiating a New HEC-RAS Controller Class and Defining Variables

You can essentially copy and paste the code below. Just make sure you put “End” below all code to end the Sub. In addition, make sure you enter the correct data into Sheet_Name. This is the name of the worksheet where your cross-section station-elevation data is stored.

Finally, make sure you enter your version of HEC-RAS when instantiating a new HEC-RAS Controller class. For example, if you are running version 5.0.5, this line of code should read “Dim RC As New RAS505.HECRASController.”

Sub HECRASController1()
'Instantiate a new HECRASController Class
Dim RC As New RAS507.HECRASController

'Open HEC-RAS
RC.Project_Open (strRASProject)
RC.ShowRas 'Actually opens HEC-RAS on your desktop

'Define Variables
Dim i As Integer 'for iterating through text lines in geometry data
Dim j As Integer 'for iterating through Excel data
Dim k As Integer 'for iterating through excel data
Dim l As Integer 'for indexing station data
Dim m As Integer 'for indexing elevation data
Dim XS As Integer 'Cross-section name
Dim strTextLine As String
Dim StaElev As String
Dim strNewStaElLine As String 'New station-elevation data
Dim intLineNum As Integer 'for iterating through line numbers
Dim Sheet_Name As String 'Name of sheet used for getting station-elevation data
Dim Col_Sta As Integer 'Column in Spreadsheet
Dim Col_El As Integer 'Column in Spreadsheet
Dim Line_Number As Integer 'Column in Spreadsheet

Sheet_Name = "Sheet Name With Station-Elevation Data"

'Define the read geometry files
Open strReadFileName For Input As #1
Open strWriteFileName For Output As #2

Please note that code seems to work differently on each machine. That means, unfortunately, this code may not work perfectly on your computer even though it worked on mine. If you end up having to do some debugging, I recommend looking through stackoverflow.com. Alternatively, feel free to contact me if you have any questions, and I will do my best to help.

Step 5: Looping through the data for each cross-section

This is the fun part of this process. The “meat and potatoes” of this macro are two “Do While” loops within a “For Next” loop. A “For Next” loop allows you to go through a block of code a specific number of times. A “Do While” loop allows you to run a block of code until a specific condition is met.

Please note that the ParseStringbyEights function and the ParseStringbyEights function were written by Chris Goodell of The RAS Solution. You can find these functions in his book, “Breaking the HEC-RAS Code.”

For XS = 2 To 26 Step 1

'Variables for Loop
StaElev = Worksheets("HEC-RASController Data").Cells(XS, 2)
Col_Sta = Worksheets("HEC-RASController Data").Cells(XS, 4).Value
Col_El = Worksheets("HEC-RASController Data").Cells(XS, 5).Value
Line_Number = Worksheets("HEC-RASController Data").Cells(XS, 3).Value

Do While intLineNum < Line_Number
    Line Input #1, strTextLine
    intLineNum = intLineNum + 1
    If InStr(1, strTextLine, StaElev) Then 'number corresponds to station number in geometry data
    'If intLineNum = Line_Number Then
        Print #2, strTextLine
        Line Input #1, strTextLine
        intLineNum = intLineNum + 1
        
        Dim lngNumParts As Long
        Dim strPart() As String
        k = 0 'initial value for indexing iterating
        l = 0 'initial value for indexing station data
        m = 0 'initial value for indexing elevation data
        Do While InStr(1, strTextLine, "#Mann") = 0
            lngNumParts = ParseStringbyEights(strTextLine, strPart())
            If lngNumParts < 10 Then
                lngNumParts = 10
                ReDim Preserve strPart(9)
            End If
            
            'Replace Station Data with what is in spreadsheet
            For i = 0 To lngNumParts - 1 Step 1
                j = intLineNum - Line_Number + i + k
                If Not Trim(strPart(i)) = "" And j Mod 2 = 0 Then
                    strPart(i) = Worksheets(Sheet_Name).Cells(l + 3, Col_Sta)
                    l = l + 1
                End If
                If Not Trim(strPart(i)) = "" And j Mod 2 = 1 Then
                    strPart(i) = Worksheets(Sheet_Name).Cells(m + 3, Col_El)
                    m = m + 1
                End If
            Next i
            k = k + 9

            'Rebuild new line of station data
            strNewStaElLine = PutInFields(strPart(0), strPart(1), strPart(2), _
            strPart(3), strPart(4), strPart(5), strPart(6), strPart(7), strPart(8), _
            strPart(9))
            
            'Print new line of station elevation pair in temp geom file
            Print #2, strNewStaElLine
            
            'Go to next line and repeat
            Line Input #1, strTextLine
            intLineNum = intLineNum + 1
            
            Loop
            Print #2, strTextLine
        Else
            Print #2, strTextLine
        End If
    Loop
Next XS

Step 6:

Finally, this block of code will close the input and output text files, replace the original geometry file with the temporary one, delete the temporary file, and run your HEC-RAS project with the new geometry file.

'Close Text Files
Close #1
Close #2

'Replace the original geometry file with the temporary one. Then delete the temporary one.
FileCopy strWriteFileName, strReadFileName
Kill strWriteFileName

'Run HEC-RAS
Dim lngMessages As Long 'Number of Messages Returned
Dim strMessages() As String 'String of Messages Returned
RC.Compute_CurrentPlan lngMessages, strMessages()

Related Questions

Is it possible to retrieve output data using the HEC-RAS Controller?

Yes, it is possible to obtain output data from HEC-RAS using the HEC-RAS Controller.