What is RAM Data Access

Document Sample
What is RAM Data Access Powered By Docstoc
					What is RAM DataAccess
The purpose of RAM DataAccess (RAM DA) is to allow you to read, add, and delete data in the
RAM Structural System (RAM SS) database. This is accomplished through the use of the COM
component, RAMDataAccess.dll. This file installs with RAM SS and its information is
registered on your computer. A Microsoft COM (Component Object Model) component aids in
software communication. The RAMDataAccess.dll component contains functions and a way for
an application to call the functions (interfaces). The great thing about COM is that the
programming language you use for your applications does not need to be the same programming
language that the component was written in. Hence, we can use DA with VBA, C++, or any
other programming language that supports COM.

Installing RAM DA
As stated previously, the RAMDataAccess.dll file installs with RAM SS. The version of the .dll
file is specific to the version of RAM SS that is installed. Therefore, in order to use RAM DA,
you must have RAM SS installed on the computer and the model that is being accessed must be
the same version. It is good practice to open the model in RAM SS and run all of the various
modules. This ensures the model will be converted to the version that is installed on the
computer and that all results are available through RAM DA.

Setting up Your VBA Project in Excel
If you create a new Excel workbook, there are a few things that you need to setup so that DA can
run in the application.

   1. Make sure the ‘Developer’ tab is visible. If it is not shown, click on the Office button in
      the upper left hand corner of Excel. Click on the ‘Excel Options’ button and select the
      ‘Popular’options. Then, select ‘Display Developer Tab in the Ribbon’ and click OK.

   2. Update your security settings. Open Excel and go to Developer – Macro Security. In the
      ‘Trusted Locations’ review/add the appropriate path of where the Excel file is or will be
      saved. This is critical or the macros will be probably be disabled when the file is opened.
      Under ‘Macro Settings’, select ‘Trust access to the VBA project object model.’ Please
      note that this is an Excel setting and is not a setting tied to the file. If you are running the
      DA program on another computer, that computer must have acceptable security settings
      as well.
   3. When you are in the Visual
      Basic Editor, you need to add
      references to DA library. Open
      Excel and go to Developer –
      Visual Basic. Once you are in
      the editor, go to Tools –
      References and select
      ‘RAMDataAccess 2.0 Type
      Library’ from the ‘References –
      VBAProject’ dialog box.
      Adding this reference to your
      application creates a library of
      the functions and constants that
      are available in RAM DA. If
      you hit F2 on your keyboard, the Visual Basic Editor will bring up your libraries.

Creating a Procedure in a New Module
Within the Visual Basic Editor, create a new module in
the VBA Project by going to Insert – Module. By
creating a new module in our project, we will be able to
execute our program from Excel. Next, create a Sub
Procedure in the module, by going to Insert – Procedure.
A Sub Procedure is a named sequence of statements that
will be executed as a unit. We would use a Function
Procedure if we wanted to return a value. The scope of
the procedure (Public or Private) defines the accessibility.
For a simple program like we are creating, it is not
necessary to define the scope of the procedure. Once the
module and procedure have been created, you can enter
your code.

Understanding Interfaces
Interfaces are COM objects that allow you to access a group of related methods for an entity. A
method is a routine that gets or sets information in the interface. When DataAccess was
originally created, its main purpose was to retrieve information from the Structural System
database. These interfaces are referred to as the “Original” or “Old” DA interfaces. These
interfaces have broad groups of methods for information like model data, structure geometry,
and member forces. This structure does not work well when the data needs to be modified. So, a
new set of interfaces referred to as the “Object Model” or “New” interfaces were created. These
interfaces group methods for specific Structural System entities like a floor type, beam, or deck.
There’s no reason why you couldn’t create an entire program using only the old interfaces if you
were only interested in getting information from the database. However, it is encouraged that
you use the new object model interfaces as much as possible because the old interfaces will not
be updated and eventually may be replaced.

You create an instance of DA by defining an object variable and setting a new reference to the
main DA interface (RAMDataAccess1) in the COM object.

       Dim RAMDataAcc1 As RAMDATAACCESSLib.RamDataAccess1
       Set RAMDataAcc1 = New RAMDATAACCESSLib.RamDataAccess1

Now, with the main interface established, we can access the next interface, either an old DA
interface or the main object model interface.

Using the Old DA Interfaces
Using the old interfaces is fairly straight forward. First, you define an object variable. Then,
you set the object variable equal to an interface pointer from the main DA interface

       Set RAMDataAccIDBIO = RAMDataAcc1.GetDispInterfacePointerByEnum(IDBIO1_INT)

