Author Topic: VBA and XML  (Read 1986 times)

0 Members and 1 Guest are viewing this topic.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4043
VBA and XML
« on: January 31, 2007, 01:39:34 PM »
I did a quick search, and found no results for VBA and XML here.  Does anyone use XML with Autocad and VBA?  What I want to do is search/query a XML file, and build some output csv file.  Any ideas on what I should do or not do?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA and XML
« Reply #1 on: January 31, 2007, 01:45:55 PM »

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4043
Re: VBA and XML
« Reply #2 on: January 31, 2007, 01:50:01 PM »
Thanks Matt.  Question though, is there a way to search for a Element "Value" other than the For Each method?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Guest

  • Guest
Re: VBA and XML
« Reply #3 on: January 31, 2007, 01:54:43 PM »
I haven't touched XML in a long time.

Mike Tuersley has done quite a bit with XML.

Glenn R

  • Water Moccasin
  • Posts: 1932
  • What idiot child of married cousins wrote this?!
Re: VBA and XML
« Reply #4 on: January 31, 2007, 06:23:12 PM »
Duh,

What you're talking about is XPATH - used to query an xml file.

Here's a simple XML file:
Code: [Select]
<Drawings>
  <Drawing>
    <Name>Dwg1</Name>
    <Path>C:\Temp</Path>
  </Drawing>
  <Drawing>
    <Name>Dwg2</Name>
    <Path>C:\Temp</Path>
  </Drawing>
  <Drawing>
    <Name>Dwg3</Name>
    <Path>C:\Temp</Path>
  </Drawing>
</Drawings>

...and some VBA to manipulate it:
Code: [Select]
Option Explicit

Private Const CFG_FILE As String = "C:\Temp\Drgs.xml"

Public Sub XmlTest()
    Dim pCfgDoc As DOMDocument
    Dim pFSO As FileSystemObject
   
    ' Spin up the filesystem object...
    Set pFSO = New FileSystemObject
    ' Check if our xml cfg file exists...
    If Not pFSO.FileExists(CFG_FILE) Then Exit Sub
    ' Spin up a new xml document...
    Set pCfgDoc = New DOMDocument
    ' Load in our configuration data...
    pCfgDoc.Load CFG_FILE
   
    ' Get all the 'Drawing' elements...
    Dim pDrgNodes As IXMLDOMNodeList
    Dim pDrgNode As IXMLDOMNode
    Dim pDrgNameNode As IXMLDOMNode
    Dim pDrgPathNode As IXMLDOMNode
   
    Set pDrgNodes = pCfgDoc.selectNodes("/Drawings/Drawing")
   
    ' Loop the drawing nodes...
    For Each pDrgNode In pDrgNodes
        Set pDrgNameNode = pDrgNode.selectSingleNode("Name")
        Debug.Print pDrgNameNode.text
       
        Set pDrgPathNode = pDrgNode.selectSingleNode("Path")
        Debug.Print pDrgPathNode.text
    Next
   
    ' Go straight for a particular node...
    Dim someSearchVariable As String
    Dim pExtraSpecialDrgNode As IXMLDOMNode
   
    someSearchVariable = "Dwg2"
   
    Set pExtraSpecialDrgNode = pCfgDoc.selectSingleNode("/Drawings/Drawing[Name='" & someSearchVariable & "']")
    If pExtraSpecialDrgNode Is Nothing Then Exit Sub
   
    Debug.Print vbCr + vbCr + pExtraSpecialDrgNode.text
    Set pDrgNameNode = pExtraSpecialDrgNode.selectSingleNode("Name")
    Debug.Print pDrgNameNode.text
   
End Sub

Set a reference in VBA to "Microsoft XML, v3.0" or above.
I strongly suggest you look at the short tutorials for XML and XPATH available at w3c.

Cheers,
Glenn.
Me

Glenn R

  • Water Moccasin
  • Posts: 1932
  • What idiot child of married cousins wrote this?!
Re: VBA and XML
« Reply #5 on: February 02, 2007, 09:17:44 PM »
How did you get on Duh?
Me

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4043
Re: VBA and XML
« Reply #6 on: February 03, 2007, 12:01:16 PM »
I have made some good progress.  I am able to search through my XML and find the entry that matches the attribute from the block.  I tried both examples you gave me.  My other problem (which goes hand and hand with this one) is I just learned you cannot ReDim Preserve an arrays' first dimension IF you have started populating that array with data.  So my work around right now is I am using 2 arrays to hold my data, as I can redim both of those being that they are single dimension.  My ultimate goal is to eliminate the array, and build an XML file on the fly with the data Im putting into the arrays.

Before I put the cart in front of the horse, I should tell you I ultimately want to put all this code in C#, but I'm still learning C#.  How hard is it to extract block attribute data in .Net? (Wrong forum i know, but it applies)  I'm doing really well learning C#, but Autocad & C# is still eluding me somewhat.
« Last Edit: February 03, 2007, 12:03:12 PM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Glenn R

  • Water Moccasin
  • Posts: 1932
  • What idiot child of married cousins wrote this?!
Re: VBA and XML
« Reply #7 on: February 03, 2007, 09:16:30 PM »
Not that difficult. It's sort of similar to how you would do it woth VBA. There are a few examples here to look at.
Me