Building a Structural (Engineering) Design eWorkbook – Part 3

Note that I am using VBScript because it belongs to a family of languages, and capability basically increases from VBScript, VBA to VB, to VB.net. Whilst software may support COM automation or whatever the .net equivalent is called, not all software hosts VBA. For example Multiframe structural analysis and DesignCAD have object models which are accessible from VBA but neither hosts the VBA and the development environment. These programs can however be controlled from other programs which host VBA or alternatively by using VBScript and the command prompt. I typically use MS Excel/VBA however my Multiframe license and its hardware lock currently only run on MS Windows XP, which I only have on a netbook with no MS Office license. My main computer is a laptop running MS Windows 10, with Office 365.  By using VBScript I can remove the dependence on MS Excel and run the application on the netbook or any other Windows computer without office.

If preference was to use google sheets, then an alternative family of languages would be JScript, JavaScript, Java, and possibly C#.  Where JScript is Microsoft’s variation of JavaScript which runs under Windows Scripting Host (WSH) the same way that VBScript does. So that which I write in VBScript could be equally well written in JScript.

Can also consider comparing VBScript and JScript to Octave. Where Octave is part of the family of applications including: MatLAB, SciLAB and FreeMAT. The last time I looked at Octave it had a relatively primitive command prompt, running in a command prompt window. About the only benefit of Octave over VBScript is that I can do arithmetic on its command line: I don’t really need that facility if I want some simple number crunching I can use the immediate window of VBE editor, use Excel worksheet, or more frequently use ATCalc. In the main however want to save the calculation sequence in a file for future use. If like dependence on the internet then could opt for python and jupyter notebooks. However the task at hand involves multiple desktop applications, not just an over sophisticated calculator. Data from Excel to drive Multiframe, the results used in further Excel calculations, and then conclusions used in materials lists and specifications and drawings produced in ProgeCAD or AutoCAD LT. A spreadsheet cell cannot directly drive another application, it can contain a flag or switch value, or it can hold a command button which can execute a macro.

Another issue is timing between VBA and worksheet calculations: the issue is that VBA can execute faster than a worksheet can update its calculations. So to have VBA modify a worksheet cell and then grab the results for use elsewhere will typically fail. So it is better to just use the worksheet as a data store in such situations and do all the calculations in VBA. Here, presentation of calculations is a secondary consideration the primary task is to get results of calculations and make decisions and/or automate some action.

If presentation is the primary consideration then would use the MS Excel worksheets, and wouldn’t have need for user defined functions (UDF) as would want to present all the calculations contained within. That however would be relatively slow and unproductive, when considering established technology and established technical science and technical mathematics. We know how to design and assess established technologies we just want to design and assess them a lot faster, and implement them a lot faster. I would contend that for the past 20 to 30 years computer calculation has not really improved productivity and this is largely because still operating with the pencil and paper mentality. Computers are carrying out a lot more calculations than were done with pencil and paper, and then these calculations are then printed out to be read. It is mostly scrap paper and no value, it doesn’t matter if print to paper or an electronic document its junk and a waste of time to read in full. The whole thing can be summarised a lot more concisely or with 3D printers go straight to production.

Increased automation of software such as pro/engineer (Creo Parametric), archicad, revit will ultimately put the calculations behind the scenes of virtual models of physical systems. An architect deletes a column and the floor beam it supports turns red to indicate the current steel section is no longer adequate, the architect runs update and the steel section is modified. The architect can see the effect and then undo it, if they don’t like the consequences. More over the software can be set to optimise a variety of parameters, which are constrained by various rules, such that an entire product (building, ship etc..) can be auto generated by computer. The results can be manually overwritten or additional rules can be added until the optimisation and auto generation produces the desired output.

The point is some pretty looking calculations in MathCAD are of no value, unless the objective is to explain the science and mathematics behind a decision. I would contend that for more than 80% of projects, there is no need to explain the science and mathematics, its is established and so is its application to the technology being designed. So only real concern is: did the black box actually make a real assessment or did it just toss a coin to decide whether to accept or reject? One way to test the black box is to push lots of data with known results through the box, and see if it produces the expected results. So the block box needs to be able to read data files and produce result files: not just for one beam or building, but for many buildings. So not just looking at database (DBMS) to store parameters for one project but for all projects. {NB: I am aware that MathCAD does have some advanced capabilities to interact with other software and possibly hardware. However, it is not an affordable design engine, for small business to integrate into product configurators. }

The eWorkbook being considered here as multiple modes, it is to be a product configurator for use by salespeople and members of the public without any training, it has to be possible to interrogate the software to assess the validity of results, and be able to produce anything from a simple summary report of assessment made, to detail presentation of calculations. Now I don’t know what the format of a MathCAD file is, but a SMath file is just an xml file. It is thus possible for VBA to export calculations to SMath for presentation and further interrogation. Alternatively it is also possible to read the SMath xml file and generate VBA code, as it is also possible to export an Excel worksheet to VBA code.

So at this stage working with VBScript, I am simply building a box of tools, which can work without Excel but which can be enhanced working with Excel, and more enhanced working inside Excel as VBA code.

Reading Data Files

So based on the last post, our tool box comprises of:

  1. The Windows command prompt
  2. WSH cscript
  3. VBScript
  4. Text Editor (eg. Notepad, UltraEdit, Notepad++)

Also got to the point of reading an input data file and writing a result report file. The data file doesn’t explain the data it contains, and the result file not suitable as input to another application. I proposed could use a simple tag/value pair file format: the tag on one line followed by the value on another. Whilst this is possible it requires writing a file parser, as the tag/value pairs can be in any order, so as each is read, the program needs to know what to do with them. Whilst not that difficult to write it becomes more involved as move from simple scalar variables to lists and tables. Whilst I have a parser to do such, I will skip, and move onto more standardised and common file formats. Starting with xml: Extensible Markup Language. With xml, we have flexibility, we can create any tag names we wish, along with any attributes. For the reference program I could create tags: Cpe, qz, s, L. Alternatively I could create tags, parameter,name,value, units. This latter choice was my first choice when I wrote application to export MS Excel named ranges to xml, the brute force way. Attempting to use the first approach by using the MSXML object, and exporting all cells to workbook, hit a flaw: in that Excel local variable names contain “!” character and this isn’t allowed in xml tag names. This is not a big problem as can simply replace with some other valid character, possibly “-” or can delete altogether.

Anycase to test my simple reference program, I coded for both approaches. However before writing such I modified the structurallcalcs.vbs file to structuralcalcs.wsf file. The wsf files have the advantage that multiple scripting languages (VBScript/JScript) can be used in the one file, and multiple files can be included into the one script. So can create a library of useful scripts, and include them into larger projects as needed.


'
' Copyright (c)2018 S C Harrison
' Refer to License.txt for terms and conditions of use.
'
'Purpose:
'Illustrate Structural Calculations using VBScript

    <script language="VBScript" src="ErrorHandlers.vbs"/>
    <script language="VBScript" src="xdbAccess.vbs"/>
    <script language="VBScript" src="StructuralCalcs.vbs"/>


<script language="VBScript">

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

cMain

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

</script>




The above is structuralCalcs.wsf whilst the the main script (structuralCalcs.vbs) now becomes:


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: <" & fso.GetFileName(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: <" & fso.GetFileName(ifullName) & ">"
        WScript.Echo "OUTPUT: Data File: <" & fso.GetFileName(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

The main script no longer contains code outside a subroutine except for global variable declarations. The script is now called from the command prompt: cscript structuralcalcs.wsf followed by any parameters if parameters are required. I can now change the reference in the wsf file to test variations of structuralcalcs.vbs

Using XML Data Files

So to read the data from an xml file and save results to another file the script now becomes:


'
' Copyright (c)2018 S C Harrison
' Refer to License.txt for terms and conditions of use.
'
Option Explicit
    
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 xmlResultsFileName
    
Sub SaveResults
    Dim xmldoc
    Dim oRoot
    Dim oNode
    Dim tmpNode
    Dim txtNode
    
    Set xmldoc = CreateObject("MSXML2.DOMDocument.6.0")
    xmldoc.async = False
    'Create Root Node of Document
    Set oRoot = xmldoc.createNode(1, "structData", "")
    Call xmldoc.appendChild(oRoot)
    
    'Create Tag and Value
    Set tmpNode = xmldoc.createElement("pn")
    Call oRoot.appendChild(tmpNode)
    Set txtNode = xmldoc.createTextNode(CStr(pn))
    Call tmpNode.appendChild(txtNode)
    
    Set tmpNode = xmldoc.createElement("w")
    Call oRoot.appendChild(tmpNode)
    Set txtNode = xmldoc.createTextNode(CStr(w))
    Call tmpNode.appendChild(txtNode)
    
    Set tmpNode = xmldoc.createElement("M")
    Call oRoot.appendChild(tmpNode)
    Set txtNode = xmldoc.createTextNode(CStr(M))
    Call tmpNode.appendChild(txtNode)
    
    Call xmldoc.Save(xmlResultsFileName)
    
End Sub
    
Function xmlGrabParameterV2(xmldoc, paramName)
  Dim queryStr
  Dim nodeList
  Dim paramValue
  
  queryStr = "//" & paramName
  'Wscript.Echo queryStr
  
  Set nodeList = xmldoc.selectNodes(queryStr)
  If nodeList.Length <> 0 Then
    paramValue = nodeList.item(0).Text
  Else
    paramValue = "<<N/A>>"
  End If
  
  xmlGrabParameterV2 = paramValue
  
End Function
    

'Import XML type data file
Sub StructuralCalcs(xmlFileName)

    Dim fullname 

    Dim xmldoc
    Dim curNode
    Dim tmpNode
    Dim tmpList

    Dim oRoot
    Dim oNode
    Dim oChild
    Dim oChildren
    Dim oElement

    Dim attrValue

    Dim paramName
    Dim paramValue
    

    Wscript.Echo "StructuralCalcs ..."
  
    If xmlFileName <> "" Then
        Wscript.Echo xmlFileName
        Set xmldoc = CreateObject("MSXML2.DOMDocument.6.0")
        xmldoc.async = False
        If xmldoc.Load(xmlFileName) Then
            Wscript.Echo "Loaded XML Document"

            Cpe = xmlGrabParameterV2(xmldoc, "Cpe")
            qz = xmlGrabParameterV2(xmldoc, "qz")
            s = xmlGrabParameterV2(xmldoc, "s")
            L = xmlGrabParameterV2(xmldoc, "L")

            'Do Some Calculations
            pn=Cpe*qz       'kPa
            w=pn*s          'kN/m
            M=w*L^2/8       'kNm
            
            
            
            'Echo Inputs
            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"
            
            SaveResults
            
        Else
            Wscript.Echo "Failed to Load XML document"
        End If

    Else
        Wscript.Echo "File: Failed to Open"
    End If


    Wscript.Echo "... StructuralCalcs"
    Wscript.Echo "All Done!!"

End Sub




Sub cMain '(ByVal cmdArgs() )
    Dim startTime, endTime
    Dim anykey
    Dim fso, WshShell, objArgs
    Dim xmlDataPath
    Dim path1


    Wscript.Echo "----------------------------------------------------------------------"
    WScript.Echo "Main ..."
    Wscript.Echo "----------------------------------------------------------------------"
    startTime = now
    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
        'Do Something
        xmlDataPath = objArgs(0)
        WScript.Echo xmlDataPath
        path1 =  fso.GetAbsolutePathName(xmlDataPath)
        xmlResultsFileName = fso.GetParentFolderName(path1) & "\results.xml"
        WScript.Echo fso.GetFileName(xmlResultsFileName)
        
        
        'Do Something with xml File
        call StructuralCalcs(xmlDataPath)
        
        'All Done
        '========
    Else
        WScript.Echo "Not enough parameters: provide data file name"
    End If
    
    
    
    
    endTime = now
    Wscript.Echo "======================================================================"
    Wscript.Echo "Duration: ",FormatDateTime(endTime - startTime, 4)
    WScript.Echo  "... Main"
    WScript.Echo "All Done!"
    Wscript.Echo "======================================================================"
    
End Sub

There is a function xmlgrabparameterV2 which grabs a value from the xml file and assigns it to a variable, VBScript at present takes care of type conversion from string to number. The subroutine SaveResults then saves the calculated results to another xml file. The data file can change but the results are always exported to results.xml. An alternative would be to combine data and results into the same file: renaming the original file to *.bak before creating new file. For this to work, some version of MSXML is required on the computer, and the statement CreateObject(“MSXML2.DOMDocument.6.0”) may have to be modified to suit the version available. Also need to learn how to query the xml file using XPATH. The data files can be created and viewed with any text editor or can use a more specialised editor like XML Notepad 2007, which can use to experiment with XPATH queries. The xml files can be made more complex and attributes can be added. At this stage just concerned about getting some data into the file. The above code is the situation where the variables (Cpe,qz,s,L etc..) are used as tags.

The alternative approach using tags: parameter, name, value, units requires a more complicated XPATH query. I couldn’t figure out a direct query to get the values, so settled for getting close then using the MSXML object model to get the required data. The code is as follows:


'
' Copyright (c)2018 S C Harrison
' Refer to License.txt for terms and conditions of use.
'
Option Explicit
    
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 xmlResultsFileName
    
Sub SaveVariable(xmldoc,oRoot, varName, varValue, varUnits)
    Dim oNode
    Dim mainNode
    Dim nameNode
    Dim valueNode
    DIm unitsNode
    Dim txtNode
    
    Set mainNode = xmldoc.createElement("variable")
    Call oRoot.appendChild(mainNode)
    
    Set nameNode = xmldoc.createElement("name")
    Call mainNode.appendChild(nameNode)
    Set txtNode = xmldoc.createTextNode(varName)
    Call nameNode.appendChild(txtNode)
    
    Set valueNode = xmldoc.createElement("value")
    Call mainNode.appendChild(valueNode)
    Set txtNode = xmldoc.createTextNode(CStr(varValue))
    Call valueNode.appendChild(txtNode)
    
    
    Set unitsNode = xmldoc.createElement("units")
    Call mainNode.appendChild(unitsNode)
    Set txtNode = xmldoc.createTextNode(varUnits)
    Call unitsNode.appendChild(txtNode)
    
    

    
End Sub
    
    
    
Sub SaveResultsV1
    Dim xmldoc
    Dim oRoot
    Dim oNode
    Dim tmpNode
    Dim txtNode
    
    Set xmldoc = CreateObject("MSXML2.DOMDocument.6.0")
    xmldoc.async = False
    'Create Root Node of Document
    Set oRoot = xmldoc.createNode(1, "structData", "")
    Call xmldoc.appendChild(oRoot)
    
    'Create Tag and Value
    call SaveVariable(xmldoc,oRoot,"pn",pn,"kPa")
    call SaveVariable(xmldoc,oRoot,"w",w,"kN/m")
    call SaveVariable(xmldoc,oRoot,"M",M,"kNm")
    
    Call xmldoc.Save(xmlResultsFileName)
    
End Sub
    
Function xmlGrabParameterV1(xmldoc, paramName)
    Dim queryStr
    Dim paramValue
    Dim oNode
    Dim oNode0

    Call xmldoc.SetProperty("SelectionLanguage", "XPath")
    queryStr = "//structData/parameter/name[text()='" & paramName & "']"

    'Wscript.Echo queryStr

    Set oNode = xmldoc.SelectSingleNode(queryStr)

    If NOT(ONode is Nothing) then
        'Wscript.Echo oNode.text, oNode.nextSibling.Text, oNode.parentNode.nodeName
        set oNode0 = oNode.parentNode
        'Wscript.Echo oNode0.text
        queryStr = "value"
        Set oNode = oNode0.SelectSingleNode(queryStr)
        If NOT(ONode is Nothing) then
            paramValue = oNode.Text
        Else
            paramValue = "<<N/A>>"
        End If
    Else
        Wscript.Echo "Not Found: <" & paramName & ">"
        paramValue = "<<N/A>>"
    End If

    xmlGrabParameterV1 = paramValue
  
End Function
    

'Import XML type data file
Sub StructuralCalcs(xmlFileName)

    Dim fullname 

    Dim xmldoc
    Dim curNode
    Dim tmpNode
    Dim tmpList

    Dim oRoot
    Dim oNode
    Dim oChild
    Dim oChildren
    Dim oElement

    Dim attrValue

    Dim paramName
    Dim paramValue
    

    Wscript.Echo "StructuralCalcs ..."
  
    If xmlFileName <> "" Then
        Wscript.Echo xmlFileName
        Set xmldoc = CreateObject("MSXML2.DOMDocument.6.0")
        xmldoc.async = False
        If xmldoc.Load(xmlFileName) Then
            Wscript.Echo "Loaded XML Document"

            Cpe = xmlGrabParameterV1(xmldoc, "Cpe")
            qz = xmlGrabParameterV1(xmldoc, "qz")
            s = xmlGrabParameterV1(xmldoc, "s")
            L = xmlGrabParameterV1(xmldoc, "L")

            'Do Some Calculations
            pn=Cpe*qz       'kPa
            w=pn*s          'kN/m
            M=w*L^2/8       'kNm
            
            
            
            'Echo Inputs
            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"
            
            SaveResultsV1
            
        Else
            Wscript.Echo "Failed to Load XML document"
        End If

    Else
        Wscript.Echo "File: Failed to Open"
    End If


    Wscript.Echo "... StructuralCalcs"
    Wscript.Echo "All Done!!"

End Sub




Sub cMain '(ByVal cmdArgs() )
    Dim startTime, endTime
    Dim anykey
    Dim fso, WshShell, objArgs
    Dim xmlDataPath
    Dim path1


    Wscript.Echo "----------------------------------------------------------------------"
    WScript.Echo "Main ..."
    Wscript.Echo "----------------------------------------------------------------------"
    startTime = now
    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
        'Do Something
        xmlDataPath = objArgs(0)
        WScript.Echo xmlDataPath
        path1 =  fso.GetAbsolutePathName(xmlDataPath)
        xmlResultsFileName = fso.GetParentFolderName(path1) & "\results2.xml"
        WScript.Echo fso.GetFileName(xmlResultsFileName)
        
        
        'Do Something with xml File
        call StructuralCalcs(xmlDataPath)
        
        'All Done
        '========
    Else
        WScript.Echo "Not enough parameters: provide data file name"
    End If
    
    
    
    
    endTime = now
    Wscript.Echo "======================================================================"
    Wscript.Echo "Duration: ",FormatDateTime(endTime - startTime, 4)
    WScript.Echo  "... Main"
    WScript.Echo "All Done!"
    Wscript.Echo "======================================================================"
    
End Sub

The main calculation script is basically unchanged, juts have different versions of xmlgrabparameter, and saveresults, along with an extra procedure saveVariable. The latter is because need to build a tree to hang the results on. The results are as shown in the following view from XMl Notepad 2007.

View of data file in XML NotePad 2007
View of data file in XML NotePad 2007

When using MSXML whilst I don’t have to write a parser to find tags and attributes, still need to write some kind of parser to get data out of the file structure and into useful variables. The files are simple to create and edit with a plain text editor, but not necessarily easy to write code to read and write the data to and from. But still the files are portable and useable just about every where, more informative than comma delimited and space delimited files, and a variety of software can read data from such files, though may need to map own data structure to the other softwares data structure. {On the other hand I wouldn’t want to write my section properties into such a file.}

Using MS Access Data Files

And speaking of section properties here is a script to read the section properties .mdb file:


'
' Copyright (c)2018 S C Harrison
' Refer to License.txt for terms and conditions of use.
'
Option Explicit

Dim MtrlDB
Dim dbs, tbl
    
Sub ConnectMtrlDB(MtrlDB, ByRef dbs, ByRef tbl)
    Const dbOpenDynaset = 2
    Dim dbe

    Set dbe = CreateObject("DAO.DBEngine.36")

    Set dbs = dbe.Workspaces(0).OpenDatabase(MtrlDB, , True)
    Set tbl = dbs.OpenRecordset("Sections", dbOpenDynaset)

End Sub
    
    
Sub DisplaySectProp(SectionName)
    Dim dbs
    Dim tbl, StrCriteria

    Call ConnectMtrlDB(MtrlDB, dbs, tbl)

    StrCriteria = "SECTION = " & """" & SectionName & """"

    WScript.Echo "Search Criteria: <" & StrCriteria & ">"
    tbl.FindFirst StrCriteria

    WScript.Echo "SECTION : ", Nz(tbl.Fields("section"),"")
    WScript.Echo "FABGROUP : ", Nz(tbl.Fields("Fabgroup"),"")
    WScript.Echo "TYPE1 : ", Nz(tbl.Fields("Type1"),"")
    WScript.Echo "SWT : ", Nz(tbl.Fields("swt"),0)
    WScript.Echo "D : ", Nz(tbl.Fields("d"),0)
    WScript.Echo "DF : ", Nz(tbl.Fields("df"),0)
    WScript.Echo "B_BTM : ", Nz(tbl.Fields("B_BTM"),0)
    WScript.Echo "B_TOP : ", Nz(tbl.Fields("B_TOP"),0)
    WScript.Echo "LD : ", Nz(tbl.Fields("LD"),0)
    WScript.Echo "TF : ", Nz(tbl.Fields("Tf"),0)
    WScript.Echo "TW : ", Nz(tbl.Fields("tw"),0)
    WScript.Echo "RAD : ", Nz(tbl.Fields("RAD"),0)
    WScript.Echo "AREA : ", Nz(tbl.Fields("Area"),0)
    WScript.Echo "IX : ", Nz(tbl.Fields("Ix"),0)
    WScript.Echo "ZX : ", Nz(tbl.Fields("Zx"),0)
    WScript.Echo "SX : ", Nz(tbl.Fields("Sx"),0)
    WScript.Echo "RX : ", Nz(tbl.Fields("rx"),0)
    WScript.Echo "X_BAR : ", Nz(tbl.Fields("X_BAR"),0)
    WScript.Echo "X_O : ", Nz(tbl.Fields("X_O"),0)
    WScript.Echo "IY : ", Nz(tbl.Fields("Iy"),0)
    WScript.Echo "ZY : ", Nz(tbl.Fields("Zy"),0)
    WScript.Echo "SY : ", Nz(tbl.Fields("Sy"),0)
    WScript.Echo "RY : ", Nz(tbl.Fields("ry"),0)
    WScript.Echo "Y_BAR : ", Nz(tbl.Fields("Y_BAR"),0)
    WScript.Echo "Y_O : ", Nz(tbl.Fields("Y_O"),0)
    WScript.Echo "J : ", Nz(tbl.Fields("j"),0)
    WScript.Echo "IW : ", Nz(tbl.Fields("Iw"),0)
    WScript.Echo "QF : ", Nz(tbl.Fields("Qf"),0)
    WScript.Echo "Beta_X : ", Nz(tbl.Fields("Beta_x"),0)
    WScript.Echo "Beta_Y : ", Nz(tbl.Fields("Beta_y"),0)
    WScript.Echo "C_Y : ", Nz(tbl.Fields("C_Y"),0)
    WScript.Echo "ZX_MIN : ", Nz(tbl.Fields("ZX_MIN"),0)
    WScript.Echo "ZY_MIN : ", Nz(tbl.Fields("ZY_MIN"),0)
    WScript.Echo "FY : ", Nz(tbl.Fields("Fy"),0)
    WScript.Echo "FYF : ", Nz(tbl.Fields("fyf"),0)
    WScript.Echo "FYW : ", Nz(tbl.Fields("fyw"),0)
    WScript.Echo "FU : ", Nz(tbl.Fields("fu"),0)
    WScript.Echo "ClosedSection : ", Nz(tbl.Fields("ClosedSection"),"")
    WScript.Echo "MANUFACTURER : ", Nz(tbl.Fields("MANUFACTURER"),"")
    WScript.Echo "DataSource : ", Nz(tbl.Fields("DataSource"),"")

    tbl.Close
    dbs.Close
    
End Sub
    
    

Sub cMain '(ByVal cmdArgs() )
    Dim startTime, endTime
    Dim anykey
    Dim fso, WshShell, objArgs
    Dim mdbDataPath
    Dim userDocPath



    Wscript.Echo "----------------------------------------------------------------------"
    WScript.Echo "Main ..."
    Wscript.Echo "----------------------------------------------------------------------"
    startTime = now
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set WshShell = CreateObject("WScript.Shell")
    userDocPath = WshShell.SpecialFolders("MyDocuments")
    Set objArgs = WScript.Arguments
    
    ' See if there are any arguments.
    If objArgs.Count = 0  Then
        'Do Something
        mdbDataPath = userDocPath & "\eCalcs\Library\Materials\struMtrl.mdb"
        MtrlDB = mdbDataPath
        WScript.Echo mdbDataPath
        
        'Do Something with mdb File
        call DisplaySectProp("C200-15")
        
        
        'All Done
        '========
    Else
        WScript.Echo "Not enough parameters: provide data file name"
    End If
    
    
    
    
    endTime = now
    Wscript.Echo "======================================================================"
    Wscript.Echo "Duration: ",FormatDateTime(endTime - startTime, 4)
    WScript.Echo  "... Main"
    WScript.Echo "All Done!"
    Wscript.Echo "======================================================================"
    
End Sub

The script connects to a MS Access file using DAO, there should be no need to have MS Access on the computer. The script provides a means of testing that can read such files without the hassles of setting up MS Excel/VBA tool/references to type libraries. The function NZ() is built into MS Access, and so need to write own version or alternative for use outside MS Access. Its purpose is to prevent null field values from crashing the script.

The script for the reference program depends on how the data is stored in MS Access tables. There are at least two possibilities:

  1. Use variable names (Cpe,qz etc…) as table field names.
  2. Have a table with the fields: Characteristic, Value, Units, isParameter

The following is a view of the possible tables in MS Access, with calculated results:

Structural Data Tables in MS Access
Structural Data Tables in MS Access

In the following scripts I am assuming that the mdb files already exist, that they were created in MS Access or by other means (It is possible using DAO, but not experimenting with that at the moment). The scripts for doing the calculations can be written in VBA and contained in MS Access, and are shown as modules in the above image. The task here however is to do the calculations away from MS Access, just use the data. The code inside MS Access i slightly simpler as can use the function CurrentDb to reference the database, whilst at least two lines of code are required outside Access. It isn’t the difference between VBA and VBScript, its the availability of functions built into MS Access.

In the image above can also see on the left hand side a reference to queries. Queries are a way of selecting data and modifying it, including carrying out calculations based on.

Structural Calculations using MS Access Query
Structural Calculations using MS Access Query

So using query by example (QBE), that’s the query form that is displayed, can use the data table StructuralCalcsV1 and then calculate the required results. There is no need to save them in a table as can regenerate the results from current data when needed. Also note that there are two records in the data table and therefore two results, one is with qz=0.96 and the other with qz=0.69. This tabular view of the query can be changed to show the structured query language (SQL) behind the form. The SQL is:


SELECT 
StructuralCalcsV1.Cpe, 
StructuralCalcsV1.qz, 
StructuralCalcsV1.s, 
StructuralCalcsV1.L, 
[Cpe]*[qz] AS pn, 
[pn]*[s] AS w, 
[w]*[L]^2/8 AS M
FROM StructuralCalcsV1;

This makes it easy to get SQL code for inclusion in VBScript and VBA. Such query allows the same calculation sequence using the same variables to be applied to multiple data records: no need to copy formula to additional rows as in MS Excel worksheet, no need for array indices as in high level programming languages. If have lots of civil surveying data (railway curves for example), then probably better to work with such data in MS Access than in MS Excel. Coordinate conversion for example, or old survey units to metric. Whilst likely to run out of rows in MS Excel, unlikely to hit the limit of records allowed in MS Access. For example if processing a DXF file through MS Excel will run out of rows.

So back to the reference calculation the script for using variable names as fields, we get:


Option Explicit

Sub StructuralCalcsV1(mdbDataPath)
    Const dbOpenDynaset = 2
    Dim dbe,db
    Dim StructuralData

    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]
    
    Set dbe = CreateObject("DAO.DBEngine.36")
    Set db = dbe.Workspaces(0).OpenDatabase(mdbDataPath, , True)
    Set StructuralData = db.OpenRecordset("StructuralCalcsV1", dbOpenDynaset)

    With StructuralData
        .MoveFirst
        Do
            Cpe = .Fields("Cpe").Value
            qz = .Fields("qz").Value
            s = .Fields("s").Value
            L = .Fields("L").Value

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

            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"
            WScript.Echo "------------------------------------"
            .MoveNext
        Loop Until .EOF
    End With
 
