Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    autofill macro?? (xls 2000)

    Hello all,

    I have a job that is really repetitive and am trying to finish it faster.

    I have to go through about 200 wbks and do the exact same thing which
    goto a specified sheet. set autofilter to all, then select a cell and drag the autofill all the way down from AP4 to AP280 so a certain formula is updated through all the cells in the ap column the set the autofilter to non blanks, run a macro exit and move on to the next wbk and do the same thing.

    I have this code right below which sort of does what I want but I would like to add to it and make it more effiecent.

    i would like to take all the workbooks that need fixing and drop them in a folder. This is so that the macro know where to find each one. Then have a loop which can roll through each one run the process and exit out of it and onto the next one

    anyone have any ideas?

    Thanks


    Option Explicit

    Sub fixer()
    '
    ' fixer Macro

    Sheets("Pricing").Select
    Selection.AutoFilter Field:=1
    ActiveWindow.SmallScroll ToRight:=34
    Range("AP4").Select
    Selection.AutoFill Destination:=Range("AP4:AP280"), Type:=xlFillDefault
    Range("AP4:AP280").Select
    Sheets("ProgramChecklist").Select
    Application.Run _
    "SaveProgramToLDrive"
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autofill macro?? (xls 2000)

    Patrick

    I found this code at Ozgrid.com

    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next

    Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
    .NewSearch
    'Change path to suit
    .LookIn = "C:mydatasource"' change this to your folder
    .FileType = msoFileTypeExcelWorkbooks
    '.Filename = "Book*.xls"

    If .Execute > 0 Then 'Workbooks in folder
    For lCount = 1 To .FoundFiles.Count 'Loop through all.
    'Open Workbook x and Set a Workbook variable to it
    Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">'DO YOUR CODE HERE</span hi>

    wbResults.Close SaveChanges:=True

    Next lCount
    End If
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End Sub
    Jerry

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    Try placing all 200 workbooks in one sub-directory and run the following code to open each file. The code loops through each file in the sub-directory so be sure you only have the Excel files you want to modify there.

    Sub GetFiles()
    Dim i As Long
    Dim strFileName As String
    Dim oPath As String

    oPath = "E:YourPathHere"

    i = 0
    strFileName = Dir("" & oPath & "*.*", vbHidden)
    While strFileName <> ""
    Workbooks.Open Filename:=oPath & strFileName
    'More code here

    ActiveWorkbook.Close

    i = i + 1
    strFileName = Dir()
    Wend
    End Sub


    Regards,
    John

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autofill macro?? (xls 2000)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    It appears that "SaveProgramToLDrive" may be a seperate routine <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    If you remove this the wb's will be opened in the folder they sit, make the changes and then save them in that current folder. Try commenting it out and re-run it by stepping through with F8 in the VB editor.
    Jerry

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    so if i understand you reply, its not possible to call on a macro in each workbook called "SaveProgramToLDrive"
    and run it? maybe it needs the dirctory path of the file and then macros name?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autofill macro?? (xls 2000)

    May not be necessary just copy the SaveProgramToLDrive routine below the code I provided, at the right point it will "jump" out run SaveProgramToLDrive and then continue into my code.
    Jerry

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    i was thinking of adding this
    Application.Run "'filename.xls'!SaveProgramToLDrive"

    where somehow i update the file name each time through the loop so it calls the appropiate macro?
    maybe make a varible which gets the .xls filename and concatenates it in the code??

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    Your code is on the right track, but I see a number of problems:

    1- The variable i is never used for anything in the code and is therefore unnecessary.

    2- When you assign the string to oPath you put a backslash at the end of the path string. In the Dir function you concatenate a second one to the end of the path.

    3- The file name argument to the Dir function is *.*. This will find all files, even any files that are not .xls files. I think it would be better to use *.xls.

    4- Your Dir function finds only hidden files. I think you meant the second argument to be vbNormal.

    I would use this modification of your code:

    <code>
    Sub GetFiles()
    Dim oWB As Workbook
    Dim strFileName As String
    Dim oPath As String

    oPath = "E:YourPathHere"

    strFileName = Dir(oPath & "*.xls", vbNormal)
    While strFileName <> ""
    Set oWB = Workbooks.Open(Filename:=oPath & strFileName)
    'Your code here

    oWB.Close

    strFileName = Dir()
    Wend
    End Sub
    </code>
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    Hi legare,

    I loaded you code in to the module and use my extra code listed in the first post,

    I have come to the same problem Jazzy was having with the macro not being called in the workthat is open. Maybe the macro needs to have the filename in front of it
    something like:
    "'filename!'macro"
    ?

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    Ok Jezza,

    This is the code that works for me
    bluerayz?

    Option Explicit
    Sub fixer()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next

    Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
    .NewSearch
    'Change path to suit
    .LookIn = "Cutin" ' change this to your folder
    .FileType = msoFileTypeExcelWorkbooks
    '.Filename = "Book*.xls"

    If .Execute > 0 Then 'Workbooks in folder
    For lCount = 1 To .FoundFiles.Count 'Loop through all.
    'Open Workbook x and Set a Workbook variable to it
    Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

    Sheets("Pricing").Select
    Selection.AutoFilter Field:=1
    ActiveWindow.SmallScroll ToRight:=34
    Range("AP4").Select
    Selection.AutoFill Destination:=Range("AP4:AP280"), Type:=xlFillDefault
    Range("AP4:AP280").Select
    Sheets("ProgramChecklist").Select
    Application.Run _
    "SaveProgramToLDrive"

    wbResults.Close SaveChanges:=True

    Next lCount
    End If
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End Sub

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    What is the code that is in that macro? It might be easier to include the code into this macro. If that can't be done, then you will have to include the workbook name in the Run command. With my code you could do that something like this:

    <code>
    Application.Run oWB.Name & "SaveProgramToLDrive"
    </code>
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    Sorry i tried to fix my post question and deleted it,

    Thanks Jezza, I can make use of this too

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    I have posted the code i am using and have ran it but de###### stops at
    Application.Run oWB.Name & "SaveProgramToLDrive"
    and tells me the macro cant be found.


    Sub GetFiles()
    Dim oWB As Workbook
    Dim strFileName As String
    Dim oPath As String

    oPath = "Cathname"

    strFileName = Dir(oPath & "*.xls", vbNormal)
    While strFileName <> ""
    Set oWB = Workbooks.Open(Filename:=oPath & strFileName)
    Sheets("Pricing").Select
    Selection.AutoFilter Field:=1
    ActiveWindow.SmallScroll ToRight:=34
    Range("AP4").Select
    Selection.AutoFill Destination:=Range("AP4:AP280"), Type:=xlFillDefault
    Range("AP4:AP280").Select
    Sheets("Calender").Select
    Application.Run oWB.Name & "SaveProgramToLDrive"

    oWB.Close

    strFileName = Dir()
    Wend
    End Sub

  14. #14
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: autofill macro?? (xls 2000)

    hmm well it will not work for you because you need to add the catenations to it specifing the correct macro name so add this to leagares code and it should work. also you should be getting vb messages or something asking you to save over the files so i added something to handle this, I havent ran this as a test or anything so it may not work but give it a try.
    Sub GetFiles()
    Dim oWB As Workbook
    Dim strFileName As String
    Dim oPath As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    oPath = "Cathname"

    strFileName = Dir(oPath & "*.xls", vbNormal)
    While strFileName <> ""
    Set oWB = Workbooks.Open(Filename:=oPath & strFileName)
    Sheets("Pricing").Select
    Selection.AutoFilter Field:=1
    ActiveWindow.SmallScroll ToRight:=34
    Range("AP4").Select
    Selection.AutoFill Destination:=Range("AP4:AP280"), Type:=xlFillDefault
    Range("AP4:AP280").Select
    Sheets("Calender").Select
    Application.Run "'"& oWB.Name & "'!SaveProgramToLDrive"

    oWB.Close

    strFileName = Dir()
    Wend
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End Sub

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autofill macro?? (xls 2000)

    Thanks Matix and Leagare and Jezza for your help it finally works,

    i do get the message boxesMatix, your code seem to not prevent them but I will live with it since it has made my live a lot less tedious

    thanks

Posting Permissions

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