Building a Structural (Engineering) Design eWorkbook – Part 2

So not going to jump straight into using MS Excel or LibreOffice Calc, rather going to start with VBScript, then move onto Excel/VBA. The reason is Basic in LibreOffice Calc doesn’t have an immediate window, and so all output has to be in  pop-up message boxes, or we have to start writing directly to spreadsheet cells. Another reason is that VBScript should be available on all MS Windows computers, and working from the command prompt can be more efficient than pointing and clicking. VBScript is a useful tool for transforming data and preparing data files which may otherwise be useful for use by the eWorkbook.

I have also already previously compared various calculation tools:

  1. Electronic Calculations (eCalc’s)
  2. Comparing Calculation Tools
  3. More Comparing Calculation Tools

For the comparison I had a simple sequence of calculations as shown in the following illustration, which is using SMath.

Simple Structural Calculation: Beam Bending Moment from Wind Load
Simple Structural Calculation: Beam Bending Moment from Wind Load

In developing the eWorkbook will expand on this calculation. For example need to consider multiple load cases, not just one load case, though if can identify the maximum load case only need to calculate the bending moment for the maximum not for every load case. The value of Cpe needs determining which is dependent on the shape of the building, an the reference pressure qz also needs to be calculated. So there are more inputs required and more calculations to carry out. Using SMath, can simply insert these extra calculations above the existing calculations, though automating the determination of Cpe may not be overly practical or feasible using SMath. However, through the comparison already opted for Excel/VBA has preferred tool not SMath or MathCAD.

The point to note here is that using SMath, the inputs, calculation, and outputs all appear on the screen and all can be printed to paper. If want to change the inputs then simply assign new values to the parameters (Cpe, qz, s, L), then print the page again. The equivalent VBScript for the calculation was given as:

Cpe=-0.7
qz=0.96 ‘kPa
pn=Cpe*qz ‘kPa
w=pn*3 ‘kN/m
L=6 ‘m
M=w*L^2/8 ‘kNm
Wscript.Echo “Moment: “, FormatNumber(M,2)

This can be stored in a script file say StructuralCalcs.vbs, if run from windows by clicking, it produces the following.

VBScript Output running through wscript
VBScript Output running through wscript

Its quick and produces the answer, but do not know what it is the answer for, nor can change the output unless change the script. It is thus not useful process for documenting calculations but it is useful for getting results and making decisions or taking actions based on the results. If run by clicking then will typically run through windows scripting host (WSH) by using wscript. If we use a command prompt then we can force it to use cscript and display the results on the command line by using: cscript structuralcalcs.vbs.

Running Script from command prompt through cscript
Running Script from command prompt through cscript

To display inputs and more output we need more echo statements. So an extended version of the script would be:


Cpe=-0.7
qz=0.96         'kPa
s=3         'm
L=6         'm
pn=Cpe*qz       'kPa
w=pn*s          'kN/m
M=w*L^2/8       'kNm

'Summarise Inputs and Results in Report File
Wscript.Echo "Cpe = " & CStr(Cpe) 
Wscript.Echo "qz = " & CStr(qz) & " kPa"
Wscript.Echo "s = " & CStr(s) & " m"
Wscript.Echo "L = " & CStr(L) & " m"
Wscript.Echo "pn = " & FormatNumber(pn,2) & " kPa"
Wscript.Echo "w = " & FormatNumber(w,2) & " kN/m"
Wscript.Echo "M = " & FormatNumber(M,2) & " kNm"

and the output:

Extended output of StructuralCalcs script
Extended output of StructuralCalcs script

I haven’t shown the equations in the output because the if there is a typing error, then the equations displayed won’t match the equations which are evaluated. For example could display M=w.L/8 whilst calculating M=w.L^2/8, since  M=WL/8 is a valid expression, the results may be confusing. Then again, no explanation has to what variable ‘M’ stands for, and could equally well put the wrong variable name alongside the result: though such error is easier to spot in the code. However if I was interested in purely report writing with calculations then would use a spreadsheet or SMath type software. The interest here is that the calculations drive something other than a report or in addition to the report.

The next task is to vary the inputs without rewriting the script. This can be done via the use of input boxes.

StructuralCalcs parameter input box
StructuralCalcs parameter input box

Now even though the script is ran through cscript it still pops up these boxes, one for each parameter, there is no going back if make an error or change mind, there is only stepping forward from one box to the next. The required script is as follows:


'Get Values of Input Parameters
Cpe=InputBox("Pressure Coefficient: ")
qz=InputBox("Pressure Coefficient: [kPa]")
s=InputBox("Beam Load Width: [m]")
L=InputBox("Beam Span: [m]")

'Do Some Calculations
pn=Cpe*qz       'kPa
w=pn*s          'kN/m
M=w*L^2/8       'kNm

'Summarise Inputs and Results in Report File
Wscript.Echo "Cpe = " & CStr(Cpe) 
Wscript.Echo "qz = " & CStr(qz) & " kPa"
Wscript.Echo "s = " & CStr(s) & " m"
Wscript.Echo "L = " & CStr(L) & " m"
Wscript.Echo "pn = " & FormatNumber(pn,2) & " kPa"
Wscript.Echo "w = " & FormatNumber(w,2) & " kN/m"
Wscript.Echo "M = " & FormatNumber(M,2) & " kNm"

To get rid of the pop-up boxes can make use of standard input and output, this will result in prompts for input on the command line, and the results displayed to the command line.

Using stdio to get input and display results
Using stdio to get input and display results

The required script becomes:


Option Explicit

'Example to Testing piping data input and output results
' cscript structuralcalcs03.vbs < data.txt

Dim Cpe     'External Pressure Coefficient
Dim qz      'Site Reference Wind Pressure [kPa]
Dim s       'Beam Spacing = Load Width [m]
Dim L       'Beam Span [m]
Dim pn      'Design Pressure [kPa]
Dim w       'Uniformly Distributed Design Load [kN/m]
Dim M       'Bending Moment [kNm]

Dim StdIn, StdOut
Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

'Get Values of Input Parameters
StdOut.WriteLine "External Surface Pressure Coefficient"
Cpe = StdIn.ReadLine

StdOut.WriteLine "Site Reference Pressure [kPa]"
qz = StdIn.ReadLine

StdOut.WriteLine "Load Width = Beam Spacing [m]"
s = StdIn.ReadLine

StdOut.WriteLine "Beam Span [m]"
L = StdIn.ReadLine

'Do Some Calculations
pn=Cpe*qz       'kPa
w=pn*s          'kN/m
M=w*L^2/8       'kNm

'Summarise Inputs and Results in Report File
StdOut.WriteLine "Cpe = " & CStr(Cpe) 
StdOut.WriteLine "qz = " & CStr(qz) & " kPa"
StdOut.WriteLine "s = " & CStr(s) & " m"
StdOut.WriteLine "L = " & CStr(L) & " m"
StdOut.WriteLine "pn = " & FormatNumber(pn,2) & " kPa"
StdOut.WriteLine "w = " & FormatNumber(w,2) & " kN/m"
StdOut.WriteLine "M = " & FormatNumber(M,2) & " kNm"

This time the script now has “Option Explicit” on and a requirement to declare variables in “Dim” statements, VBScript doesn’t support type declarations so no are given: all variables are of type variant. Whilst more than one variable can be defined in a “Dim” statement, I only define one variable if I provide a comment describing the variable.

Now that the script uses standard input/output (abbreviated: stdio), can now make use of MS DOS and command prompt tricks. Instead of sequentially stepping  through each of the input prompts, these can be pipe into the program using a file. The file requires one response per line. There are 4 input parameters so need a data file with 4 lines, thus:

-0.7
0.96
3
6

Such file is not very informative, as don’t know what each line represents unless look at the script, however can now call (run) the script thus: cscript structuralcalcs.vbs < data.txt.

Using stdio to get input piped in through a data file and display results
Using stdio to get input piped in through a data file and display results

This time the input prompts are displayed on the command line, but the inputs are not displayed. Since the results are also directed to stdio, can now go one step further and also direct the output to a file thus: structuralcalcs.vbs < data.txt < results.txt.

Using stdio to get input piped in through a data file and pipe results to a data file.
Using stdio to get input piped in through a data file and pipe results to a data file.

