VBA XML Find and Replace

[cfoye130]

Is it possible using excel vba to open a xml file in notepad and do a find and replace, and then resave it?

Reason for asking is I am trying to reproduce an infopath form with data from excel, exported into an xml. Issue I have is when I export the xml from Excel, the header is not my infopath header so the xml is not recognized as an info path form. If you do a find and replace in the xml, infopath recognizes the form just fine...

 

[xenou]

This might be a handy one if attached to a right click menu. It's easy to manipulate XML (somewhat) as they are just text files. I used a test of "Change me" and "Changed!" for the replacement texts. You will want to change this in the code where I have declared the constants for what to search for and what to replace it with. The search value has to be exactly the same - it is case sensitive. It would be interesting to know if there is a native XML search query that could be used.

Edit: Note, by the way, should the code fail half way through (for unexpected reason), you will abort but a temp file should be left in the folder with the original contents of the xml file - though I doubt this will ever happen.

[COLOR="Navy"]Option Explicit[/COLOR]


[COLOR="Navy"]Sub[/COLOR] CleanupXML()
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ts(1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'//TextStream[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] String, t [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] FileContents [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="SeaGreen"]'---------------------------------------------------------[/COLOR]
[COLOR="Navy"]Const[/COLOR] SEARCH_FOR [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "Change me" [COLOR="SeaGreen"]'CaSe Sensitive![/COLOR]
[COLOR="Navy"]Const[/COLOR] REPLACE_WITH [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "Changed!" [COLOR="SeaGreen"]'CaSe Sensitive![/COLOR]
[COLOR="SeaGreen"]'---------------------------------------------------------[/COLOR]

[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
s = Application.GetOpenFilename()
[COLOR="Navy"]If[/COLOR] s <> "False" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]If[/COLOR] FSO.FileExists(s) [COLOR="Navy"]Then[/COLOR]
    
        [COLOR="SeaGreen"]'//Get File Contents[/COLOR]
        t = FSO.GetParentFolderName(s) & "\" & Replace(FSO.GetTempName(), ".tmp", ".xml")
        [COLOR="Navy"]Name[/COLOR] s [COLOR="Navy"]As[/COLOR] t
        [COLOR="Navy"]Set[/COLOR] ts(0) = FSO.OpenTextFile(t, 1, False, -2) [COLOR="SeaGreen"]'//For reading, use default encoding[/COLOR]
        FileContents = ts(0).ReadAll
        ts(0).Close
        [COLOR="Navy"]Set[/COLOR] ts(0) = [COLOR="Navy"]Nothing[/COLOR]
        
        [COLOR="SeaGreen"]'//Make replacement[/COLOR]
        FileContents = Replace(FileContents, SEARCH_FOR, REPLACE_WITH)
        
        [COLOR="SeaGreen"]'//Write new file contents[/COLOR]
        [COLOR="Navy"]Set[/COLOR] ts(1) = FSO.OpenTextFile(s, 2, True, -2) [COLOR="SeaGreen"]'//For writing, use default encoding[/COLOR]
        ts(1).Write (FileContents)
        ts(1).Close
        [COLOR="Navy"]Set[/COLOR] ts(1) = [COLOR="Navy"]Nothing[/COLOR]
        
        [COLOR="SeaGreen"]'//Delete Temp file if all actions succeeded[/COLOR]
        FSO.DeleteFile (t)
    
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="SeaGreen"]'//Check that all files are closed[/COLOR]
My_Exit:
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] ts(0) [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    ts(0).Close
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] ts(1) [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    ts(1).Close
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Set[/COLOR] FSO = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Gugan V]

Hi xenou,

Reference with your above code,

I am currently working in developing a macro for XML file update.

Objective: I have a worksheet it contains 2 columns A and B. A column contains “Refname” and B column contains respective “Designnote”. So system should copy the Refname form column A and find the Refname in XML file.

Next system should find the standard key word and my standard keyword is “Description/”

Step by step procedure:

Step 1 – find the Refname in XML file – by copy the column A cell 1 value and find the string in xml file.
Step 2 – find the next standard keyword ‘Description/’ and replace this string with column B cell 1 value

In my XML file x no ‘Description/’ will be available but system should only replace the first keyword after the Refname.

I hope and accept the requirement is hard to solve and to understand

I expect your help regarding this.

For your better understating I have attached picture. It will give you more explanation pictorially.

Thanks in advance,
Gugan.

 

[Gugan V]

you can download the attachemnt here,
Please refer the attachment - Download - 4shared - Gugan V

 

[seetha]

HI,

I need to edit the text file line by line... When I try to use a loop and use ReadLine instead of ReadAll given above, its' giving an error. The object ts(0) is not recognised in any of the loop syntax. Its giving Object variable not defined or With Block error. Please help

 

[ExcelPt]

I tried using your code for converting xml files with Chinese and Japanese characters but XML files becomes unreadable after that. I checked on several portals and changed language settings of system and so, but nothing worked. Is there any way we can use your code for mentioned languages.