Can I use .xl VBA to amend XML string

MichaelRSnow

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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,908
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
387
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,908
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
387

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,908
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,908
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
387
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 :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,292
Messages
5,836,451
Members
430,431
Latest member
Tomexcel2022

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