Now, with the interface established, you can access its methods and data. The above interface,
RAMDataAccIDBIO, contains several methods (functions and sub routines) needed to open,
close, save, create, etc. a RAM SS database. For example, if we now want to load a RAM SS
database, we could call the LoadDataBase method from the interface.

       RAMDataAccIDBIO.LoadDataBase OpenFile

The LoadDataBase method expects a string variable (OpenFile) that represents the database
name. All of the other old interfaces have a similar structure to that described above. Data is
always returned by calling methods from an interface.

Using the New Object Model Interfaces
The object model interfaces are structured similarly to the structure in RAM SS. For example, to
model a beam in RAM SS, you first need be in a model and have a floor type defined. Then you
can go to Layout – Beams and add a beam. The structure of the object model interfaces are
similar in that it is a “layered” process. First, you need to reference the main object model
interface, then the floor type interface, and then the beams interface, before you can add a new
beam to the collection of beams. Data that you want to retrieve from the database is typically
stored as a property of the object in the object model interfaces. This differs from the old
interfaces in that you don’t directly call a method to retrieve a particular piece of information.
For example, if you wanted to know the size of a particular beam using the old DA interfaces,
you would call the GetMemberSize method from the IMemberData1 interface, where the unique
member ID number (lMemberID) is supplied to the function and the size of the member
(pbstrSize) is returned.
       Dim IMemberData1 As Object
       Set IMemberData1 = RAMDataAcc1.GetDispInterfacePointerByEnum(IMemberData_INT)
       IMemberData1.GetMemberSize lMemberID, pbstrSize

Using the object model interfaces, a reference to a particular beam object in the IBeam interface
would be set from the main object model interface by calling the GetBeam method for a unique
member ID (lMemberID). Then, you can assign the value of the strSectionLabel property to a
variable that you defined (strSize).

       Dim IModel as RAMDATAACCESSLib.IModel
       Set IModel = RAMDataAcc1.GetDispInterfacePointerByEnum(IModel_INT)
       Dim IBeam as RAMDATAACCESSLib.IBeam
       Set IBeam = IModel.GetBeam(lMemberID)
       strSize = IBeam.strSectionLabel

A Practical Example
You may or may not know that within RAM SS you have the ability to customize the master
steel table and the column/beam design tables. One use for manipulating the tables is to reduce
the number of steel sections that are selected from during the optimization process to gain
economy through repetition. With RAM DA, you could create a program to “find and replace”
particular sizes that are assigned in the model. Instead of using the beam/column design tables to
pare down the sections available during and optimization, it might be easier to have an extensive
list of available shapes to see which sizes occur frequently during the optimization and then
replace the uncommon shapes with a heavier size that is used more often in the model. The
following program can be used to replace steel shapes in a model for beams, columns, horizontal
braces, and/or vertical braces.

Microsoft Excel is used as an input table for the Visual Basic for Applications program. The
four check boxes on the left allow the user to select which members types should be changed.
Once the “Find and Replace” button is clicked, the program will ask the user to open a RAM SS
model and all instances for the desired member type with the assigned size in the “Find” cell will
be replaced with the size in the “Replace With” cell.
The Code

A module and sub procedure were created as described previously. The declaration section
exists at the module level outside of our sub procedure. Although it is not necessary to have a
declaration section, it can be useful to define variables that appear in multiple procedures within
the module. If you include the statement “Option Explicit” in the declaration sections, then all
variables must be defined before you use them in a procedure. It is a great way to ensure that
you have not misspelled a variable name. Prior to creating the module, if you had toggled the
“Require Variable Declaration” from Tool – Options, the “Option Explicit” statement would
have automatically appeared in the declarations section.

                                                             Since we have to define every
                                                            variable in our procedure, it is not a
                                                            bad idea to define all of your variables
                                                            right away in groups based on the data
                                                            type. A data type is the characteristic
                                                            of a variable that determines what
                                                            kind data of it can hold. There are
                                                            several data types such as object, long,
                                                            double, and string. The “Dim”
                                                            statement is used to declare a variable
as a particular data type and allocate storage space. By defining our interface objects (i.e.
IModel) with specific references to RAMDATAACCESSLib instead of a generic “Object”, we
force early binding. The main advantage in declaring a variable this way is that when you write
code to access methods or properties of the interfaces, you’ll be able to see what
methods/properties are available for the interface. For example, once the period is typed after
IVerticalBrace, the menu pops up and the appropriate property can be selected. Had I simply
defined IVerticalBrace as an “Object” instead of a specific reference to
RAMDATAACCESSLib.IVerticalBrace, a menu would not have popped up.
After the variables have been declared, we start to get into the meat of our code. This portion of
the code clears old data in the Excel worksheet, sets variables equal to the values (true is toggled)
of the worksheet check boxes, and saves the worksheet find and replace shapes as strings.

