Parsing an XML file to Excel using VBA

[maonh]

Hi,
My customer has an XML file which they download from one of their vendors. I then take the data in the file and upload it into our SIS. Before the upload, the data has to be parsed out into a csv. The data that will have to be in individual columns is in one string. I can parse it out in Excel with no problem, but I need to automate the process using a macro, so my customer can do it by themselves without my help.

I have a macro that will pull the data into a schema, but I am not sure where to add the additional code to parse out the string.(I am VBA challenged). I have included the instructions to break up the string, programmatically, and the macro I will use. Any ideas will be greatly appreciated.

Instructions to parse the string (right and left):
Split the string by the left parenthesis (assuming the parenthesis was always present) you would have the word "computer)" as the second part of the array produced, which you could then shorten by one character using left(len("computer)")-1) if I remember correctly. For the first part of the array you could use a similar technique using left([first part of array],4) for the number and right(len([first part of array])-4) for the name. Trim everything.

Here is my macro:
Sub CreateXMLList()
Dim oMyMap As XmlMap
Dim strXPath As String
Dim oMyList As ListObject
Dim oMyNewColumn As ListColumn

' Add a schema map.
' ThisWorkbook.XmlMaps.Add (ThisWorkbook.Path & "\Myschema.xsd")
ThisWorkbook.XmlMaps.Add ("C:\PraxisUpload.xsd")

' Identify the target schema map.
Set oMyMap = ThisWorkbook.XmlMaps("PraxisUpload_map")

' Create a new list in A1.
Range("A1").Select
Set oMyList = ActiveSheet.ListObjects.Add

' Find the first element to map.
strXPath = "/PraxUL/Praxis/SSN"
' Map the element.
oMyList.ListColumns(1).XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Non_Course"
' Map the element
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Category"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Stu_Last_Name"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Title"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Date of Test"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Score"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Form_Name"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Form_No."
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Subcomponent_Test"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath

' Add a column to the list.
Set oMyNewColumn = oMyList.ListColumns.Add
' Find the next element to map.
strXPath = "/PraxUL/Praxis/Subcomponent_Score"
' Map the element.
oMyNewColumn.XPath.SetValue oMyMap, strXPath



' Give the columns logical names
oMyList.ListColumns(1).Name = "SSN"
oMyList.ListColumns(2).Name = "Non_Course"
oMyList.ListColumns(3).Name = "Category"
oMyList.ListColumns(4).Name = "Stu_Last_Name"
oMyList.ListColumns(4).Name = "Title"
oMyList.ListColumns(4).Name = "Date of Test"
oMyList.ListColumns(4).Name = "Score"
oMyList.ListColumns(4).Name = "Form_Name"
oMyList.ListColumns(4).Name = "Form_No."
oMyList.ListColumns(4).Name = "Subcomponent_Test"
oMyList.ListColumns(4).Name = "Subcomponent_Score"

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Thank you

 

[maonh]

I forgot to include an example of what one of the strings looks like.


EX: 5086 Social Studies: Cont and Interp (computer)

 

[combo]

[tt]set oMyListRange=oMyList.Range[/tt] will return range of your list. Next you could use one of two options:
- write contents of the range to text file (see

http://dailydoseofexcel.com/archives/2004/11/09/roll-your-own-csv/

for code, use oMyListRange instead of Sheet1.UsedRange in example code),
- create new workbook, copy the range, SaveAs csv:
[tt]Dim wbNewWorkbook As Workbook
Dim wsNewWorksheet As Worksheet
Dim wbNewWbPathAndName As String
Set wbNewWorkbook = Workbooks.Add(template:=xlWBATWorksheet)
Set wsNewWorksheet = wbNewWorkbook.Worksheets(1)
oMyList.Range.Copy
wsNewWorksheet.Range("A1").PasteSpecial Paste:=xlPasteAll
wbNewWorkbook.SaveAs Filename:=wbNewWbPathAndName & ".csv", FileFormat:=xlCSV, CreateBackup:=False[/tt]
The second uses current windows list delimiter for csv file.

combo

 

[maonh]

Thank you for the code. I was in training all this week, but I will try it first thing on Monday.