Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 17th, 2007, 03:33 AM
Registered User
Join Date: Mar 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Use of Attributes prop.ty for a OOXML node

For the very good autothors of EXCEL 2007 VBA
I am working with nodes like this
Dim NodeXml As IXMLDOMNode
and I refer to <c> tags on a sheet1.xml file. I would obtain the attributes r and t of such nodes. I "feel" that the Attributes property should give the answer but, alas, it is rejected.
How can I use it?

By the way, I obtain this aim with personal functions, where CellaXml is xml property of a node:

Function SharedCell(CellXml As String) As Boolean
  'Indicates if the cell refers to a sharedStrings
  Dim Pos2Ang As Integer
  Pos2Ang = InStr(1, CellXml, "><")
  If Right(CellXml, 2) = "/>" Then
    SharedCell = False
    Exit Function
  End If
  If Mid(CellXml, Pos2Ang - 5, 1) = "t" Then SharedCell = True
End Function

Function CellRef(CellXml As String)
  Dim Pos0 As Integer 'Position of "http://" after "//"
  Dim CellXmlParz As String 'CellXml after "http://"
  Pos0 = InStr(1, CellXml, "http://") + 6
  CellXmlParz = Right(CellXml, Len(CellXml) - Pos0)
  Dim Pos1 As Integer 'Position of refer. 's letter in CellXmlParz.
  Dim Pos2 As Integer 'Position of last refer. 's digit in CellXmlParz.
  Pos1 = InStr(1, CellXmlParz, "r=") + 3
  Pos2 = InStr(Pos1, CellXmlParz, " ") - 1
  LungRif = Pos2 - Pos1
    CellRef = Mid(CellXmlParz, Pos1, Pos2 - Pos1)
End Function

It works (till now) but Attributes should be the rational, "official" solution, dont' you agree. But how is it to be used?

Old September 24th, 2007, 05:05 AM
Registered User
Join Date: Mar 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

By Gianni Giaccaglini.
the "Attributes" problem is not so important: I found XPath filters they can solve it:
Set NodiXmlRif = DocXml.selectNodes("//sheetData/row/c[@t='s']/@r")
Set NodiXmlVal = DocXml.selectNodes("//sheetData/row/c[@t='s']/v")

These instructions refer to <c> (cell) tag of a sheet1.xml OOXML file. The filter [@t='s'] in this case selects only the c nodes with t property = "s", then the sub-nodes /@r and /v gives the reference property @r (e.g. "A1", "B1" etc.) and the values v, making it possible take into account the refs and the index (v) of sharedString.xml file.
All unfiltered nodes can be obtained by the following codes:
Set NodiXmlRif = DocXml.selectNodes("//sheetData/row/c/@r")
Set NodiXmlVal = DocXml.selectNodes("//sheetData/row/c/v")

(see http://blog.shareoffice.it/giannigiaccaglini, too; it is in italian but the entire routines in two version should be clear, I 'll hope)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Databinding the ImageURL Prop of an Image Control Aaron Edwards ASP.NET 2.0 Professional 1 November 22nd, 2007 12:53 PM
How to delete a xml node with all attributes and s vishnu108mishra XML 2 November 14th, 2007 04:19 AM
How add a new OOXML node... Gianni Giaccaglini Excel VBA 0 September 17th, 2007 03:12 AM
GridView.SelectedIndex Prop SteveP55419 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 October 8th, 2006 09:08 PM
Copying Source Node attributes to output node pvsat XSLT 2 November 3rd, 2005 09:46 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.