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