+ Reply to Thread
Results 1 to 4 of 4

Auto run macro on all workbooks/sheets

  1. #1
    Registered User
    Join Date
    10-16-2006
    Location
    Shreveport, LA
    Posts
    32

    Auto run macro on all workbooks/sheets

    Hey all,

    I have 100's of workbooks that have a mispelled word in a cell. Some of the workbooks have more than one sheet.

    I have written a macro that will correct the mispelling, and now would like to have the macro run automatically everytime I open a workbook, I would like for it to check all the sheets, but if not, then whatever sheet the workbook opens to will be fine.

    Here is the macro...

    Sub mySpelling()

    ' Macro to correct misspelled word , Quanity, in B22 in hundreds of spreadsheets.

    Dim myWord
    Range("b22").Select
    myWord = ActiveCell
    If myWord = "Quanity" Then
    ActiveCell.FormulaR1C1 = "Quantity"
    End If

    End Sub

    I have done a search and have seen about "personal.xls", that does not seem to work. Thinking that I will have to add something to XLStart folder but not sure.

    Any help will be appreciated!

    ww
    Last edited by mikerickson; 10-16-2008 at 06:20 PM.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Try this macro


    It gets a xls file name from c:\Temp - change Path to suit.
    Opens a file
    Checks b22 on every sheet and corrects spelling if required
    Closes workbook - saving changes
    repeats process until all files have been done

    Suggest test run on a backup copy of some ofyour files and verfy all is correct before running over all of the files.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-16-2006
    Location
    Shreveport, LA
    Posts
    32
    mudraker,

    Macro works great! First time would not run, found a typo, now works great. Typo was in For Each statement, I changed Workbook to Workbooks. Here is the finished product...

    Please Login or Register  to view this content.
    And now let me move to the next step of the learning curve. I understand most of the code except for " sFile$ = Dir ", and I do understand that sFile is a variable and you are populating it with the file names in the directory. What I don't understand is how the code only goes through the directory once, how does it know to stop at the last file and not to loop?

    Also, why the $ signs after the variable names?

    I appreciate you taking the time. Thanks

    ww

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    I do not know how or why the dir command ends up setting the variable with a blank entry - but it does after it has been through every file so by using Do Until sFile = "" we can stop the loop after every file has been processed.

    Microsoft help file & web site does not appear to go into details of how this works.

    AS for using the $ sign at the end of a variable - it is a hang over from old programming days when using the $ forced a variable as a string type. I was taught many years ago to use it and most of the time still do - If you declare the variables correctly within the macro there is no need to use the $ symbol

    Have done a bit of chasing up on the net it looks like I will stop using the $ symbol.

    http://www.daniweb.com/techtalkforums/thread17511.html

+ 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