Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel XML to ADO Recordset (VB6/Excel 2002)

    Is there a convenient way to take XML data that was generated from an Excel Spreadsheet (Save As XML) and bring it into VB6 as an ADODB Recordset?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel XML to ADO Recordset (VB6/Excel 2002)

    ADO can open a recordset from disk, either in its own proprietary format or as XML.

    <air code>
    Dim objRS as new ADODB.recordset
    objRS.Open "c:myExcelExportstodaysdata.xml"
    </air>

    I've never looked inside an ADODB XML persisted recordset, so I'm not sure of the format you would need to use to define the columns.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XML to ADO Recordset (VB6/Excel 2002)

    I tried that, actually. It seemed almost too simple; turns out it was. It returned the following error:

    The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    The is the statement:

    rsGroups.Open "Cocuments and Settingsmshea.000DesktopPhysicians_Mike Upload.xml"

  4. #4
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XML to ADO Recordset (VB6/Excel 2002)

    I tried moving the file from my desktop to the root, and also added the value indicating it is trying to open it from a file. The line and error reads as follows:

    rsGroups.Open "C:Physicians_Mike Upload.xml", , adOpenForwardOnly, , adCmdFile

    Recordset cannot be created. Source XML is incomplete or invalid.

    It is XML 1.0 if that makes a difference. There were no other Save As options Excel had to offer in the XML formats.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel XML to ADO Recordset (VB6/Excel 2002)

    I just persisted an ADO recordset to an XML file and it is a very strange format. Rather than using child elements for the fields, it defines attributes. I'd attach the file, but the data is confidential.

    Perhaps you can export your Excel data directly to an ADO friendly XML recordset in the first place?

    Or... from your VB program, use ADO to read the Excel sheet directly?

  6. #6
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel XML to ADO Recordset (VB6/Excel 2002)

    Excel only had one XML option in the Save As. I thought about using VB but I would need to create an Excel object which I didn't want to do. Damn, I heard that ADO was suppose to be so XML friendly !

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel XML to ADO Recordset (VB6/Excel 2002)

    No, you don't need to automate Excel to read an XLS files with ADO. See How To Use ADO with Excel Data from Visual Basic or VBA for more information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •