Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find/Replace in closed workbooks (all versions)

    hello all,

    i have a problem with a lot of data to be changed in closed workbooks. sometimes our projectdata will change, so that no customer can book
    its times on the older code. Now, i should find and replace all the data in the timesheet with the old psp (project structure plan) to the new code (all structurcodes in row 6 in all worksheets). ->
    P.100.004.001 to P.100.007.001 (as an example)


    one method is to find/replace the data after importing it, but then there is a brake - i only changed the imported data....

    so, is it possible to change the psp data in all closed worksheets in a folder -(about 40 - 60 workbooks) -> for example (the new data in aworksheet in cell A5, and the old data in cell B5)??

    stefan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find/Replace in closed workbooks (all versions)

    You can't replace data in a closed workbook. You'd have to open each workbook in turn, replace the data, then close the workbook. If you search this forum for do while loop dir you'll find many examples of code that loops through all workbooks in a folder.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find/Replace in closed workbooks (all versions)

    ah, You mean, i should use the code, You sent me for the import of excel files in a folder.
    thanks,
    stefan

  4. #4
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find/Replace in closed workbooks (all versions)

    ok, i modified and started the code, but unfortunately it only changes the strings/values in the first worksheet in each workbook (see my example)..
    i have no idea why only the first sheet.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find/Replace in closed workbooks (all versions)

    Since you don't specify what Cells belongs to, it always refers to Cells on the active sheet. Change the line

    Cells.Replace What:=FndSource, Replacement:=FndTarg

    to

    wshSource.Cells.Replace What:=FndSource, Replacement:=FndTarg

  6. #6
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find/Replace in closed workbooks (all versions)

    thanks again, Hans and I wish You a nice day
    stefan,

Posting Permissions

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