Hi

Ive read through the MSDN reference library for help on XML in VBA - although, I cant find any answers. Maybe you guys can help me out.

I have an XML Index file. This file specifies a userId, and the path where that users file can be found.

As Below...

name	subject_id
output/2000027/02934289	850912AB05108268B3A3F5B4340CC1F9
output/1999270/06491337	6A49B2D34F376C2E257FBA9F5979C556
output/2000024/02931089	AA6194970A98F3C5542BF226A627051D

So, for user AA619... the file path is (output/200024/02931089)

The index file is very large.

How do I go about:

1. Opening XML File

2. Go through each entry

3. Set directory to that entry, and open the file in excel

4. Apply a formula

5.Close File

6 Go to next entry.

I have this so far.

Workbooks.OpenXML Filename:= _
"D:\-=MISC=-\RAU\bssr1\face_x_face\sets\dos\enrollees.xml", LoadOption:= _
xlXmlLoadImportToList

but I am unsure of how to "read in" the xml file, and traverse it....

Thanks.

Chris

Hi Chris,

Your asking a lot there....this will take a few posts...

What your doing at the moment by calling OpenXML, I believe, is creating a workbook containing a sheet named after the element which 'name' and 'subjectID' is in. The sheet contains your example data there with row 1 containing the name and subjectID headings. Is that correct?

your xml will look like this

<root>

<entry><name>...</name><subjectID>...</subjectID></entry>

and this becomes a sheet named entry, with row 1 containing headings name and subjectID

If this does happen then you've read the XML in OK and you no longer need to worry about thinking in terms of XML but in terms of an worksheet, which is similar to an array. The sheet generated is like an array, are you familiar with them?

What you would do is loop down the rows of the sheet accessing each column of the row applying steps 3, 4, and 5 to each row. This looping is fairly common, it's not the best performance wise but you know. Your column references will be fairly static column 1 = name and column 2 = subjectID, but your CurrentRow increments a line at a time so use a variable for this. Your loop needs to have an end point, a last row where the data stops.

Dim nEndRowIndex As Double
nEndRowIndex = ActiveWorkbook.Worksheets(SheetName).Range("A2").End(xlDown).Row

The code above should give you the last row containing data.

While the code that loops will be similar to this...

Dim nCurrentRow As Integer
'for the start row to the end row
For nCurrentRow = 2 To nEndRowIndex
'name and subjectID
msgbox(ActiveWorkbook.Worksheets(SheetName).Cells(nCurrentRow, 1))

msgbox(ActiveWorkbook.Worksheets(SheetName).Cells(nCurrentRow, 2))

Next

If you can get the loop going then post again about opening the file and applying a formula and I'll help you out.

Aaah ok!

That makes much more sense. I see now what u mean. When i open an XML file, Excel just creates it as a new workbook with 2 colums, and now I dont have to worry about XML readers or anything like that. This makes it much more simple.

I will post back later if i have problems. But i think its simpler now. The XML index file will remain open, while the iterative loop goes thru every entry...

Then i will assign a variable for directory and file name

Something like

Dim Directory as String

Dim File as String

Get the two values from the XML file, and then open as follow...

ChDir "D:\-=MISC=-\RAU\bssr1\face_x_face\sims\dos\face\C\output\" & Directory

Workbooks.OpenText Filename:= "D:\-=MISC=-\RAU\bssr1\face_x_face\sims\dos\face\C\output\1998155\" & File

That sound right?

I appreciate the speedy respone. Thanks

Hi man, no worries.

Yeah i think thats about right, I use MSXML to read XML in VBA but from what I read of the OpenXML statement I think your XML becomes a sheet contained in a new workbook, I was hoping you'd know that. When you import XML through the Excel front end that's what happens. Try it and see, I'd bet all your money on it.

I don't think your index file will stay open though, I think it will be read into an inmemory/unsaved sheet/book and then closed. But once the data is read in you no longer need to think in XML, it's a worksheet you work from, and just do what you need in the loop.