Can I use .xl VBA to amend XML string

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382
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
Code:
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows
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!


Code:
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
 
Last edited:

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382
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!


Code:
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
Code:
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows
Sorry misunderstood the requirement.

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

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
382

ADVERTISEMENT

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

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,893
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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


Code:
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

Active Member
Joined
Aug 3, 2010
Messages
382
OK.
See if I am on the right track here. I have to jack it in for today.


Code:
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top