Loop through xml files in a directory - working only on first file

[chtf]

Hi,

I created the below macro with the goal of updating in bulk (find/replace) some of the data inside multiple xml files saved in a folder. However, the below macro is only updating the first file saved in the folder and requires debugging on this line "Open sFileName For Input As iFileNum" when trying to access the succeeding file. Appreciate your help in identifying the issue on the below macro code. Thank you!


Sub TextFile_FindReplace7()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFilePath As String
Dim sFileName As String
Dim myfile As String

'Specify File Path
sFilePath = ActiveWorkbook.Sheets("Sheet1").Range("H1").Value
myfile = Dir(sFilePath & "\*.xml")
sFileName = sFilePath & "\" & myfile

Do While sFileName <> ""

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, ActiveWorkbook.Sheets("Sheet1").Range("C11").Value, ActiveWorkbook.Sheets("Sheet1").Range("C12").Value)
sTemp = Replace(sTemp, ActiveWorkbook.Sheets("Sheet1").Range("C14").Value, ActiveWorkbook.Sheets("Sheet1").Range("C15").Value)
sTemp = Replace(sTemp, ActiveWorkbook.Sheets("Sheet1").Range("C17").Value, ActiveWorkbook.Sheets("Sheet1").Range("C18").Value)

'Save txt file as (if possible)

iFileNum = FreeFile
Open sFileName For Output As iFileNum

Print #iFileNum, sTemp

Close iFileNum

'Set the fileName to the next available file
sFileName = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Completed!"

End Sub

 

[John_w]

Welcome to Mr Excel forums.

Change this:

myfile = Dir(sFilePath & "\*.xml")
   sFileName = sFilePath & "\" & myfile

Do While sFileName <> ""

to:

myfile = Dir(sFilePath & "\*.xml")
Do While myfile <> vbNullString
   sFileName = sFilePath & "\" & myfile
   sTemp = ""

and this:

'Set the fileName to the next available file
   sFileName = Dir

to:

   myfile = Dir

Please use VBA code tags.

 

[chtf]

That worked perfectly! Thank you so much for your help!