Spreadsheets and Databases: June 2014

Monday, 23 June 2014

Chrome Bookmarks and Blogger Posts

Chrome Bookmarks
An Excel workbook to extract Bookmarks from the Google Chrome export file and import into MS Excel, once into Excel it is relatively simple to import into MS Access, where additional tags and grouping can be added to make it easier to manage and to find appropriate bookmarks.

Blogger Posts
A macro which will break apart the exported Blogger xml file, and create one html file for each post, and a single index page to all the files. Additionally keywords and titles are summarised in Excel worksheet.

Spreadsheet can be down loaded: schGoogleToolBox.xls

Revisions:
[23/06/2014] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

Thursday, 19 June 2014

Experiments with DAO in MS Excel

Workbook showing button to run main application

A simple application to open a MS Access mdb file to:

List the contained tables
List the fields of a selected table
Import the data into the workbook

The workbook contains macros and has a vba reference to Microsoft DAO 3.6 Object Library. My file is set up for Office 2003 on a 64 bit Windows 7 machine. It maybe necessary to change the reference if it cannot be found.

Main Dialogue box

Running the main application provides dialogue box with buttons to select the available options.

Open db shows a file dialogue box to select an mdb file to open. Sample mdb file can be found with my materials library. Once a file has been selected a dialogue box displays showing the tables inside the data base file.

A single table can then be selected. Then the fields belonging to that table can be displayed or the data in the table can be imported into the workbook. The data is not linked, it is copied into the workbook.

Tables found inside the mdf

As such its not necessary to do this with Excel as it is able to import data directly from a MS Access table. However using linked data can be a problem if the data file moves, as it can be near impossible to update the links, and the spreadsheet typically needs to be re-built from scratch. Though newer versions of MS Excel this is apparently easier to do.

Any case there are benefits to using MS Access to store data and retrieve using DAO. For example retrieving section properties more directly by name rather than by column number. Though it is possible to write complex formula and use Vlookup to get properties by name. (eg. get section properties like Ixx, Iyy). This is the approach used by my AS4600 cold-formed steel design spreadsheet. Using vlookup tables is fine if just dealing with a single section and single segment, but when dealing with an entire building with many elements then DAO is the simpler more efficient option. {Or some alternative data base.}

Fields belonging to the selected table

The worksheet can be found here: dbExperiments.xls

Other Approach to Viewing mdb Files in Office without MS Access
For a better view and querying of MS Access files look in the Microsoft office folder and find the main executable file for MS Query (MSQRY32.EXE for Office 2003). This uses .dqy files which are simple text files which can be edited with notepad or other plain text editor.

MS Query

ODBC Data Source Administrator

Revisions:
[19/06/2014] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

Experiments with XML Data files and Treeviews

Experimenting with XML files as format for storing configuration parameters and main data for applications. Using MSXML and XPath seemed difficult to directly get at the data, lot easier to extra attributes and their values than data records. So figured as a basic starting point see if could traverse the whole tree and display the data as an indented list in an Excel worksheet and also in a treeview form. I believe that XQuery is the mechanism to get data, but not sure how that relates to MSXML.

The main application is a subroutine called MainApplication. It contains various calls to subroutines which are either commented out or active. It is set to allow the user to select an XML file and open it and display in a treeview component. Sample data can be obtained from my barrier design application just extract the xml file from the zip file.

The subroutine: xmlDislayIndentedList will display the data in the worksheet and also display some information about the type of xml node. It also highlights with a green background the final leaf node containing the text data.

The worksheet can be obtained here: xmlExperiments.xls

Revisions:
[19/06/2014] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

Bill of Materials and Equivalent Binary Tree

A simple spreadsheet using binary trees to build a bill of materials. Two displays are included:

A simple indented list placed in the Excel worksheet
An expandable/collapsible treeview component on a form.

The two main subroutines are:

mainAppBuildTree (builds a tree and displays in worksheet as indented list)
mainAppViewTree (builds a tree and displays in treeview component)

Data is taken from the worksheet. Two tables are required one listing all items and providing a unique key code for. Another list providing assembly definitions as single level bills of materials. These single level bills of materials are then exploded to build the entire product structure tree. To do this stacks are used.

The spreadsheet can be downloaded here: treeExperiments2014.xls

Other Experiments with Bill's of materials can be found on my ExcelCalcs profile:

Actually revisited trees so could display an XML data file in a tree.

Revisions:
[19/06/2014] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

British Iron and Steel Federation(1967),The Steel Designers Manual (3rd ED), Crosby Lockwood and Son Ltd.
Owens, G.W and Knowles P.R(1996), Steel Designers Manual , 5th edition, The Steel Construction Institute, Blackwell Science

{for current edition check:

Steel Construction Institute shop

Amazon.com

Wiley

}

NB: The newer editions have fewer rigid frame formulae and design charts than the older versions, but do cover common frames for which may not want to waste time using structural analysis software.

Taken from the ExcelCalcs.com website.

Related Posts:

Bundle of my ExcelCalcs UpLoads

The bundle can be downloaded from MiScion Pty Ltd : Spreadsheet Bundle

Pages

Monday, 23 June 2014

Thursday, 19 June 2014

Tuesday, 17 June 2014