XPath中的VBA DOM变量属性 [英] VBA DOM Variable Attribute in XPath
本文介绍了XPath中的VBA DOM变量属性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
现在最大的障碍是该位置只使用作者的姓氏。所以我可以分割字符串和所有，但是如何将它重新加载到XPath？
下面你会看到< PublishedAuthor id ='LASTNAME'> 而不是全名，所以我不能在这次XPath中放入 athr 。我不断得到一个对象错误。
<？xml version =1.0？>
< catalog>
< book id =冒险>
< author> Gambardella，Matthew< / author>
< title> XML开发人员指南< / title>
< price> 44.95< / price>
< misc>
< Publisher id =5691>
< PublisherLocation>洛杉矶< / PublisherLocation>
< / Publisher>
< PublishedAuthor id =Gambardella>
< StoreLocation> Store B< / StoreLocation>
< / PublishedAuthor>
< / misc>
< / book>
< book id =冒险>
< author> Ralls，Kim< / author>
< title>午夜雨< / title>
< price> 5.95< / price>
< misc>
< Publisher id =4787>
< PublisherLocation>纽约< / PublisherLocation>
< / Publisher>
< PublishedAuthor id =Ralls>
< StoreLocation> Store B< / StoreLocation>
< / PublishedAuthor>
< / misc>
< / book>
< book id =冒险>
< author> Boal，John< / author>
< title> Mist< / title>
< price> 15.95< / price>
< misc>
< Publisher id =8101>
< PublisherLocation>新墨西哥< / PublisherLocation>
< / Publisher>
< PublishedAuthor id =Boal>
< StoreLocation> Store B< / StoreLocation>
< / PublishedAuthor>
< / misc>
< / book>
< book id =神秘>
< author> Ralls，Kim< / author>
< title>一些神秘书< / title>
< price> 9.95< / price>
< misc>
< Publisher id =6642>
< PublisherLocation>纽约< / PublisherLocation>
< / Publisher>
< PublishedAuthor id =Ralls>
< StoreLocation> Store B< / StoreLocation>
< / PublishedAuthor>
< / misc>
< / book>
< / catalog>
我的代码：
Option Explicit
Sub mySub（）
Dim XMLFile As Variant
Dim作为变量
Dim athr As String，BookType As String，Title As String，StoreLocation As String
Dim AuthorArray（）As String，BookTypeArray（）As String，TitleArray（）As String，StoreLocationArray（）As String
Dim i As Long，x As Long ，j As Long
Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
设置mainWorkBook = ActiveWorkbook
设置XMLFile = CreateObject（Microsoft.XMLDOM）
XMLFile.Load（C：\Books.xml）
x = 1
j = 0
设置Author = XMLFile.SelectNodes / catalog / book / author）
For i = 0 To（Author.Length - 1）
ReDim Preserve AuthorArray（0 To i）
ReDim Preserve BookTypeArray（0 To i）
ReDim Preserve TitleArray（0 To i）
ReDim保存StoreLocationArray（0 To i）
athr =作者（i）.Text
BookType =作者（i） .ParentNode.getAttribute（id）
标题=作者（i）.ParentNode.getElementsByTagName（title）。Item（0）.nodeTypedValue
StoreLocation = Author（i）.ParentNode.SelectSingleNode misc / PublishedAuthor [@ id =& athr& / StoreLocation）。文本
如果athr =Ralls，Kim然后
AuthorArray（j）= athr
BookTypeArray（j）= BookType
TitleArray（j）=标题
StoreLocationArray（j）= StoreLocation
j = j + 1
x = x + 1
End If
Next
范围（A3：A& UBound（AuthorArray）+ 1）= WorksheetFunction.Transpose（AuthorArray）
范围（B3：B& UBound（BookTypeArray） + 1）= WorksheetFunction.Transpose（BookTypeArray）
范围（C3：C& UBound（BookTypeArray）+ 1）= WorksheetFunction.Transpose（TitleArray）
范围（D3：D& UBound（BookTypeArray）+ 1）= WorksheetFunction.Transpose（TitleArray）
有更好的方法来处理有这样的问题？
StoreLocation =作者（i）.ParentNode.SelectSingleNode（misc / PublishedAuthor [@ id = ; athr&/ StoreLocation）。文本如果发布的作者id是相同的va作为作者的价值。
感谢您的任何指导，帮助或评论！
解决方案
Sub mySub（）
Dim XMLFile As Variant
Dim作为变量
Dim athr As String，BookType As String，Title As String，StoreLocation As String
Dim AuthorArray（）As String，BookTypeArray（）As String，TitleArray（）As String，StoreLocationArray（）As String
Dim i As Long，x As Long ，j As Long，pn As Object，loc As Object，arr，ln As String
Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
设置mainWorkBook = ActiveWorkbook
设置XMLFile = CreateObject（Microsoft.XMLDOM）
XMLFile.Load（C：\Books.xml）
x = 1
j = 0
Set Author = XMLFile.SelectNodes（/ catalog / book / author）
对于i = 0 To（Author.Length - 1）
athr = Author（i）。文本
如果athr =Ralls，Kim然后
设置pn =作者（i）.ParentNode
BookType = p n.getAttribute（id）
标题= pn.getElementsByTagName（title）。Item（0）.nodeTypedValue
设置loc = pn.SelectSingleNode（misc / PublishedAuthor [@ id ='& athr& '] / StoreLocation）
'没有找到全名 - 尝试姓氏
如果loc是Nothing然后
'得到最后的名字
arr = Split（athr， ，）
ln = Trim（arr（LBound（arr）））
设置loc = pn.SelectSingleNode（misc / PublishedAuthor [@ id ='& ln&'] / StoreLocation ）
End If
如果不是loc is Nothing然后
StoreLocation = loc.Text
Else
StoreLocation =???
End If
AddValue AuthorArray，athr
AddValue BookTypeArray，BookType
AddValue TitleArray，Title
AddValue StoreLocationArray，StoreLocation
j = j + 1
x = x + 1
End If
Next
范围（A3）。调整大小（j，1）.Value = WorksheetFunction.Transpose （$）
范围（B3）。调整大小（j，1）.Value = WorksheetFunction.Transpose（BookTypeArray）
范围（C3）。调整大小（j，1）.Value = WorksheetFunction .Transpose（TitleArray）
Range（D3）。调整大小（j，1）.Value = WorksheetFunction.Transpose（StoreLocationArray）
End Sub
'实用方法 - 根据需要调整数组大小，并添加一个新值
Sub AddValue（arr，v）
Dim i As Long
i = -1
On Error Resume Next
i = UBound（arr）+ 1
On Error GoTo 0
如果i = -1则i = 0
ReDim保存arr（0 To i）
arr（i） = v
End Sub
I have a program that searches for and gathers (via array) a specific author and their 'BookType' and 'BookTitle' I am now trying to learn how to use the author's name - that I've stored in an array - as a variable in the XPath to get the 'Store Location'.
The biggest obstacle now is that the location only uses the author's last name. So I can split the string and all, but how would I feed it back into the XPath?
Below you'll see that <PublishedAuthor id='LASTNAME'> as opposed to their full name, so I cannot just put athr in the XPath this time. I keep getting an object error.
<?xml version="1.0"?>
<catalog>
<book id="Adventure">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<price>44.95</price>
<misc>
<Publisher id="5691">
<PublisherLocation>Los Angeles</PublisherLocation>
</Publisher>
<PublishedAuthor id="Gambardella">
<StoreLocation>Store B</StoreLocation>
</PublishedAuthor>
</misc>
</book>
<book id="Adventure">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<price>5.95</price>
<misc>
<Publisher id="4787">
<PublisherLocation>New York</PublisherLocation>
</Publisher>
<PublishedAuthor id="Ralls">
<StoreLocation>Store B</StoreLocation>
</PublishedAuthor>
</misc>
</book>
<book id="Adventure">
<author>Boal, John</author>
<title>Mist</title>
<price>15.95</price>
<misc>
<Publisher id="8101">
<PublisherLocation>New Mexico</PublisherLocation>
</Publisher>
<PublishedAuthor id="Boal">
<StoreLocation>Store B</StoreLocation>
</PublishedAuthor>
</misc>
</book>
<book id="Mystery">
<author>Ralls, Kim</author>
<title>Some Mystery Book</title>
<price>9.95</price>
<misc>
<Publisher id="6642">
<PublisherLocation>New York</PublisherLocation>
</Publisher>
<PublishedAuthor id="Ralls">
<StoreLocation>Store B</StoreLocation>
</PublishedAuthor>
</misc>
</book>
</catalog>
My code:
Option Explicit
Sub mySub()
Dim XMLFile As Variant
Dim Author As Variant
Dim athr As String, BookType As String, Title As String, StoreLocation As String
Dim AuthorArray() As String, BookTypeArray() As String, TitleArray() As String, StoreLocationArray() As String
Dim i As Long, x As Long, j As Long
Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
Set mainWorkBook = ActiveWorkbook
Set XMLFile = CreateObject("Microsoft.XMLDOM")
XMLFile.Load ("C:\Books.xml")
x = 1
j = 0
Set Author = XMLFile.SelectNodes("/catalog/book/author")
For i = 0 To (Author.Length - 1)
ReDim Preserve AuthorArray(0 To i)
ReDim Preserve BookTypeArray(0 To i)
ReDim Preserve TitleArray(0 To i)
ReDim Preserve StoreLocationArray(0 To i)
athr = Author(i).Text
BookType = Author(i).ParentNode.getAttribute("id")
Title = Author(i).ParentNode.getElementsByTagName("title").Item(0).nodeTypedValue
StoreLocation = Author(i).ParentNode.SelectSingleNode("misc/PublishedAuthor[@id=""" & athr & """]/StoreLocation").Text
If athr = "Ralls, Kim" Then
AuthorArray(j) = athr
BookTypeArray(j) = BookType
TitleArray(j) = Title
StoreLocationArray(j) = StoreLocation
j = j + 1
x = x + 1
End If
Next
Range("A3:A" & UBound(AuthorArray) + 1) = WorksheetFunction.Transpose(AuthorArray)
Range("B3:B" & UBound(BookTypeArray) + 1) = WorksheetFunction.Transpose(BookTypeArray)
Range("C3:C" & UBound(BookTypeArray) + 1) = WorksheetFunction.Transpose(TitleArray)
Range("D3:D" & UBound(BookTypeArray) + 1) = WorksheetFunction.Transpose(TitleArray)
Is there a better way to deal with an issue like this?
StoreLocation = Author(i).ParentNode.SelectSingleNode("misc/PublishedAuthor[@id=""" & athr & """]/StoreLocation").Text works flawlessly if the PublishedAuthor id was the same value as the author value.
Thank you for any guidance, help, or comments!
解决方案
Sub mySub()
Dim XMLFile As Variant
Dim Author As Variant
Dim athr As String, BookType As String, Title As String, StoreLocation As String
Dim AuthorArray() As String, BookTypeArray() As String, TitleArray() As String, StoreLocationArray() As String
Dim i As Long, x As Long, j As Long, pn As Object, loc As Object, arr, ln As String
Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
Set mainWorkBook = ActiveWorkbook
Set XMLFile = CreateObject("Microsoft.XMLDOM")
XMLFile.Load ("C:\Books.xml")
x = 1
j = 0
Set Author = XMLFile.SelectNodes("/catalog/book/author")
For i = 0 To (Author.Length - 1)
athr = Author(i).Text
If athr = "Ralls, Kim" Then
Set pn = Author(i).ParentNode
BookType = pn.getAttribute("id")
Title = pn.getElementsByTagName("title").Item(0).nodeTypedValue
Set loc = pn.SelectSingleNode("misc/PublishedAuthor[@id='" & athr & "']/StoreLocation")
'not found on full name - try last name
If loc Is Nothing Then
'get the last name
arr = Split(athr, ",")
ln = Trim(arr(LBound(arr)))
Set loc = pn.SelectSingleNode("misc/PublishedAuthor[@id='" & ln & "']/StoreLocation")
End If
If Not loc Is Nothing Then
StoreLocation = loc.Text
Else
StoreLocation = "???"
End If
AddValue AuthorArray, athr
AddValue BookTypeArray, BookType
AddValue TitleArray, Title
AddValue StoreLocationArray, StoreLocation
j = j + 1
x = x + 1
End If
Next
Range("A3").Resize(j, 1).Value = WorksheetFunction.Transpose(AuthorArray)
Range("B3").Resize(j, 1).Value = WorksheetFunction.Transpose(BookTypeArray)
Range("C3").Resize(j, 1).Value = WorksheetFunction.Transpose(TitleArray)
Range("D3").Resize(j, 1).Value = WorksheetFunction.Transpose(StoreLocationArray)
End Sub
'Utility method - resize an array as needed, and add a new value
Sub AddValue(arr, v)
Dim i As Long
i = -1
On Error Resume Next
i = UBound(arr) + 1
On Error GoTo 0
If i = -1 Then i = 0
ReDim Preserve arr(0 To i)
arr(i) = v
End Sub
这篇关于XPath中的VBA DOM变量属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