End Sub

Sub StructuralCalcsV2(mdbDataPath)
    Const dbOpenDynaset = 2
    Dim dbe,db
    Dim StructuralData
    Dim StructuralResults


    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]

    Set dbe = CreateObject("DAO.DBEngine.36")
    Set db = dbe.Workspaces(0).OpenDatabase(mdbDataPath, , True)
    db.Execute "DELETE StructuralResults.* FROM StructuralResults;"
    Set StructuralData = db.OpenRecordset("StructuralCalcsV1", dbOpenDynaset)
    Set StructuralResults = db.OpenRecordset("StructuralResults", dbOpenDynaset)

    With StructuralData
        .MoveFirst
        Do
            Cpe = .Fields("Cpe").Value
            qz = .Fields("qz").Value
            s = .Fields("s").Value
            L = .Fields("L").Value

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

            StructuralResults.AddNew
            StructuralResults.Fields("pn").Value = pn
            StructuralResults.Fields("w").Value = w
            StructuralResults.Fields("M").Value = M
            StructuralResults.Update

            .MoveNext
        Loop Until .EOF
    End With

    WScript.Echo "All Done!"
 
End Sub


Sub cMain '(ByVal cmdArgs() )
    Dim fso, WshShell, objArgs
    
    'General
    Dim mdbDataPath
    Dim userDocPath
    
    
    
    WScript.Echo "Main ..."
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set WshShell = CreateObject("WScript.Shell")
    userDocPath = WshShell.SpecialFolders("MyDocuments")
    Set objArgs = WScript.Arguments

    mdbDataPath = userDocPath & "\eCalcs\Library\Materials2\StructuralCalcs.mdb"

    StructuralCalcsV2 mdbDataPath

    WScript.Echo "... Main"
    WScript.Echo "All Done!"
    