Next, we can open our RAM SS model. Prior to opening the model in DA, you will want your
Structural System modules to be analyzed and/or designed so that all of the appropriate member
data and forces are available. Also, you need to be out of the model when accessing it through
DA. Although the model path can be hard coded, it is convenient to use the VBA
GetOpenFilename function to select the model through a graphical user interface. This function
will return a string for the file name that includes the path. Then, we can initialize DA and load
the database through the IDBIO interface. Finally, we initialize the main object model interface.

In this program, the user inputs the find and replace steel shape name. As a precaution, it is
probably a good idea to verify that the sections exist in the model master steel table. To
accomplish this, we will need to locate the master steel table and read data from it. First, we use
the old DA interface “IModelData1” to retrieve the Tables directory path. A “1” was entered for
the other returned variables because we are not interested in their value. That way, we don’t
need to define a variable that we are not going to use. Then, we use the new model interface
“ISteelCriteria” to get at the name of the master steel table. The string “mastersteeltable” is then
set equal to the path plus the file name. VBA has defined objects can be used to read from a text
file. First, we use the Create Object function to return a FileSystemObject. Then, the
OpenTextFile method is used to create our master steel table as a TextStream object. Finally, the
code loops through each line of the TextStream and reads in characters until it finds a space. The
master steel table is structured that the shape name is the first set of characters on each line.
Therefore, by creating a string for the characters before the space, we can compare the value to
our input value to see if we have a match. If no match is found, a message box will appear
stating that the shape is invalid, the database will be closed, and the program will terminate. This
routine will run for both the “Find” and “Replace” sections.

The intent of this program was to find and replace all steel shapes for the desired member
type(s). Therefore, it is necessary to look at every applicable member on each story and see if
                                          the size matches the “Find” size. First, we determine
                                          the number of stories in the model so we can loop
                                          through each story. The “GetStories” method of the
                                          IModel interface returns a pointer to an IStories
                                          collection interface that represents all of the stories in
the model. A collection is simply a group of objects and a collection interface is simply a way to
pass around the collection. The IStories collection interface has a property “GetCount” that is
equal to the number stories in the model. Once we set a variable equal to this property, we can
then create a “For” loop that steps through each story. It is important to understand that the
stories are numbered starting with zero. Therefore, our loop should run from zero to the total
number of stories minus 1.
The IStory interface represents a single story in the model. To get at the particular story we are
interested in, we can use the “GetAt” method of the IStories collection interface. This method
takes an index number and returns an interface for the story represented by that index. Then, we
                                                         can get at the properties and methods for
                                                         our specific story. For example, the
                                                         “strLabel” property returns the story label
                                                         and the “GetBeams” method returns a
                                                         pointer to an IBeams collection interface.
                                                         The number of objects in the collection
can then be reduced by using the “Filter” function in the IBeams collection interface. Currently,
you can filter for material or frame type. In this instance, the function is used to eliminate beams
in the collection that are not a steel material type. Similarly, the columns, horizontal braces, and
vertical braces collection interfaces can all be returned from the IStory interface and filtered for
steel material types.

Now that we have the filtered collections, we can look at each member, see if the size matches
our “Find” criteria, and then change the size to match our “Replace” criteria. The block of code
nested in the “If” statement will only be performed if the “Beam” was toggled in our input
spread sheet. As with the IStories collection interface, the number of beams in the IBeams
collection interface can be determined from the GetCount property. Then, we can look at each
                                                       beam one by one in a “For” loop. With the
                                                       IBeam interface set to a particular beam,
                                                       we can retrieve the section label and
                                                       member number. If the section label
                                                       property matches the “Find” size, it will be
                                                       updated to match the “Replace” size and
                                                       the eAnalyze flag is set. The eAnalyze flag
                                                       is analogous to freezing the design in RAM
                                                       Steel Beam. Then, the story label, member
                                                       type, and member number, are stored in the
                                                       appropriate cells in the output table.
                                                       Although this is not necessary, it may be
                                                       desired for record keeping. A similar
                                                       process would take place for columns,
horizontal braces, and vertical braces. Then, the program will go to the next story and repeat the
whole process.

Finally, once the all of the changes have been made, the database can be saved and closed. By
setting RAMDataAcc1 equal to “nothing”, the object reference is released from the memory.
                                    The End Sub statement signals the end of the routine.

Shared By: