Excel VBA find and replace string in non text file and rename file

Long explanation:
I am attempting to do a find and replace on a text string in multiple files in multiple subfolders and rename files in the same folder structure at the same time for iterated file extensions.
Short explanation:
Anywhere the characters "ABC" appear need to be replaced with "XYZ" (excluding folder names)

Here is the code I have thus far:

Option Explicit
Sub Creo_rename()
    Const ForReading = 1
    Const ForWriting = 2
    Dim myend As Integer
    Dim irow As Integer
    Dim oldPNpath As String
    Dim newPNpath As String
    Dim oldPN As String
    Dim newPN As String
    Dim objfso As Object
    Dim objfile As TextStream
    Dim strtext As String
    Dim strnewtext As String
    
    Set objfso = CreateObject("Scripting.FileSystemObject")
    myend = Sheets(1).UsedRange.Rows.Count
    For irow = 4 To myend
        oldPNpath = Cells(irow, 1).Value
        Set objfile = objfso.OpenTextFile(oldPNpath, ForReading)
        
        strtext = objfile.ReadAll
        objfile.Close
        oldPN = Cells(irow, 2).Value
        newPN = Cells(irow, 4).Value
        newPNpath = Cells(irow, 3).Value
        strnewtext = Replace(strtext, oldPN, newPN)
        
        Set objfile = objfso.CreateTextFile(newPNpath, True, False)
        Set objfile = objfso.OpenTextFile(newPNpath, ForWriting, True)
        objfile.WriteLine (strnewtext)
        objfile.Close
        
        Kill oldPNpath
        Cells(irow, 5) = "check"
    Next
End Sub

Attached is my source data as well.


I am having multiple problems with this code. But rarely the same one twice in a row. Sometimes the error is "out of memory" sometimes it is "permission denied". The code has managed to run up to line 750 (out of +1700) but mostly it errors out on the first couple of lines. I am not even sure I am if this possible being that the files I am dealing with are not true ".txt" files, even though you can open them with a text editor and preform a find and replace on them.
Any help that can be given is greatly appreciated.



Running:
Windows 7, sp1
excel 2010

Ok, now that I have had the night to think about it I do not believe that the logic of my code will work for my situation.

This is actually an attempt to rename a family of Creo/Parametric 3D CAD models/assemblies/drawings. If anyone is familiar with the way Creo (previously Pro/Engineer) works, inside the assembly and drawing files is the potential for thousands of references to other part (.prt) and assembly (.asm) files. So if you need to change the file name of a part or assembly it is not as simple as renaming the file, you have to update the references in the file as well. This is very labor intensive using the Creo software itselft as you have to have all the files open at the sametime and Creo limits you to having 15 items open at one time.

I need a way to take a set of characters "ABC-123" and go thru each folder and subfolder and rename any files and update any references in those files to the new number "XYZ-789".
Please let me know if this doesn't make any sense.
Again any help is greatly appreciated.

Really....Nobody?

Ok....let me try this from another direction.

Is what I am trying to do even in possible with VBA or do I need to switch to different language/method, maybe VBScript or Python? Obviously I can't get help with that here but someone has to at least have an opinion......

Maybe this can at least begin you on the correct path to the solution...
http://stackoverflow.com/questions/1...eas-close-file

Thanks for the link dlsmith36. I found that in my initial searching and did used it as a starting point. I appreciate the input.

Here is an update.
I set up a couple of extra for loops to iterate thru the subfolders and each file and knock it against the part number listing I have in the excel sheet. And it seemed to be working when I stepped thru it but when I played the code from the beginning I get an "Input past end of file" error 62 on line

strtext = objfile.ReadAll.

As I understand this error it means that excel thinks I am trying to read or assign something after an "end of file" character.

This tells me that excel can't read the file as it is not pure text. Since I have already wasted too much time on this thing anyways and no one else seems to know the answer either. I am going to go back to the tried and true method of beating my head against a wall for two days and rename the files manually (and yes I realize that if I just done it that way in the first place I would have been done already)

I am posting the last of my code, maybe someone will find it useful for something completely unrelated.

Sub FileLoop()
Dim myobject As Object
Dim mysource As Object
Dim myfile As Object
Dim irow As Integer
Dim mySourcePath As String
Dim myend As Integer
Const ForReading = 1
Const ForWriting = 2
Dim oldPNpath As String
Dim newPNpath As String
Dim oldPN As String
Dim newPN As String
Dim objfso As Object
Dim objfile As TextStream
Dim strtext As String
Dim strnewtext As String
Dim includesubfolders As Boolean
Dim mysubfolder As Folder
Dim myfilename As String
Dim newfilename As String

        mySourcePath = "C:\Users\bhale\Desktop\Self Test  Adapters"
        Set myobject = New Scripting.FileSystemObject
        Set mysource = myobject.GetFolder(mySourcePath)
        'Set myfile = mysource.Files
        myend = ActiveSheet.UsedRange.Rows.Count
        Application.ScreenUpdating = False
        'On Error Resume Next
       For Each mysubfolder In mysource.SubFolders
        Set mysource = myobject.GetFolder(mysubfolder.Path)
        Set myfile = mysource.Files
            For Each myfile In mysource.Files
                Set objfile = myobject.OpenTextFile(myfile, ForReading, False, False)
                strtext = objfile.ReadAll '-------------------------------------------------error was here
                objfile.Close
                
                    For irow = 2 To myend
                        oldPN = Cells(irow, 2).Value
                        newPN = Cells(irow, 4).Value
                        strnewtext = Replace(strtext, oldPN, newPN)
                        myfilename = myfile.Name
                        newfilename = Replace(myfilename, oldPN, newPN, 1, -1, vbTextCompare)
                    Next
                Set objfile = myobject.OpenTextFile(myfile.ParentFolder & "\" & newfilename, ForWriting, True)
                objfile.Write strnewtext
                objfile.Close
                
                Kill myfile
                Cells(irow, 5) = "check"
                
            Next
        Next
End Sub