Spreadsheets and Databases: Excel

Showing posts with label Excel. Show all posts

Wednesday, 14 January 2015

Import XML Data to MS Excel

Simple Excel Application which reads the XML file created by earlier application. The data is read into the workbook, and written to a single worksheet. It is also separated into worksheets for parameters, variables, lists and tables. Lists are written across a row. Named ranges are also created for Parameters and Variables.

It doesn't use any XML library to read the file, instead it uses a limited parser written in vba.

Its a simple experimental application showing possibility of exporting and importing parameters to simple XML files, to share data between spreadsheet applications.

Normally I try to create spreadsheets with all parameters on a single worksheet, formatted to be compatible with export to DBase, Paradox and MS Access tables, such worksheet could also be directly exported to XML. However as workbooks grow, not all parameters get referenced on the summary sheet. The two XML workbooks help clean the workbooks up.

Spreadsheet can be down loaded: importXML.xls

Revisions:
[14/01/2015] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

Export MS Excel named Ranges to XML

A simple MS Excel application which allows opening another MS Excel workbook and then saves all the named ranges to an XML file. Whilst Excel permits saving workbook as an XML file, or exporting data to an XML file, such is of little use for data randomly scattered throughout the workbook.

The application exports the data tagged as parameters, variables, lists and tables. If a named range is a single cell and unprotected then it tags it as a parameter. If the single cell is not unprotected then it is tagged as a variable. A named range which consists of a single row or column of data is tagged a list, whilst a named range comprising of rows and columns is tagged a table.

It doesn't use any XML library, it simply writes the data to a simple text file with appropriate tags. Also it uses FileSystem objects to write to the file, as the standard vba print statements do not recognise unicode characters, where as Excel cells can contain such.

Spreadsheet can be down loaded: exportNamedToXML.xls

Revisions:
[14/01/2015] Original
[23/04/2016] Changed download links to MiScion Pty Ltd Web Store

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

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

Pages

Wednesday, 14 January 2015

Import XML Data to MS Excel

Export MS Excel named Ranges to XML

Monday, 23 June 2014

Chrome Bookmarks and Blogger Posts

Thursday, 19 June 2014

Experiments with DAO in MS Excel

Bill of Materials and Equivalent Binary Tree

Tuesday, 17 June 2014

VBA Experiments with Class Objects

Collection of Short Excel/vba macros to assist learning vba

Structural Materials Data for Timber Design to AS1720

Structural Materials Data

Cold-formed Steel: Calculation of Effective Section Modulus for C-Section

Experiments using Excel XY Charts for Parametric Sketching

Using MS Excel Worksheets to Generate Acad LT Scripts

Draw Cold-formed Steel Sections Using Excel and Acad LT

Cold-formed Steel Member Checks and Design to AS4600