How to Make an Excel Fixed Width File from SWMM 5

How to Make an Excel Fixed Width File from SWMM 5

How to Make an Excel Fixed Width File from SWMM 5

by dickinsonre
How to Make an Excel Fixed Width File from SWMM 5
You can use this macro for MATLAB or some other DOS based program by using the Tools command of SWMM 5 and configuring the EXCEL file option.  If you use the EXCEL tool then your SWMM 5 input file will be opened up in EXCEL and you can use this macro to make a fixed format file for SWMM 5.  Note, SWMM 5 does not use fixed format but other programs may need it for reading,  You adjust the column widths in the macro for your program.
Here is the macro, which is placed  in Excel using these command:
  1. Copy the code above.
  2. Open Excel.
  3. Alt + F11 to open the Visual Basic Editor.
  4. Click Insert-Module from the menu.
  5. Paste the code into the window that appears at right.
Option Explicit
Sub CreateFixedWidthFile(strFile As String, ws As Worksheet, s() As Integer)
    Dim i As Long, j As Long
    Dim strLine As String, strCell As String
    
     'get a freefile
     ' source is http://www.vbaexpress.com/kb/getarticle.php?kb_id=759
    Dim fNum As Long
    fNum = FreeFile
    
     'open the textfile
    Open strFile For Output As fNum
     'loop from first to last row
     'use 2 rather than 1 to ignore header row
    For i = 1 To ws.Range("a65536").End(xlUp).Row
         'new line
        strLine = ""
         'loop through each field
        For j = 0 To UBound(s)
             'make sure we only take chars up to length of field (may want to output some sort of error if it is longer than field)
            strCell = Left$(ws.Cells(i, j + 1).Value, s(j))
             'add on string of spaces with length equal to the difference in length between field length and value length
            strLine = strLine & strCell & String$(s(j) - Len(strCell), Chr$(32))
        Next j
         'write the line to the file
        Print #fNum, strLine
    Next i
     'close the file
    Close #fNum
    
End Sub
 'for example the code could be called using:
Sub CreateFile()
    Dim sPath As String
    sPath = Application.GetSaveAsFilename("SWMM5_Fixed_EXPORT", "Text Files,*.inp")
    If LCase$(sPath) = "false" Then Exit Sub
     'specify the widths of our fields
     'the number of columns is the number specified in the line below +1
    Dim s(15) As Integer
     'starting at 0 specify the width of each column for the SWMM5 File, alter the columns if you need more columns in your data input file
    s(0) = 40
    s(1) = 20
    s(2) = 20
    s(3) = 20
    s(4) = 20
    s(5) = 20
    s(6) = 20
    s(7) = 20
    s(8) = 20
    s(9) = 20
    s(10) = 20
    s(11) = 20
    s(12) = 20
    s(13) = 20
    s(14) = 20
    s(15) = 20
     'for example to use 3 columns with field of length 5, 10 and 15 you would use:
    'dim s(2) as Integer
     's(0)=5
     's(1)=10
     's(2)=15
     'write to file the data from the activesheet
    CreateFixedWidthFile sPath, ActiveSheet, s
End Sub
 .

Comments

Post a Comment

Popular posts from this blog

Orifice and Weir flow calculations

Note: Orifice and Weir Flow Computations The orifice flow calculation proceeds as follows: 1. Initially and whenever the setting (i.e., the fraction opened) changes, flow coefficients for both orifice and weir behavior are computed as follows: a. For side orifices: Define Hcrit = h/2 where h is the opening height. b. For bottom orifices: i. For a circular orifice, compute area over length (i.e., circumference) as AL = h /4. ii. For a rectangular orifice compute AL = h*w/(2*(h+w)) where w is the opening width. iii. Compute Hcrit = Cd*AL/0.414 where Cd is the orifice discharge coefficient. At step 1b, the critical head for the bottom orifice, where orifice flow turns into weir flow, is found by equating the result of the orifice equation to that of the weir equation Cd*Area*sqrt(2g)*sqrt(Hcrit) = Cw*Length*sqrt(Hcrit)*Hcrit or Hcrit = (Cd * Area) / (Cw/sqrt(2g) * Length) The value of Cw/sqrt(2g) for a sharp crested weir is 0.414.
Read more

SWMM 5 Error Messages

Engine Error Number Description ERROR 101: memory allocation error.                                                                                             ERROR 103: cannot solve KW equations for Link                                                                                   ERROR 105: cannot open ODE solver.                                                                                              ERROR 107: cannot compute a valid time step.                                                                                     ERROR 108: ambiguous outlet ID name for Subcatchment                                                                            ERROR 109: invalid parameter values for Aquifer                                                                                  ERROR 110: ground elevation is below water table for Subcatchment                                                         
Read more

EMC Washoff in SWMM5

Image
There are four steps to using EMC concentrations in your network: 1. Define your pollutant by adding a pollutant using the Data=>Quality=>Pollutant command : 2. Define the Land Use by using the Data=>Land Uses command or the Land Use Editor: 3. Define Buildup to be None by clicking on the None Tab: 4. Define the EMC Washoff concentration by clicking on the Washoff Tab: This is where you would add a GW concentration More: http://www.swmm2000.com/
Read more