End Sub

The main lines of code for connecting to the database are:

 Set dbe = CreateObject("DAO.DBEngine.36")
    Set db = dbe.Workspaces(0).OpenDatabase(mdbDataPath, , True)
    Set StructuralData = db.OpenRecordset("StructuralCalcsV1", dbOpenDynaset)

The first two lines connect to the database, the third line connects to a specific table in the database. The code then references the fields, assigns values to suitable variables and does the calculations. There are two subroutines one version does the calculations and prints the results the other stores the results in another table. Something else can then get the data from the two tables and produce a summary report, if a report is required, or it can do something else with the result data.

The alternative approach using field names to identify characteristics and properties, is the approach I typically take inside MS Excel, such that each parameter worksheet has the potential to be exported to an MS Access table. That way I can move between using MS Excel for worksheet calculations or MS Access for a product configurator independent of Excel. VB calculations are portable, whilst worksheet calculations are locked to a spreadsheet application, and become wasted in terms of increasing future productivity. The script is:


Option Explicit

Dim mdbDataPath

Sub setCharacteristic(characteristicName, characteristicValue)
    Const dbOpenDynaset = 2
    Dim dbe,db
    Dim tbl
    Dim StrCriteria

    Set dbe = CreateObject("DAO.DBEngine.36")
    Set db = dbe.Workspaces(0).OpenDatabase(mdbDataPath, , True)
    Set tbl = db.OpenRecordset("StructuralCalcsV3", dbOpenDynaset)

    StrCriteria = "Characteristic = " & """" & characteristicName & """"
    tbl.FindFirst StrCriteria
    If Not (tbl.NoMatch) Then
        tbl.Edit
        tbl.Fields("Value").Value = characteristicValue
        tbl.Update
    End If
    tbl.Close
    
