Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Variable name in running macros (XL97)

    I have some code that opens over 100 files... I want to be able to use a variable name so that I can force a macro to run in each one of those files. here is the code..

    Sub FreezeControl()
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    WorkFile = ActiveWorkbook.Name
    MyFile = Dir("2001JDPControl*.xls")
    Do While Len(MyFile) > 0
    JDPFile = MyFile
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Workbooks.Open FileName:=JDPFile, updatelinks:=3
    Windows(JDPFile).Activate
    Application.Run "'!UpdateData"
    ActiveWorkbook.Save
    ActiveWindow.Close
    Windows(WorkFile).Activate
    MyFile = Dir()
    Loop
    End Sub

    Where it says Application.Run "'!UpdateData" I need the filename in there. Leaving it out tells me the system can't find the macro.
    Putting it in... Application.Run "'JDPFile'!UpdateData" gives me a compile error message.
    TIA for pointing me in the right direction.

    Dennis

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Variable name in running macros (XL97)

    Hi Dennis,
    Try:
    Application.Run "'" & JDPFile & "'!UpdateData"
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Variable name in running macros (XL97)

    JPDFile seems to be a variable so if you change the code to read <pre> Application.Run JDPFile &

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

    Re: Variable name in running macros (XL97)

    Andrew's code should have read:

    <pre> Application.Run JDPFile & "!UpdateData"
    </pre>

    Legare Coleman

Posting Permissions

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