xml - VBA DOM Variable Attribute in XPath -

i have program searches , gathers (via array) specific author , 'booktype' , 'booktitle' trying learn how use author's name - i've stored in array - variable in xpath 'store location'.

the biggest obstacle location uses author's last name. can split string , all, how feed xpath?

below you'll see <publishedauthor id='lastname'> opposed full name, cannot put athr in xpath time. keep getting 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 variant dim author variant dim athr string, booktype string, title string, storelocation string dim authorarray() string, booktypearray() string, titlearray() string, storelocationarray() string dim long, x long, j long  dim mainworkbook workbook dim n 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") = 0 (author.length - 1)     redim preserve authorarray(0 i)     redim preserve booktypearray(0 i)     redim preserve titlearray(0 i)     redim preserve storelocationarray(0 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"          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 better way deal issue this?

storelocation = author(i).parentnode.selectsinglenode("misc/publishedauthor[@id=""" & athr & """]/storelocation").text works flawlessly if publishedauthor id same value author value.

thank guidance, help, or comments!

sub mysub()  dim xmlfile variant dim author variant dim athr string, booktype string, title string, storelocation string dim authorarray() string, booktypearray() string, titlearray() string, storelocationarray() string dim long, x long, j long, pn object, loc object, arr, ln string  dim mainworkbook workbook dim n 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") = 0 (author.length - 1)      athr = author(i).text      if athr = "ralls, kim"          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 nothing             'get last name             arr = split(athr, ",")             ln = trim(arr(lbound(arr)))             set loc = pn.selectsinglenode("misc/publishedauthor[@id='" & ln & "']/storelocation")         end if          if not loc 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  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 array needed, , add new value sub addvalue(arr, v)     dim long     = -1     on error resume next     = ubound(arr) + 1     on error goto 0     if = -1 = 0     redim preserve arr(0 i)     arr(i) = v end sub