End Sub



Function getCharacteristic(characteristicName)
    Const dbOpenDynaset = 2
    Dim dbe,db
    Dim tbl
    Dim StrCriteria

    Set dbe = CreateObject("DAO.DBEngine.36")
    Set db = dbe.Workspaces(0).OpenDatabase(mdbDataPath, , True)
    Set tbl = db.OpenRecordset("StructuralCalcsV3", dbOpenDynaset)

    StrCriteria = "Characteristic = " & """" & characteristicName & """"
    tbl.FindFirst StrCriteria
    If Not (tbl.NoMatch) Then
        getCharacteristic = Nz(tbl.Fields("Value").Value,"")
    Else
        getCharacteristic = ""
    End If
    tbl.Close
    
End Function


Sub StructuralCalcsV3()

    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]


    Cpe = getCharacteristic("Cpe")
    qz = getCharacteristic("qz")
    s = getCharacteristic("s")
    L = getCharacteristic("L")

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

    Call setCharacteristic("pn", CStr(pn))
    Call setCharacteristic("w", CStr(w))
    Call setCharacteristic("M", CStr(M))

    WScript.Echo "All Done!"

End Sub


Sub cMain '(ByVal cmdArgs() )
    Dim fso, WshShell, objArgs
    
    'General
    Dim userDocPath
    
    
    
    WScript.Echo "Main ..."
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set WshShell = CreateObject("WScript.Shell")
    userDocPath = WshShell.SpecialFolders("MyDocuments")
    Set objArgs = WScript.Arguments

    mdbDataPath = userDocPath & "\eCalcs\Library\Materials2\StructuralCalcs.mdb"

    StructuralCalcsV3

    WScript.Echo "... Main"
    WScript.Echo "All Done!"
    
End Sub

Connecting to the database remains the same, but this time have a function to get the value of the characteristic and a subroutine to set the value. It should be noted that this code only works for mdf files it won’t work for the .accdb files.  Whilst the code inside MS Access doesn’t change, it still uses CurrenDb, the code external to MS Access does change. Instead of using data access objects (DAO), instead have to use ActiveX Data Objects (ADO).

And there I hit a problem. The ADO code works inside MS Excel/VBA, it doesn’t work when I use it in VBScript. Using VBScript I get the following error (on Windows 10, 64 bit):

ADODB.Connection: Provider cannot be found. It may not be properly installed.

Most information sources I find indicate that it is because the drivers are 32 bit, and vbscript is 64 bit and calls 64 bit drivers. Maybe true, may be not. I typically get my command prompt from xyplorer, which turns out to be 32 bit, if I try to force the 64 bit version of cscript it doesn’t work, I get the 32 bit version. If I use Windows taskbar to run command prompt then I get 64 bit version if command prompt and can then force either 32 bit or 64 bit version of cscript. However it doesn’t fix the driver problem. I attempted to install the solution, but it stopped and indicated that I have a 32 bit version of office installed, and 32 bit drivers. I didn’t attempt to install 32 bit drivers, as the script works from Excel/VBA, and I didn’t want to mess up something which was otherwise working.

The VBScript does run on my netbook under Windows XP, with no office installed. So no error in minor conversion from VBA to VBScript, so the error is somewhere in the Windows 10, 64 bit system, and requires further research. The script is as follows, but it may not work on any given system:


Option Explicit

Dim mdbDataPath

Sub adoStructuralCalcsV1()
  Dim dbCon
  Dim StructuralData
  Dim connStr
  
  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]
  
  Set dbCon = CreateObject( "ADODB.Connection" )
  'dbCon.Provider = "Microsoft.Jet.OLEDB.4.0"
  'dbCon.Provider = "Microsoft.ACE.OLEDB.12.0"
  
  connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & mdbDataPath & ";"
  'connStr = "“Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & mdbDataPath & ";"
  WScript.Echo connStr
  dbCon.Open connStr
  

  set StructuralData=CreateObject("ADODB.recordset")
  StructuralData.Open "Select * From StructuralCalcsV1", dbCon

  With StructuralData
    .MoveFirst
    Do
      Cpe = .Fields("Cpe").Value
      qz = .Fields("qz").Value
      s = .Fields("s").Value
      L = .Fields("L").Value

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

      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"
      WScript.Echo "------------------------------------"
      .MoveNext
    Loop Until .EOF
  End With
 
  WScript.Echo "All Done!"
End Sub

Sub adoStructuralCalcsV2()
  Const adOpenDynamic=2
  Const adLockOptimistic=3
  Dim dbCon
  Dim StructuralData
  Dim StructuralResults
  
  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]
  
  Set dbCon = CreateObject( "ADODB.Connection" )
  'dbCon.Provider = "Microsoft.Jet.OLEDB.4.0"
  dbCon.Provider = "Microsoft.ACE.OLEDB.12.0"
  dbCon.Open mdbDataPath
  
  set StructuralData=CreateObject("ADODB.recordset")
  StructuralData.Open "Select * From StructuralCalcsV1", dbCon

  set StructuralResults=CreateObject("ADODB.recordset")
  StructuralResults.Open "Select * From StructuralResults", dbCon, adOpenDynamic, adLockOptimistic
  
  With StructuralData
    .MoveFirst
    Do
      Cpe = .Fields("Cpe").Value
      qz = .Fields("qz").Value
      s = .Fields("s").Value
      L = .Fields("L").Value
      
      pn = Cpe * qz         'kPa
      w = pn * s            'kN/m
      M = w * L ^ 2 / 8     'kNm
      
      StructuralResults.AddNew
      StructuralResults.Fields("pn").Value = pn
      StructuralResults.Fields("w").Value = w
      StructuralResults.Fields("M").Value = M
      StructuralResults.Update
      
      .MoveNext
    Loop Until .EOF
  End With
 
  WScript.Echo "All Done!"
 
End Sub

Sub cMain '(ByVal cmdArgs() )
    Dim fso, WshShell, objArgs
    
    'General
    Dim userDocPath
    
    WScript.Echo WScript.FullName
    
    WScript.Echo "Main ..."
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set WshShell = CreateObject("WScript.Shell")
    userDocPath = WshShell.SpecialFolders("MyDocuments")
    Set objArgs = WScript.Arguments

    mdbDataPath = userDocPath & "\eCalcs\Library\Materials2\StructuralCalcs.accdb"
    'mdbDataPath = "C:\Users2\AppDev\Materials2\StructuralCalcs.accdb"
    
    WScript.Echo mdbDataPath
    
    adoStructuralCalcsV1

    WScript.Echo "... Main"
    WScript.Echo "All Done!"
    
End Sub

The lines to achieve the connection are:

  Set dbCon = CreateObject( "ADODB.Connection" )
  'dbCon.Provider = "Microsoft.Jet.OLEDB.4.0"
  'dbCon.Provider = "Microsoft.ACE.OLEDB.12.0"

  connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & mdbDataPath & ";"
  WScript.Echo connStr
  dbCon.Open connStr

  set StructuralData=CreateObject("ADODB.recordset")
  StructuralData.Open "Select * From StructuralCalcsV1", dbCon

Using LibreOffice Base

If don’t have access to MS Access then the next alternative would be LibreOffice Base. I have written the reference calculations using LibreOffice Basic (LOB), and also tested using its stored queries. I haven’t yet tested with writing VBScript to control LO Base. The benefit of the script is that it can control, either MS Access or LO Base, or likewise LO Calc or MS Excel. The main calculation script remains unchanged but the functions/routines to get data from the office applications changes to suit. The change can be implemented by simply changing the library files referenced in the wsf file. So for example the function getCharacteristic would not change its name, it would just have two or more alternative variations to match the application it depends on., and change the wsf file includes to match the application available on the available computer. Shifting data from one application to another is easier than shifting and converting application code. I still need to experiment with LibreOffice and VBScript, for which purpose I may need an additional ODBC driver.

Anycase the saved queries in LO Base are not able to carry out the calculation sequence in one step, three queries are required. First calculate pn, use the result of this query to calculate w, then the result of this query to calculate M, thus somewhat less convenient than MS Access.

Using SQLITE Data Files

Another alternative to MS Access is to use sqlite and DB Browser for sqlite. Using DB Browser the data file and tables can be easily created along with queries.

Structural Calc tables as viewed in DB Browser for sqlite
Structural Calc tables as viewed in DB Browser for sqlite

Since using VBScript, the C libraries are not much use, instead we need a ODBC driver, such as that by available at http://www.ch-werner.de/sqliteodbc/. With this driver then connection to sqlite is similar to connecting to MS Access, only difference is it works on Windows 10, 64 bit.


Option Explicit

Const adOpenDynamic=2
Const adLockOptimistic=3

Dim dataPath

Sub TestSQLconnection
    Dim oCS
    Dim oCNCT
    Dim objRecordset
    Dim x
    Dim sql
    Dim i

    oCS = "Driver={SQLite3 ODBC Driver};Database=@FSPEC@;StepAPI=;Timeout="
    Set oCNCT = CreateObject( "ADODB.Connection" )

    oCS = Replace( oCS, "@FSPEC@", dataPath)
    oCNCT.Open oCS

    set objRecordset=CreateObject("ADODB.recordset")
    'objRecordset.Open "Select * from StructuralCalcsV1", oCNCT
    call objRecordset.Open("Select * from StructuralCalcsV1", oCNCT)

    ' i = 1
    ' Do until objRecordset.EOF
        ' WScript.Echo i
        ' for each x in objRecordset.fields
            ' WScript.Echo x.name, " = ", x.value
            'WScript.Echo x.value
        ' next
        ' objRecordset.MoveNext
        ' i = i +1
    ' Loop
    
    'objRecordset.AddNew
    



    WScript.Echo "All Done!"
End Sub

Sub StructuralCalcsV1()
    Dim oCS
    Dim oCNCT
    Dim objRecordset
    Dim sql
    
    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]
    
    oCS = "Driver={SQLite3 ODBC Driver};Database=@FSPEC@;StepAPI=;Timeout="
    Set oCNCT = CreateObject( "ADODB.Connection" )

    oCS = Replace( oCS, "@FSPEC@", dataPath)
    oCNCT.Open oCS

    set objRecordset=CreateObject("ADODB.recordset")
    objRecordset.Open "Select * from StructuralCalcsV1", oCNCT
    


    With objRecordset
        .MoveFirst
        Do
            Cpe = .Fields("Cpe").Value
            qz = .Fields("qz").Value
            s = .Fields("s").Value
            L = .Fields("L").Value

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

            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"
            WScript.Echo "------------------------------------"
            .MoveNext
        Loop Until .EOF
    End With

End Sub




Sub StructuralCalcsV2()
    Dim ConnectionStr
    Dim fn
    Dim dbCon
    Dim StructuralData
    Dim StructuralResults
    Dim sql

    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]

    ConnectionStr = "Driver={SQLite3 ODBC Driver};Database=@FSPEC@;StepAPI=;Timeout="
    fn = dataPath
    Set dbCon = CreateObject( "ADODB.Connection" )

    ConnectionStr = Replace( ConnectionStr, "@FSPEC@", fn )
    dbCon.Open ConnectionStr

    set StructuralData=CreateObject("ADODB.recordset")
    StructuralData.Open "Select * from StructuralCalcsV1", dbCon
    
    set StructuralResults=CreateObject("ADODB.recordset")
    StructuralResults.Open "Select * From StructuralResults", dbCon, adOpenDynamic, adLockOptimistic

    With StructuralData
        .MoveFirst
        Do
            Cpe = .Fields("Cpe").Value
            qz = .Fields("qz").Value
            s = .Fields("s").Value
            L = .Fields("L").Value

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

            StructuralResults.AddNew
            StructuralResults.Fields("pn").Value = pn
            StructuralResults.Fields("w").Value = w
            StructuralResults.Fields("M").Value = M
            StructuralResults.Update

            .MoveNext
        Loop Until .EOF
    End With

    WScript.Echo "All Done!"

End Sub


Sub setCharacteristic(characteristicName, characteristicValue)
  Dim dbCon
  Dim ConnectionStr
  
  Dim StructuralData
  Dim fn
   
  Dim StrCriteria
  
  ConnectionStr = "Driver={SQLite3 ODBC Driver};Database=@FSPEC@;StepAPI=;Timeout="
  fn = dataPath
  
  ConnectionStr = Replace(ConnectionStr, "@FSPEC@", fn)
  
  Set dbCon = CreateObject( "ADODB.Connection" )
  dbCon.Open ConnectionStr
  set StructuralData=CreateObject("ADODB.recordset")
  
  StrCriteria = "SELECT * FROM ""StructuralCalcsV3"" WHERE ""Characteristic""=" + "'" + characteristicName + "'"
  StructuralData.Open StrCriteria, dbCon, adOpenDynamic, adLockOptimistic

  If Not (StructuralData.BOF And StructuralData.EOF) Then
    StrCriteria = "UPDATE ""StructuralCalcsV3"""
    StrCriteria = StrCriteria + " SET ""Value""=" + "'" + characteristicValue + "'"
    StrCriteria = StrCriteria + " WHERE ""Characteristic""=" + "'" + characteristicName + "'"
    
    dbCon.Execute StrCriteria
   
  End If
  StructuralData.Close
