Results 1 to 5 of 5

VBA XML Find and Replace

This is a discussion on VBA XML Find and Replace within the Excel Questions forums, part of the Question Forums category; Is it possible using excel vba to open a xml file in notepad and do a find and replace, and ...

  1. #1
    Board Regular
    Join Date
    Aug 2008
    Posts
    84

    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
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,064

    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.

    Code:
    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
            ts(0).Close
            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)
            ts(1).Close
            Set ts(1) = Nothing
            
            '//Delete Temp file if all actions succeeded
            FSO.DeleteFile (t)
        
        End If
    End If
    
    '//Check that all files are closed
    My_Exit:
    If Not ts(0) Is Nothing Then
        ts(0).Close
    End If
    If Not ts(1) Is Nothing Then
        ts(1).Close
    End If
    Set FSO = Nothing
    Exit Sub
    
    ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume My_Exit
    End Sub

    Using: Office 2007/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
    Location
    Chennai - India
    Posts
    8

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

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

    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
    Posts
    1

    Default Re: VBA XML Find and Replace

    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

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
  •  


DMCA.com