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
Use of spreadsheets, databases and high level programming languages in industrial, manufacturing, mechanical and structural engineering
Showing posts with label xml. Show all posts
Showing posts with label xml. Show all posts
Wednesday, 14 January 2015
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
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
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 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
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
Subscribe to:
Posts (Atom)