End Sub



Function getCharacteristic(characteristicName)
  Dim dbCon
  Dim ConnectionStr
  
  Dim StructuralData
  Dim fn
   
  Dim StrCriteria
  
  ConnectionStr = "Driver={SQLite3 ODBC Driver};Database=@FSPEC@;StepAPI=;Timeout="
  fn = dataPath
  
  ConnectionStr = Replace(ConnectionStr, "@FSPEC@", fn)
  
  Set dbCon = CreateObject( "ADODB.Connection" )
  dbCon.Open ConnectionStr
  set StructuralData=CreateObject("ADODB.recordset")
  
  
  StrCriteria = "SELECT * FROM StructuralCalcsV3 WHERE Characteristic=" + "'" + characteristicName + "'"
  StructuralData.Open StrCriteria, dbCon
  
  If Not (StructuralData.BOF And StructuralData.EOF) Then
    StructuralData.MoveFirst
    getCharacteristic = StructuralData.Fields("Value").Value
  Else
    getCharacteristic = ""
  End If
  StructuralData.Close
  

End Function


Sub StructuralCalcsV3()
  Dim db
  Dim StructuralData
  
  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]
  
  Cpe = CDbl(getCharacteristic("Cpe"))
  qz = CDbl(getCharacteristic("qz"))
  s = CDbl(getCharacteristic("s"))
  L = CDbl(getCharacteristic("L"))
  
  pn = Cpe * qz         'kPa
  w = pn * s            'kN/m
  M = w * L ^ 2 / 8     'kNm

  Call setCharacteristic("pn", CStr(pn))
  Call setCharacteristic("w", CStr(w))
  Call setCharacteristic("M", CStr(M))

  WScript.Echo "All Done!"
 
End Sub


Sub cMain '(ByVal cmdArgs() )
    Dim fso, WshShell, objArgs
    
    'General
    Dim userDocPath
    
    WScript.Echo WScript.FullName
    
    WScript.Echo "Main ..."
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set WshShell = CreateObject("WScript.Shell")
    userDocPath = WshShell.SpecialFolders("MyDocuments")
    Set objArgs = WScript.Arguments

    dataPath = userDocPath & "\eCalcs\Library\Materials2\StructuralCalcs.sqlite"
    
    WScript.Echo dataPath
    
    'TestSQLconnection
    'StructuralCalcsV1
    'StructuralCalcsV2
    StructuralCalcsV3

    WScript.Echo "... Main"
    WScript.Echo "All Done!"
    
End Sub

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

cMain

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

I had some problems finding out what code to write. Most sample code made reference to columns rather than fields and also used numbers to reference the columns. Such approach isn’t very helpful for a DBMS, and makes it about as inconvenient as MS Excel VLOOKUP. However, I shifted from VBScript to Excel/VBA, and used the object browser and intellisense to identify other properties and methods of the connection object, initially testing the ADO connection to MS Access. Once I got that to work I then moved onto using the sqlite driver, got that to work in VBA, then converted to VBScript, and still worked. On that basis I figured that the MS Access version would be a breeze to convert from VBA to VBScript, but as noted above it wasn’t.

Note that there is more SQL code used than with MS Access, the code can be worked out and tested using DB Browser. The use of sqlite as a data file format seems reasonable and more preferable than using xml, however there maybe  a problem with respect to continued availability of an ODBC driver. On the other hand can use the command line version of sqlite, and pass it command line parameters, and otherwise pipe output to a file, and otherwise process such file: but that’s more programming effort. At the moment however the driver is available and works.

Using MySQL

I haven’t attempted to write scripts for MySQL. I used MySQL workbench to set up tables, and tested the saved queries (views). As with LO Base (HSQLDB), and sqlite, the sequence of calculations need to be done in stages, they cannot be done in one hit as with MS Access.

The other test I did with MySQL, was to use the WAMP installation, and use phpMyAdmin to set up tables in MySQL, then wrote a version of the calculation script in php. A relatively simple exercise.

MySQL seems something of an overkill as a place to store calculation data, however if have multiple users accessing the data via the internet, then it may be the suitable DBMS for the task. In such situation unlikely to be using VBScript for application programming, so at this stage I haven’t experimented with the ODBC driver for MySQL, but I don’t expect its usage will be much different than that for sqlite.

Conclusion

Databases such as MS Access make accessing data easier to find and modify than xml files. Drivers for xml, MS Access and other common databases typically come with an MS Windows installation,  drivers for other DBMS can readily be found and downloaded from the internet.

Using a DBMS simplifies reading and writing files compared to writing own file structure and parser to read such file. A DBMS can store more records than an MS Excel worksheet. Tabular calculations can be done faster and more robustly using queries, compared to using copied row calculations in MS Excel.

Furthermore such data files can be accessed without the applications needed to create them, a lot can therefore be done with a Windows computer out of the box, just using VBScript and available COM or .net automation objects.

The only thing that VBScript is lacking is an immediate interpretive mode such as available in the VBA integrated development environment (IDE), and as otherwise provided by MatLAB, Octave, SciLAB, FreeMAT and python.

Also whilst I may prefer the more compact syntax of JScript, it is lacking some of the built in functions available to VBScript. Also at this point in time I cannot write user defined functions in JScript for use in MS Excel worksheets: and so moving between JScript and VBA would be more effort than moving between VBScript and VBA.

Also given problems had with connecting to .accdb, will stick with .mdb files. So for time being will be using MS Access (.mdb) files for storing data outside of MS Excel. Using MS Access tables is also tidier than using lookup ranges scattered here and there across worksheets in an MS Excel workbook.

Having got data tables, the next step is using record data structures, in VBA these can be user defined types using “TYPE … END TYPE” statements, or using class modules. In VBScript, “TYPE…END TYPE” is not supported but classes are via “CLASS…END CLASS” statement, unlike VBA these class definitions don’t need to be in separate modules or files: multiple classes can be defined in one file. Record types and classes are important for moving/passing blocks of data around inside a program.

In one of the scripts above I moved the variables outside the subroutine to make them global so I didn’t have to pass a long list of variables to each subroutine, with an appropriate record/class only need to pass one variable, and that one variable provides access to all the other variables. The class/record can have the same fields as the database tables, so there is some commonality.

Unfortunately Excel worksheet functions cannot pass data around in records/classes, the closest we get to passing a block of data is using a range, or otherwise an index into some more complex data structure. For example the section name provides an index into the section property table: one item provides access to all the other properties significantly simplifying the use of functions, as don’t need to pass all the section properties variable by variable.


Revision:

  1. [28/11/2018]: Original