+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    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:

    Please Login or Register  to view this content.
    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

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

    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.

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

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

    Really....Nobody?

  4. #4
    Registered User
    Join Date
    06-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

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

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

  5. #5
    Registered User
    Join Date
    05-31-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    17

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

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

  6. #6
    Registered User
    Join Date
    06-27-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel VBA find and replace string in non text file and rename 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
    Please Login or Register  to view this content.
    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.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1