Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro on all open books (XP Sp2)

    I have several sets of workbooks that need a simple edit. I'd like to be able to do a global search and replace on all open workbooks. I can set up a macro to work on a single book and I can run it for each book but what I'd like to do is open all the books, click on the macro and have it run on all books.

    Any suggestions would be appreciated.

    Thanks

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

    Re: Macro on all open books (XP Sp2)

    Assuming that your macro works on the active workbook, you could do this to run it for all open workbooks (I'm not sure what you mean by "open all the books"):

    Sub EditAll()
    Dim wbk As Workbook
    For Each wbk In Workbooks
    wbk.Activate
    ' substitute name of macro that acts on one workbook
    Call EditOne
    Next wbk
    End Sub

    Perhaps not the most elegant method, but it requires no changes to an existing macro.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro on all open books (XP Sp2)

    Thanks Hans, just what I was looking for. All the files I am working on are in a single folder (ie., all the files in folder 'x' need to be edited) What I planned to do was to use file-open, then ctrl-a to select them all, then open. Once they are all open, I'll run the macro then I'll use close all and confirm the save one by one. Not pretty, but it will get the job done a lot faster then running the macro book by book.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro on all open books (XP Sp2)

    I pasted the code and get a syntax error on the line:

    Set wbk = Workbooks.OpenText Filename:=strFolder & stFile

    ??

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

    Re: Macro on all open books (XP Sp2)

    Edited by HansV to correct error.

    You can loop through all workbooks in a folder using the Dir function. For example:

    Sub ProcessFiles()
    ' Folder to process - must include trailing backslash
    Const strFolder = "C:Test"
    Dim strFile As String
    Dim wbk As Workbook

    On Error GoTo ErrHandler

    strFile = Dir(strFolder & "*.xls")
    Do While Not strFile = ""
    ' Open workbook
    Set wbk = Workbooks.Open(Filename:=strFolder & strFile)
    ' Code to process file goes here
    ' ...
    ' Close and save the workbook
    wbk.Close SaveChanges:=True
    ' And on to the next one
    strFile = Dir
    Loop

    ExitHandler:
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

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

    Re: Macro on all open books (XP Sp2)

    Sorry, I forgot something when copying the code from another post and modifying it. It should be

    Set wbk = Workbooks.Open(Filename:=strFolder & strFile)

    instead of

    Set wbk = Workbooks.OpenText Filename:=strFolder & stFile

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro on all open books (XP Sp2)

    As an alternative you could use my Flexfind utility, which is capable of doing srearch and replace on all open files. It has one advantage: it can also look in Excel's objects like chart titles and headers and footers:

    http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro on all open books (XP Sp2)

    Jan,
    Thanks for the link. Hans got me out of my problem today but I'll put yours into my armoury for the future.

Posting Permissions

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