What is the best strategy for writing my collections and objects to disk and reimporting them later?

Question

I have a number of collections and objects set up that have a one-to-many relationship that is four levels deep. CollectionA contains a number of CollectionB objects and CollectionB contains a number of CollectionC objects and CollectionC contains a number of Content objects.

After the user has spent time populating all of these collections and objects with the necessary data, they need to be able to save all of the properties of each object and then read the output file and start where they left off.

I know this is a somewhat broad question so I'm looking to see which direction I should head in my research on how to do this. Right now I'm most concerned with the File I/O and I'm thinking of two different routes.

It's an excel project, so I was thinking I could loop through all of the Collections and objects from the top down and write their values to a spreadsheet and then loop through the spreadsheet rows and output those to a delimited text file. Then do the reverse to read the text file back in - from text to spreadsheet, then from spreadsheet into collections and objects.

Maybe the other option would be to bypass the spreadsheet and use a TextStream? Would that be possible and would it be the way to go if I'm planning on converting this VBA application to standalone VB.NET sometime in the future?

Answer 1

One good option is to store your collections as XML files. It will be relatively easy to add or read individual elements afterwards; and you'll save yourself from the indexing nightmare that will inevitably occur if you try to arrange your 4+ dimensional data onto a two-dimensional spreadsheet. They're text files, so human readable; if file size becomes a problem, just zip them.

You'll just have to devise an appropriate format, i.e. tag names etc., see example at the end of this answer.

EDIT: If having the data packaged into your Excel workbook is really important to you, then you can just slap the xml code onto one of the sheets and store it there... I've done this before.

Create an XML document as shown below:

Set xmlFatAssCollectionA = CreateObject("Msxml2.DOMDocument.6.0")

Then edit it: loop through your collections, appending each element as a child of the larger collection, as appropriate. When done, save the XML document.

xmlFatAssCollectionA.save("C:\MyDir\MyCollection.xml")

Documentation: http://msdn.microsoft.com/en-us/library/ms756987%28v=VS.85%29.aspx

You can use XPath to access the required nodes/elements. XPath tutorial

Here's what your resulting xml file might look like. I only added one collectionB object, and omitted the whole collectionA layer, but you get the idea.

<?xml version="1.0" encoding="ISO-8859-1"?>

<bObject>
  <cObject>
    <contentObject>
      <title lang="da">Kejserens nye klæder</title>
      <author>H.C. Andersen</author>
      <price>priceless</price>
    </contentObject>
  </cObject>
  <cObject>
     <contentObject>
      <title lang="en">Harry Potter</title>
      <author>J K. Rowling</author>
      <year>2005</year>
      <price>29.99</price>
    </contentObject>
    <contentObject>
      <title lang="fr">Les Schtroumpfs</title>
      <author>Some person</author>
      <year>1985</year>
    </contentObject>
  </cObject>
</bObject>