This time very little is displayed on the command line, as all the output is directed to the results file, which contains the following:


Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.

External Surface Pressure Coefficient
Site Reference Pressure [kPa]
Load Width = Beam Spacing [m]
Beam Span [m]
Cpe = -0.7
qz = 0.96 kPa
s = 3 m
L = 6 m
pn = -0.67 kPa
w = -2.02 kN/m
M = -9.07 kNm

Piping data like this is a useful way to run old MS DOS programs, using the shell command of VBA/VBScript. So for example Excel can be used to do preliminary calculations and get input parameters, then VBA can write a simple data file which is then piped into the application. The results can also be piped to a file, and then a VBA procedure can read the data into a worksheet for further calculation.

Anycase rather than pipe results to a file, it is preferable to write directly to a file, and retain some echo statements which display progress of the program on the command line. The next step therefore is to change the output from standard output to a text file. To do this need to create a file system object, and then a text stream object. So the script becomes:



Option Explicit

'Example to Testing piping data input and output results
' cscript structuralcalcs03.vbs < data.txt

Dim Cpe     'External Pressure Coefficient
Dim qz      'Site Reference Wind Pressure [kPa]
Dim s       'Beam Spacing = Load Width [m]
Dim L       'Beam Span [m]
Dim pn      'Design Pressure [kPa]
Dim w       'Uniformly Distributed Design Load [kN/m]
Dim M       'Bending Moment [kNm]

Dim StdIn, StdOut
Dim fpText
Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")
set fpText = fso.CreateTextFile("results.txt", True)


Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

'Get Values of Input Parameters
StdOut.WriteLine "External Surface Pressure Coefficient"
Cpe = StdIn.ReadLine

StdOut.WriteLine "Site Reference Pressure [kPa]"
qz = StdIn.ReadLine

StdOut.WriteLine "Load Width = Beam Spacing [m]"
s = StdIn.ReadLine

StdOut.WriteLine "Beam Span [m]"
L = StdIn.ReadLine

'Do Some Calculations
pn=Cpe*qz       'kPa
w=pn*s          'kN/m
M=w*L^2/8       'kNm

'Summarise Inputs and Results in Report File
fpText.WriteLine "Cpe = " & CStr(Cpe) 
fpText.WriteLine "qz = " & CStr(qz) & " kPa"
fpText.WriteLine "s = " & CStr(s) & " m"
fpText.WriteLine "L = " & CStr(L) & " m"
fpText.WriteLine "pn = " & FormatNumber(pn,2) & " kPa"
fpText.WriteLine "w = " & FormatNumber(w,2) & " kN/m"
fpText.WriteLine "M = " & FormatNumber(M,2) & " kNm"

WScript.Echo "Results in File: results.txt"
WScript.Echo "All Done!"

Whilst the command prompt appears like so:

Using stdio to get input piped in through a data file and results directly written to a file
Using stdio to get input piped in through a data file and results directly written to a file

and the result file appears thus:


Cpe = -0.7
qz = 0.96 kPa
s = 3 m
L = 6 m
pn = -0.67 kPa
w = -2.02 kN/m
M = -9.07 kNm

The script thus requires a lot more extra code than the calculation presented in SMath. The script can be further modified to directly read from a data file, in which case the input prompts become irrelevant, and therefore can be deleted.


Option Explicit

'Example structural calculations using hardcoded input/output filenames

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = 2, TristateTrue = -1, TristateFalse = 0

Dim Cpe     'External Pressure Coefficient
Dim qz      'Site Reference Wind Pressure [kPa]
Dim s       'Beam Spacing = Load Width [m]
Dim L       'Beam Span [m]
Dim pn      'Design Pressure [kPa]
Dim w       'Uniformly Distributed Design Load [kN/m]
Dim M       'Bending Moment [kNm]

Dim StdIn, StdOut
Dim fpText, fpTextIN
Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")
set fpText = fso.CreateTextFile("results.txt", True)
set fpTextIN = fso.OpenTextFile("data.txt")

Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

'Get Values of Input Parameters
Cpe = fpTextIN.ReadLine
qz = fpTextIN.ReadLine
s = fpTextIN.ReadLine
L = fpTextIN.ReadLine

'Do Some Calculations
pn=Cpe*qz       'kPa
w=pn*s          'kN/m
M=w*L^2/8       'kNm

'Summarise Inputs and Results in Report File
fpText.WriteLine "Cpe = " & CStr(Cpe) 
fpText.WriteLine "qz = " & CStr(qz) & " kPa"
fpText.WriteLine "s = " & CStr(s) & " m"
fpText.WriteLine "L = " & CStr(L) & " m"
fpText.WriteLine "pn = " & FormatNumber(pn,2) & " kPa"
fpText.WriteLine "w = " & FormatNumber(w,2) & " kN/m"
fpText.WriteLine "M = " & FormatNumber(M,2) & " kNm"

WScript.Echo "Results in File: results.txt"
WScript.Echo "All Done!"

The format of the data input file remains the same as used for the piping examples, and the results file also remains the same. The names of the input file and  the output file are hardcoded into the program, so if wish to use different file names have to modify the program, or rename previous files if don’t want to overwrite the results. Some times hardcoding the filenames is useful, its quick, and simplifies use of the program. However if we wish to provide different file names, then the script/program needs to get some input as to what the filenames are. There are two ways to do this. The first way would be to add some prompts using either input boxes or StdIn, and alternative way and my preferred way is to add command line parameters to the script.

The first parameter could be the input file name and the second parameter the results file name. Alternatively, the result file can have the same base file name as the input file and just be given a different file extension. For example the data file could be project.dat and the result file be project.rpt. To do this, will modify the whole script into a subroutine, with the main script checking the command line and calling the calculation subroutine only if it has a filename to pass to the subroutine.

The revised script is as follows.


Option Explicit

'Example structural calculations using hardcoded input/output filenames

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = 2, TristateTrue = -1, TristateFalse = 0

Sub StructuralCalcs(dataFileName,resultFileName)
    Dim Cpe     'External Pressure Coefficient
    Dim qz      'Site Reference Wind Pressure [kPa]
    Dim s       'Beam Spacing = Load Width [m]
    Dim L       'Beam Span [m]
    Dim pn      'Design Pressure [kPa]
    Dim w       'Uniformly Distributed Design Load [kN/m]
    Dim M       'Bending Moment [kNm]

    Dim StdIn, StdOut
    Dim fpRpt, fpData
    Dim fso

    Set fso = CreateObject("Scripting.FileSystemObject")
    set fpRpt = fso.CreateTextFile(resultFileName, True)
    set fpData = fso.OpenTextFile(dataFileName)

    Set StdIn = WScript.StdIn
    Set StdOut = WScript.StdOut

    'Get Values of Input Parameters
    Cpe = fpData.ReadLine
    qz = fpData.ReadLine
    s = fpData.ReadLine
    L = fpData.ReadLine

    'Do Some Calculations
    pn=Cpe*qz       'kPa
    w=pn*s          'kN/m
    M=w*L^2/8       'kNm

    'Summarise Inputs and Results in Report File
    fpRpt.WriteLine "Cpe = " & CStr(Cpe) 
    fpRpt.WriteLine "qz = " & CStr(qz) & " kPa"
    fpRpt.WriteLine "s = " & CStr(s) & " m"
    fpRpt.WriteLine "L = " & CStr(L) & " m"
    fpRpt.WriteLine "pn = " & FormatNumber(pn,2) & " kPa"
    fpRpt.WriteLine "w = " & FormatNumber(w,2) & " kN/m"
    fpRpt.WriteLine "M = " & FormatNumber(M,2) & " kNm"

    WScript.Echo "Results in File:" & resultFileName

End Sub


