'input past end of file' error while opening some XML files in EXCEL

Welcome to Experts Exchange !

You are going to need / want to use the filesystem object for reading and writing the files, it is better at handling different encodings.  The following updated procedure works in testing here with that file now.

If you want additional info on the FileSystem here are some links below that should help, it's a good tool to be familiar with.  Also, I used it in the code below in "late binding" mode so that you didn't need to add a reference to your project.  But when you are first coding with it you probably want to use "early binding" and be able to have the VBA IDE intellisense show the various methods and properties.  These two different approaches are mention in the 2^nd and 3^rd articles below.  Hope this helps.

• FileSystemObject object | Microsoft Docs
• Using VBA FileSystemObject (FSO) in Excel - Easy Overview & Examples
• FileSystemObject in VBA – Explained

Option Explicit

Sub TextFile_FindReplace()
    'PURPOSE: Modify Contents of the 'Transformer Step Jump Period'  XML file
    
    ' Text file I/O constants
    Const ForReading = 1
    Const ForWriting = 2
    Const ForAppending = 8
    Const TristateTrue = -1
    Const TristateFalse = 0
    Const TristateUseDefault = -2

    ' Local variables
    Dim FSO As Object
    Dim BaseDir As String
    Dim BaseExt As String
    Dim FileObj As Object
    Dim FileList As String
    Dim FileContent As String
    Dim FirstTXT As String
    Dim SecondTXT As String
    Dim ThirdTXT As String
    Dim FourthTXT As String

    ' Define folders / files to process
    BaseDir = "C:\TX_XML"
    BaseExt = "xml"

    ' Create filesystem object
    Set FSO = CreateObject("Scripting.FileSystemObject")

    'Check if base folder exists
    If Not FSO.FolderExists(BaseDir) Then
        MsgBox "Base folder not found"
        Exit Sub
    End If

    ' Get replacement values from active sheet
    FirstTXT = Range("A1").Value
    SecondTXT = Range("A2").Value
    ThirdTXT = Range("A4").Value
    FourthTXT = Range("A5").Value

    FileList = ""

    ' Process all matching files in base folder
    FSO.GetFolder (BaseDir)
    For Each FileObj In FSO.GetFolder(BaseDir).Files
    
        ' Make sure the extension matches desired
        If LCase(FSO.GetExtensionName(FileObj.Path)) = LCase(BaseExt) Then
        
            ' Read entire file into variable
            With FSO.OpenTextFile(FileObj.Path, ForReading, False, TristateUseDefault)
                FileContent = .ReadAll
                .Close
            End With
        
            ' Process replacements
            FileContent = Replace(FileContent, FirstTXT, SecondTXT)
            FileContent = Replace(FileContent, ThirdTXT, FourthTXT)
        
            ' Write entire file back to disk
            With FSO.OpenTextFile(FileObj.Path, ForWriting, True, TristateTrue)
                .Write FileContent
                .Close
            End With
            
            ' Add to list of processed files
            FileList = FileList + vbNewLine + FileObj.Name
            
        End If
        
    Next

    ' Report files processed
    If FileList = "" Then
        MsgBox "No XML Files Found"
    Else
        MsgBox "The following XML files in " & BaseDir & " have been updated:" & vbNewLine & FileList
    End If

End Sub

Select allOpen in new window

---

»bp

@Mohsen Ahmadi,

Are you all set with this now, or do you need more help?  If all set, could you please close it out now.  If you need help with the question close process take a look at:

• How do I close my question?

---

»bp