Can I use .xl VBA to amend XML string

[MichaelRSnow]

I have an .xml file which has 1500 lines of code, I need to amend a field on various lines (500 or so).

is it possibel to write VB code to Find a line, then find a section of code on that line and replace it?

i.e. each

textbox8="12345"

code appears on a new line
I was hoping there is a way that you can run a mcaro in xls to find the code (above) then search the line to replace the what value with the with value


FIND > REPLACE WHAT > WITH WHAT
textbox8="94172" > MGProductLinkedPrimeFlexAccountInd="Y" > MGProductLinkedPrimeFlexAccountInd="N"
textbox8="94173" > MGProductLinkedPrimeFlexAccountInd="Y" > MGProductLinkedPrimeFlexAccountInd="N"
textbox8="94174" > MGProductLinkedPrimeFlexAccountInd="Y" > MGProductLinkedPrimeFlexAccountInd="N"
textbox8="94175" > MGProductLinkedPrimeFlexAccountInd="Y" > MGProductLinkedPrimeFlexAccountInd="N"
textbox8="94180" > MGProductLinkedPrimeFlexAccountInd="Y" > MGProductLinkedPrimeFlexAccountInd="N"

Any help will be much appreciated?

 

[daverunt]

Hi,

Have a play with some basic code to see if it works then you can embellish the code to your liking : )
It should replace all searched for strings (middle parameter) with the last string parameter. So replace textbox8="94172" with textbox8="00000"
Backup your file!

oFile = "c:\junk\Test.xml"

'Create FileSystemObject for reading file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 1)
        strFileContent = f.ReadAll
        f.Close
        Set f = Nothing


'Create FileSystemObject for writing file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 2)
    NewstrFileContent = Replace(strFileContent, "textbox8=""94172""", "textbox8=""00000""")
       f.Write NewstrFileContent
        
        f.Close
        Set f = Nothing

 

[MichaelRSnow]

Hi,

Have a play with some basic code to see if it works then you can embellish the code to your liking : )
It should replace all searched for strings (middle parameter) with the last string parameter. So replace textbox8="94172" with textbox8="00000"
Backup your file!

oFile = "c:\junk\Test.xml"

'Create FileSystemObject for reading file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 1)
        strFileContent = f.ReadAll
        f.Close
        Set f = Nothing


'Create FileSystemObject for writing file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 2)
    NewstrFileContent = Replace(strFileContent, "textbox8=""94172""", "textbox8=""00000""")
       f.Write NewstrFileContent
        
        f.Close
        Set f = Nothing

Thank you

I was hoping to use excel to scroll through the changes

this is the change

NewstrFileContent = Replace(strFileContent, "MGProductLinkedPrimeFlexAccountInd=""Y""", "MGProductLinkedPrimeFlexAccountInd=""N""")

But it should be searching the xml for "textbox8="94172"" then doing the find/replace (as shown above) on that line only? then move on to cell A2 in excel to get the next one "textbox8="94173"" find and replace, then A3 "textbox8="94174""?? Does that make sence?

 

[daverunt]

Sorry misunderstood the requirement.

Is the find / replace with value all the same or are they in columns too?

 

[MichaelRSnow]

Sorry misunderstood the requirement.

Is the find / replace with value all the same or are they in columns too?

Yes they are in columns as well B2 (find) and C2 (replace), but the find and replace values are all the same for this one, but may not be the same for the next lot of change i want to make so i was hoping to use the excel sheet values as the find and replace.

 

[daverunt]

OK. I will try and see what I can do but I'll admit I think I'm borderline on being able to do this, at least within a reasonable time frame.

 

[daverunt]

OK.
See if I am on the right track here. I have to jack it in for today.

oFile = "c:\junk\Test.xml"


'Set range in column A
 Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 

'Create FileSystemObject for reading file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 1)
    
    Do Until f.AtEndOfStream = True
    
        strLine = f.ReadLine
        
 For Each Cell In Rng
     
 If InStr(strLine, Cell.Value) > 0 Then
        strFind = Cell.Offset(0, 1).Value
        strRep = Cell.Offset(0, 2).Value
                  
        strLine = Replace(strLine, strFind, strRep)
        'strLine = Replace(strLine, "MGProductLinkedPrimeFlexAccountInd=""Y""", "MGProductLinkedPrimeFlexAccountInd=""N""")
    Else
        
       strLine = strLine

   End If
Next
       
       NewStrLine = NewStrLine & strLine & vbNewLine
       
Loop
  
        'MsgBox (NewStrLine)

        f.Close
        Set f = Nothing
        
        
'Create FileSystemObject for writing file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 2)
    
       f.Write NewStrLine
        
        f.Close
        Set f = Nothing

 

[MichaelRSnow]

OK.
See if I am on the right track here. I have to jack it in for today.

oFile = "c:\junk\Test.xml"


'Set range in column A
 Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
 

'Create FileSystemObject for reading file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 1)
    
    Do Until f.AtEndOfStream = True
    
        strLine = f.ReadLine
        
 For Each Cell In Rng
     
 If InStr(strLine, Cell.Value) > 0 Then
        strFind = Cell.Offset(0, 1).Value
        strRep = Cell.Offset(0, 2).Value
                  
        strLine = Replace(strLine, strFind, strRep)
        'strLine = Replace(strLine, "MGProductLinkedPrimeFlexAccountInd=""Y""", "MGProductLinkedPrimeFlexAccountInd=""N""")
    Else
        
       strLine = strLine

   End If
Next
       
       NewStrLine = NewStrLine & strLine & vbNewLine
       
Loop
  
        'MsgBox (NewStrLine)

        f.Close
        Set f = Nothing
        
        
'Create FileSystemObject for writing file content
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set f = FSO.OpenTextFile(oFile, 2)
    
       f.Write NewStrLine
        
        f.Close
        Set f = Nothing

This code is spot on daverunt, thank you very much for writing that.

much appreciated
Michael

 

[daverunt]

WooHoo!

Glad to help.