Creating XML-files & Reading XML-files

  • In this example I will show how we can:
    * Create an XML-file from a named range in the active workbook.
    * Open and read an XML-file.


    Originally the post is based on a thread created by Stanl and I thanks Stan for pointing out the OLE DB Provider MSPersist:
    http://ozgrid.com/forum/showthread.php?t=27756


    What we need:
    * MS Windows 2000 and later
    * MS Excel 2000 and later
    * MDAC 2.5 or above (ADO Library)


    Create an XML-file:
    Step 1

    Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.


    Step 2
    Insert the following code in a standardmodule:
    [vba]
    Option Explicit


    Sub Create_XML_Recordset()
    Dim rst As ADODB.Recordset
    Dim str As ADODB.Stream
    Dim stCon As String


    'A named range is used in the example.
    Const stSQL As String = "SELECT * FROM [Report]"


    'The connection string.
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes"";"

    'Instantiate the ADODB's objects.
    Set rst = New ADODB.Recordset
    Set str = New ADODB.Stream


    With rst
    .CursorLocation = adUseClient
    .Open stSQL, stCon, adOpenStatic, adLockReadOnly, adCmdText
    'Save the recordset into the stream.
    .Save str, adPersistXML
    .Close
    With str
    .SaveToFile "C:\Report.xml", adSaveCreateOverWrite
    .Close
    End With
    End With


    'Release object from memory.
    Set str = Nothing
    Set rst = Nothing


    End Sub
    [/vba]


    A stream-object is nothing then an in-memory "container" which per se only contains bytes and the advantage is that we don't create any unnessary overhead as we don't use the disc to store it in.


    The created XML-file contains a schema based on rowset only - see the following snippet XML-code:


    [vba]
    xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    - <s:Schema id="RowsetSchema">
    [/vba]


    This mean that Excel can't read the contents in a correct way unless we create an XSLT-file enabling Excel to interpretate the data in a correct way (only for the versions 2002 (XP) and 2003)


    However, personally I find it both complicated and tidious to create XSLT-files (and it only works for 2002 (XP) and later) so instead we can use a workaround to import XML-data into Excel:


    Open an XML-file:
    Step 1

    Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.


    Step 2
    Insert the following code in a standardmodule:
    [vba]
    Sub Read_XML_Data_1()
    Dim rst As ADODB.Recordset
    Dim stCon As String, stFile As String
    Dim i As Long, j As Long


    Set rst = New ADODB.Recordset


    stFile = "C:\Report.xml"
    stCon = "Provider=MSPersist;"


    With rst
    .CursorLocation = adUseClient
    .Open stFile, stCon, adOpenStatic, adLockReadOnly, adCmdFile
    Set .ActiveConnection = Nothing
    End With


    With ActiveSheet
    'Add the fieldnames to the first row.
    For j = 0 To i - 1
    .Cells(1, j + 1).Value = rst.Fields(j).Name
    Next j
    'Copy the data from the recordset.
    .Range("A2").CopyFromRecordset rst
    End With
    'Closing the recordset.
    rst.Close


    'Release object from memory.
    Set rst = Nothing


    End Sub
    [/vba]


    As we can see from the above examples they both rely on a database-approach and the present of rowset as an XML-schema make it easy to deal with the data.


    (Since the Recordset- and the Connection-object of ADO is on the same level in the Object-model we are not forced to use the Connection-object however there are some disadvantages although they refer to real database-management and not when used with Excel.)


    However, the idea with XML is per se is to apply XML on all kind of data, from semi-structured to full structered data as the target with it is to be platform- and software-independed.


    This require both the use of XSD-files as well as XSLT-files to interpretate the data.


    At present I find XML both interesting and valuable but I also find it confusing and complicated.


    Finally, I have avoided to discuss the built-in features Excel offer to deal with XML as they only refer to version 2003 and later on. For a detailed discussion on this subject please see the following article at MSDN:
    Using the XML Features of the Excel 2003 Object Model

  • Re: Creating XML-files &amp; Reading XML-files


    Dennis,


    First, thank you for mentioning me. In the short time I have visited OzGrid, I have increased my understanding of coding and math threefold.


    As for your comments about XML - it is really a glass 'half-full'. In a perfect world one would be able to stream Excel ranges - as data-only or fully formatted - to 'whatever'. At present Excel offers


    xlXMLSpreadsheet = 46
    xlRangeValueMSPersistXML = 12
    xlRangeValueXMLSpreadsheet = 11


    constants, the first used with .SaveAs the others persisted from a range as either data or fully formatted.


    My present frustration is with the scope of these constants. For example, I create csv data from a mainframe ( Auto-dealer Inventory - test_x.csv ) and format it as an Excel-enabled XML pivot (pivot.xml). I must use .SaveAs rather than xlRangeValueXMLSpreadsheet (which persists the Range but not the underlying Cache) yet you will see by looking at Pivot.xml in Notepad or an XML editor, that Microsoft neatly combines both the Excel Pivot and the underlying MsPersist z:row.


    These means re-construction of the Pivot in Excel has to be with .Open() as opposed to a streamed Range-Insert [which would rock my world].


    These comments are meant as theory, not argument - best in 2005 to yourself, your family and all of OzxGrid


    Stan

  • Re: Creating XML-files &amp; Reading XML-files


    Thanks very much for posting this code...


    One question... one of the recordsets I am trying to save to XML is very large (~35000 records) and saving the recordset to the stream seems to take ~ 60 seconds... is there an event similar to FetchProgress that can be used to monitor the progress of the save - or a reasonable work-around if not?


    Thanks!



    Thanks again!
    Vincent

  • Re: Creating XML-files &amp; Reading XML-files


    I see these are way outdated... but I notice when you open the rs you only give the file location and name...


    I need to do a select statement on the XML file... is that possible?


    John

  • Re: Creating XML-files &amp; Reading XML-files


    Quote from JSPzer

    I see these are way outdated... but I notice when you open the rs you only give the file location and name...


    I need to do a select statement on the XML file... is that possible?


    John


    If you read the rules you will see that you instructed not to hijack old Threads but to ask your own questions.