Results 1 to 6 of 6

Thread: VBA XML Find and Replace

  1. #1
    Board Regular
    Join Date
    Aug 2008

    Default VBA XML Find and Replace

    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...

  2. #2
    MrExcel MVP
    xenou's Avatar
    Join Date
    Mar 2007
    Clev. OH, USA

    Default Re: VBA XML Find and Replace

    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.

    Option Explicit
    Sub CleanupXML()
    Dim FSO As Object '//FileSystemObject
    Dim ts(1) As Object '//TextStream
    Dim s As String, t As String
    Dim FileContents As String
    Const SEARCH_FOR As String = "Change me" 'CaSe Sensitive!
    Const REPLACE_WITH As String = "Changed!" 'CaSe Sensitive!
    On Error GoTo ErrHandler:
    s = Application.GetOpenFilename()
    If s <> "False" Then
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FileExists(s) Then
            '//Get File Contents
            t = FSO.GetParentFolderName(s) & "\" & Replace(FSO.GetTempName(), ".tmp", ".xml")
            Name s As t
            Set ts(0) = FSO.OpenTextFile(t, 1, False, -2) '//For reading, use default encoding
            FileContents = ts(0).ReadAll
            Set ts(0) = Nothing
            '//Make replacement
            FileContents = Replace(FileContents, SEARCH_FOR, REPLACE_WITH)
            '//Write new file contents
            Set ts(1) = FSO.OpenTextFile(s, 2, True, -2) '//For writing, use default encoding
            ts(1).Write (FileContents)
            Set ts(1) = Nothing
            '//Delete Temp file if all actions succeeded
            FSO.DeleteFile (t)
        End If
    End If
    '//Check that all files are closed
    If Not ts(0) Is Nothing Then
    End If
    If Not ts(1) Is Nothing Then
    End If
    Set FSO = Nothing
    Exit Sub
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume My_Exit
    End Sub

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Mar 2013
    Chennai - India

    Default Re: VBA XML Find and Replace

    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,
    [IMG]C:\Users\238915\Desktop\Please refer the attachment.jpg[/IMG]

  4. #4
    New Member
    Join Date
    Mar 2013
    Chennai - India

    Default Re: VBA XML Find and Replace

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

  5. #5
    New Member
    Join Date
    Apr 2013

    Default Re: VBA XML Find and Replace


    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

  6. #6

    Default Re: VBA XML Find and Replace

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts