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

chtf

New Member
Joined
Oct 2, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
  2. Web
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Welcome to Mr Excel forums.

Change this:
VBA Code:
myfile = Dir(sFilePath & "\*.xml")
   sFileName = sFilePath & "\" & myfile

Do While sFileName <> ""
to:
VBA Code:
myfile = Dir(sFilePath & "\*.xml")
Do While myfile <> vbNullString
   sFileName = sFilePath & "\" & myfile
   sTemp = ""
and this:
VBA Code:
'Set the fileName to the next available file
   sFileName = Dir
to:
VBA Code:
   myfile = Dir
Please use VBA code tags.
 
Upvote 0
That worked perfectly! Thank you so much for your help!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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