Excel and XML

[pmatz]

Hi.

I have a database which needs cleaning up, and the base data is exported in .xml format.
I have opened this up in XML notepad and observed the structure, the part i want to clean up is data under multiple 'user' nodes. There is a Login text and a Name text that i am interested in for each user node.
I have opened the xml in excel, found the users fields and updated the name text for each login entry, using a lookup table . this was excellent. a huge amount of data updated in no time.

problem is i cant then save this back to the original xml format it came in. when i save in excel xml its all different.

any ideas of working with native xml formats to do these type of clean up tasks?

[HansV]

Foxe is a free programmable XML editor; it uses C++ syntax.

If you're willing to roll your own: XML files are plain text files. You could write VBA code in Excel to open the XML file as a text file, parse it, and write the cleaned up version out as another text file in XML format (you'd leave all XML tags intact, just modify the text values).
You could use Application.WorksheetFunction.VLookup etc. in your code to profit from Excel's formula power, while still treating the file as a plain text file.

[pmatz]

Thanks Hans.

Just starting to do the vba in word as i recieved your reply! wish me luck!
thanks for the Application.WorksheetFunction.VLookup tip though. will let you know how i get on.

[HansV]

It will probably take a few tries, so make sure to have backup copies of all files involved, but it's definitely doable. I recently wrote conversion code at work to process a whole series of XML data files with thousands of records; once I worked out the kinks it ran like a dream. Good luck!

[pmatz]

cool,

im getting an error on the worksheetfunction .
'unable to get the vlookup property of the wroksheetFunction class'

i have :
strresult = Excel.WorksheetFunction.VLookup(strlookup, "[Excelbaseadata.xls]names", 2, False)

that is throwing it.

how do you use this from within word?

[HansV]

You have to specify the lookup range the VBA way, e.g.

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsh AS Excel.Worksheet
Dim xlRng As Excel.Range

Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open("C:\Test\MyWorkbook.xs")
Set xlWsh = xlWbk.Worksheets("MySheet")
Set xlRng = xlWsh.Range("A1:D100")

strResult = xlApp.WorksheetFunction.VLookup(strLookup, xlRng, 2, False)

[pmatz]

Morning!

After a bit of syntax and error handling it works! That's great, because any future XML handling routines I can confidently apply this type of code - phew!

Here was my final code:

Code: Select all

Option Explicit

Sub Macro1()
    On Error GoTo err1
    
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim xlRng As Excel.Range
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open("D:\Excelbaseadata.xls")
    Set xlWsh = xlWbk.Worksheets("Sheet1")
    Set xlRng = xlWsh.Range("names")
    
    Dim bFound As Boolean
    Dim strLookup As String
    Dim dbLookup As Double
    Dim strResult As String
    
    Selection.HomeKey Unit:=wdStory
    
    Do
    
            Selection.Find.ClearFormatting
            Selection.Collapse
            
            With Selection.Find
                .Text = "<NAME>"
                .Replacement.Text = ""
                .Forward = True
                .Wrap = wdFindStop
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
            End With
            
            Selection.Find.Execute
            bFound = Selection.Find.Found
            
            Selection.MoveRight Unit:=wdCharacter, Count:=1
            Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
            
            strLookup = Selection
            
            If IsNumeric(strLookup) Then
                dbLookup = Val(strLookup)
                strResult = xlApp.WorksheetFunction.VLookup(dbLookup, xlRng, 2, False)
            End If
            
            Selection = strResult
            
    Loop Until (bFound = False)
    
    
    xlApp.Quit
    
    Set xlApp = Nothing
    Set xlWbk = Nothing
    Set xlWsh = Nothing
    Set xlRng = Nothing


err1:
    strResult = strLookup & "_UPDATEMANUALLY"
    Resume Next
    
End Sub

Cheers Hans - thanks for your help

[HansV]

Great!
Thanks for posting back!