Edit XMLs in Excel

MrSamExcel

New Member
Joined
Apr 6, 2016
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
I have 82 XML files each named with a 3- or 4-letter code; each XML contains a field (or tag? I'm new to XML) called OBJECTIVE followed by text and a field called STRATEGY followed by text.
ex) ABC.xml contains CODE ="ABC"
OBJECTIVE [The objective is to win.] /OBJECTIVE
INVESTMENTSTRATEGY [The strategy is to not lose.] /INVESTMENTSTRATEGY
(actual XML has < and > symbols around the fields in CAPS above but I couldn't figure how to display those properly in the forum)

What is the most efficient way to edit the text in both of these fields for all files? Currently, my only option is to open each XML, manually copy new Objective text over the old Objective text and then do the same for Strategy text; this is painful and slow and asking for errors.

Can I somehow use EXCEL to make the changes by converting/linking to the XMLs? For example, I could more easily override the old text with new text in a spreadsheet with the resulting xlsx having a columns for File Name | Code | Objective | Strategy
But I'm not sure how I'd get this back into the individual XMLs. And when I try importing the XML into Excel it says "Invalid file reference. The path to the file is invalid, or one or more of the reference schemas could not be found." The XML editors I currently use are Notepad++ and Geany; perhaps there's different software that could accomplish this or link to Excel better?

Thanks for any suggestions.
 
Last edited:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

hotabae

Board Regular
Joined
Apr 18, 2018
Messages
167
Well, I haven't used the feature before, but it seems like the Notepad++ function "find in files" seems like the easiest way.

CTRL + F -> "find in files" tab

Find What: "old objective"

Replace with: "new objective"

Directory: "folder with all the files"

Kinda seems like that's what it is for.
 

MrSamExcel

New Member
Joined
Apr 6, 2016
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
Well, I haven't used the feature before, but it seems like the Notepad++ function "find in files" seems like the easiest way.

CTRL + F -> "find in files" tab

Find What: "old objective"

Replace with: "new objective"

Directory: "folder with all the files"

Kinda seems like that's what it is for.
Perhaps I'm misunderstanding, but I thought "Find in Files" was essentially a simple text search function (like CTRL-F in Excel) except it looks for text in multiple files instead of just one document? If so, are you suggesting I copy/paste old text and new text to find/replace 82 times? If so, still better than opening each XML I suppose, quite a bit more manual than I was hoping for.
 

hotabae

Board Regular
Joined
Apr 18, 2018
Messages
167
Perhaps I'm misunderstanding, but I thought "Find in Files" was essentially a simple text search function (like CTRL-F in Excel) except it looks for text in multiple files instead of just one document? If so, are you suggesting I copy/paste old text and new text to find/replace 82 times? If so, still better than opening each XML I suppose, quite a bit more manual than I was hoping for.

I'm pretty sure if you click "replace in files" it'll do all 82 at once. That is... as long as the old text is the same every time. Make copies of the files and then try it. It should work.
 

MrSamExcel

New Member
Joined
Apr 6, 2016
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
I'm pretty sure if you click "replace in files" it'll do all 82 at once. That is... as long as the old text is the same every time. Make copies of the files and then try it. It should work.
The old and new text is different for each file. Organizing the new text is easiest for me to do in Excel -- perhaps because I am not aware of a better method -- but getting the new text into each file in a systematic way is the challenge.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

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