PDA

View Full Version : VBA macro code to find and replace values in XMLfiles using Excel list



vjmina102
08-01-2014, 08:52 AM
Looking for assistance with VBA macro code.

I have more than 3K XML files for which unique identifiers need to be replaced with others. There are three components to this:

a reference excel file, or "data-source" (see below) with one worksheet (Main) that includes the unique ID in each of the XML files and the corresponding ID (with appropriate column headings) that needs to replace the value that is in the XML file.
a second excel file that that includes a cell to reference the directory and file-name of the data-source file, another cell to include the directory only of where the XML files are located, and obviously the VBA macro code is embedded within this file with a button on the spreadsheet.
and the actual XML files the need to be modified that are named with "Case Tracking ID" from below included in the file-name, and the list of files in that named directory sorted alpha-numeric.

220086OPPMPM38927_1125644933111.xml



I have done some work with this, but it's either replacing each value in XML files with the same value (the one that's first in the data-source list), or it simply deletes the value in XML files without replacing it. As far as I can tell, the VBA code is missing the code in the "GetPatientID" function (see below) to "loop" through the data-source until "EOF" is reached. I've tried to modify it to include that, but I'm not getting it to work.





data-source: Master.xlsx



Case Tracking ID

EOC ID



PM38927

1388670



PM38928

2608904



A0711291600004

0711291600004







Replace tags in XML.xlsm



H:\Data_Analysis\PatientIDReplace\XMLFiles\



H:\Data_Analysis\PatientIDReplace\Master.xlsx




Function GetPatientID(ByVal PatientID As String) As String
Master.Activate
What = Cells.Find(What:=PatientID, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("B2").Select
For i = 1 To 10000
MatchCell = "A" + Trim(Str(i))
RawCell = "B" + Trim(Str(i))
If Range(MatchCell).Text = Range(RawCell).Text Then
GetPatientID = Range(RawCell).Text
Exit For
End If
Next

End Function


Thank you.