Sub cMain '(ByVal cmdArgs() )
    Dim fso, WshShell, objArgs
    
    'General
    Dim fPath0, fPath1
    Dim fDrv , fPath, fName, fExt
    Dim ifullName 
    Dim ofullName 

    
    WScript.Echo "Main ..."
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set WshShell = CreateObject("WScript.Shell")
    Set objArgs = WScript.Arguments
    
    
    ' See if there are any arguments.
    If objArgs.Count = 1  Then
        fPath0 = objArgs(0)
        fPath1 = fso.GetAbsolutePathName(fPath0)
        
        
        fDrv = fso.GetDriveName(fPath1)
        fPath = fso.GetParentFolderName(fPath1)
        fName = fso.GetBaseName(fPath1)
        fExt = fso.GetExtensionName(fPath1) 
        
        WScript.Echo fDrv
        WScript.Echo fPath
        WScript.Echo fso.GetBaseName(fPath1)
        WScript.Echo fso.GetFileName(fPath1)
        WScript.Echo fExt
        
        ifullName = fPath1
        ofullName = fPath & "\" & fName & ".rpt"
        
        WScript.Echo "INPUT: Data File: <" & ifullName & ">"
        WScript.Echo "OUTPUT: Data File: <" & ofullName & ">"
        
        StructuralCalcs ifullName,ofullName
        
        
    Else
        WScript.Echo "Not enough parameters: provide data file name, include file extension"
    End If
    
    WScript.Echo "... Main"
    WScript.Echo "All Done!"
    
End Sub



'------------------------------------------------------------------------------  
'MAIN
'------------------------------------------------------------------------------

cMain

'==============================================================================
'END MAIN
'==============================================================================

The main script now is just a reference to the subroutine “cMain” which processes the command line parameters, rips the filename apart using file system object methods (though I normally do this using own variations of fnsplit and fnmerge). It then builds a suitable filename for the results report, it then passes the two filenames to the structural calculations subroutine, which reads the data file (fpData), and prints the output to the results report (fpRpt). {I changed the names of the textstreams for this version of the script}

So now have a relatively long script to do some relatively trivial calculations. A data file which doesn’t explain the input data, and a results file which isn’t a comprehensive report and at the same time not very useful as input to further calculations. If the result file was formatted/structured differently then it could be piped into another structural calculation script/program.

A simple way to make the data file more meaningful is with tag/value pairs, this is similar to an AutoCAD dxf file if familiar with such, only simpler. Each tag is on a separate line, and is followed on the next line by its value. This is the kind of file I used for the soil heave program and is one of the export formats I use for my spreadsheets. The example script the data file would look something like the following:


Cpe
-0.7
qz
0.96
s
3
L
6

The reason for adopting such file format is because with most programming languages problems result if text and numbers are contained on the same line. Also whilst VBA as built-n facility to read a comma delimited file format, most programming languages do not, and file parsers have to be written. Parsing the tag/value pairs is easier than parsing a comma delimited file. But still a file parser has to be  written.

One way to avoid designing a file structure and then writing a file parser for such file, is to use a database management system (DBMS). In the past using MS DOS my options would have been DBase and Paradox, today more likely to be MS Access, LibreOffice Base, sqlite, MySQL.

Using a DBMS like DBase, Paradox of MS Access, not only is it easy to create data and results files, but also easy to create data input forms with validation. So rather than program in say Turbo Pascal or Turbo C, a program can be much more rapidly written using say Paradox application language (PAL) and its form builder. A DBMS also makes it easy to input and edit data, browse and view data, search for data. A DBMS saves a significant amount of time compared to programming everything from scratch. Unfortunately most offices provide MS Office to their staff without MS Access. It is more complicated and time consuming to develop a data input form with validation using VBA and MS Forms compared to creating such form in MS Access. On the other hand the form created with MS Forms and VBA should be compatible with other VBA capable software (eg. Word, ProgeCAD).

Now whilst MS Access makes it easier to work with data, we don’t need MS Access on a computer to be able to read and modify MS Access files (.mdb, .accdb), if using MS Windows operating system. The data files can be accessed using Data Access Objects (DAO) or ActiveX Data Objects (ADO). I typically use DAO, and that is what I used for my AS4600 quick calculator spreadsheet. By using VBA/DAO can return a value for phi.Ms directly into a single cell of a spreadsheet without having to read all the steel section properties into a worksheet, which can be a problem if wish to design/check multiple sections for an entire structure in one workbook. Whilst MS Excel can query and retrieve data from an MS Access table into a single worksheet, typically don’t want to read all the data just the data for the specific steel section. So the materials library initially was a paradox database file, which could be read directly by QPro spreadsheet, in moving to MS Excel and MS Access, such approach was not possible, so instead  the MS Access file was queried into an additional workbook. All new data was added to MS Access then refreshed by query in the workbook used as a lookup table. However incompatible versions of MS Access and MS Excel, prevented this from continuing, rather than have multiple Excel files and Access files, I broke the link between the two, and the Excel file was updated rather than the Access file. So every now and again I have to manually refresh the Access file.

The reason for putting the section property data in a DBMS is that it is easier to edit the data than in a spreadsheet. Whilst freezing panes, and data forms in MS Excel can assist with data input to wide and long tables, the creation of input forms and queries makes editing the data in MS Access far better. It is therefore preferable to remove the spreadsheet version of the section library and increase usage of the Access version.

Now not only can MS Access be used for storing and editing section properties data it can be used for storing the input data for the the simple reference script and the results. We can ignore the report, since once we have the results stored we can generate any report we want to display both the inputs and results: reporting is secondary to getting the numbers. {May not seem so if do calculations with pencil and paper, where documenting/reporting is simultaneous with calculation process.}

At the present my interest is reading and modifying existing MS Access files, though I believe it is possible to create a file using DAO, I haven’t tried doing so, so I will assume that MS Access is available to create a file. If don’t have MS Access then MDB viewer Plus can be used to both view existing files and create new files (also use for .accdb files).

As I was having trouble using VBScript and using open database connectivity (OCDB) drivers to connect to sqlite, and wasn’t intending to write script in MS Access, I don’t yet have such scripts in VBScript as I went and wrote them in Excel/VBA. The objective here however is to write scripts without the aid of the VBA editor, without intellisense, and without code completion, and without COM references, and without object browser, just use of a plain text editor.

A plain text editor is our simplest notebook, and eWorkbook, on condition we have something to translate the content.

So having got the script to a stage it can read and write files, the next step will be to change it to read and write specific types of file. The VBScript program could store data in an MS Excel file, reading and writing data from such, it could create formatted report in MS Word, or produce a drawing in ProgeCAD (or AutoCAD).

However, the starting point is to use drivers available in MS Windows and which doesn’t require any additional software, unless it is available for no fee.

So in the next part will consider storing data in the following file types:

  1. xml
  2. MS Access (.mdb ; .accdb)
  3. LibreOffice Base (HSQLDB)
  4. sqlite
  5. MySQL
  6. MS Excel

Originally I just intended writing simplified scripts to test and illustrate each of these DBMS and file types, which I did. But then I decided to use the script used in comparing calculation tools as a reference, and wrote the script for sqlite based on such. Furthermore I did the calculation sequence in MS Access using a single query, so tested the use of structured query language (SQL) in the other DBMS’s from which I discovered that I needed to do calculation in stages rather than single query (probably is possible in a single query, but it becomes an involved and cumbersome nesting).

Queries are useful way to do calculations on tabulated data, for example can calculate phi.Ms for all the steel sections in a database table, just by using query by example (QBE) if have a suitable user defined function (UDF) which can be called.

It is to be noted that even if we have a file structure provided by a DBMS or XML, we still have a variety of choices on how the data will be stored. For example I could store Excel Range names as tags in XML, however local names contain and exclamation mark (!) to separating the worksheet name from the range name, this exclamation mark is not allowed in an xml tag name. The alternative is to create xml tags like: parameter, name, value, and then store the range name wrapped in such tags: this is the way I adopted in my earlier attempt to export data from MS Excel. For my original attempt I wrote my own parser to get the data, then I discovered MSXML object. So I recently rewrote the export routines using this, but using the range names as tags, it crashed due to presence of “!” in the names: I had to find a rare file in which I used local range names. So will stick with the original concept.

As an example of using XML as a data source can look at the balustrade design program in which the AS1170 barrier load tables are stored in XML, it is written in vb.net, but there are also variations written in VBA and using MS Excel as the interface, as well as those which just use MS Excel with the loading table placed in MS Excel worksheet: start at version two and move from one to the next: as capabilities increase it becomes more dependent on VBA.

More about the file types in the next post.


Revision:

  1. [26/11/2018] : Original