Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Macro with changing file name (Excel 2003)

    Hi loungers....I am using the following macro to cause a worksheet to print:

    Sub Print_schedule()
    '
    ' Print_schedule Macro
    ' Macro recorded 3/14/2007 by David J. McNab
    ''
    Application.Run "'{file name}.XLS'!AF_locations"
    Selection.AutoFilter Field:=6, Criteria1:="*"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowSorting:=True, AllowFiltering:=True
    End Sub

    I have to be able to change the file name (eg: from 2008 to 2009...or from Location1 to Location 2)...the macro requires the file name, and yet I don't want to have to adjust the {file name} in the macro each time I rename the workbook......is there a way to have a generic name or description that is constant within the macro, but still allows each workbook to have it's own file name?

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

    Re: Macro with changing file name (Excel 2003)

    Where is the AF_locations macro stored? In the same workbook that contains Print_schedule, or in another workbook?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro with changing file name (Excel 2003)

    ..same workbook...

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

    Re: Macro with changing file name (Excel 2003)

    In that case, you should be able to replace the line

    Application.Run "'{file name}.XLS'!AF_locations"

    with

    Call AF_locations

    This doesn't contain any reference to the name of the workbook.

Posting Permissions

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