Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question VBA Application.run command for doc with no consistent name

    Hi,

    I am creating a macro that has to open a .xls file in a workbook with the latest creating date. This part of the macro works!

    Then the macro need to run the macro in the, just opened .xls file. How can i specify the name of this workbook (at the location of the '???') ? The macro name is always the same "data_distribution".


    Sub test_open_edf_data()

    'get data from the newest xls data sheets and run the macro

    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim Cnt As Long

    'Specify the path to the folder (change the path accordingly)
    MyPath = "I:\documents\VBAdata\edfdata\"

    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.xls", vbNormal)

    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0

    'Assign the date/time of the current file to a variable
    LMD = FileDateTime(MyPath & MyFile)

    'If the date/time of the current file is greater than the latest
    'recorded date, assign its filename and date/time to variables
    If LMD > LatestDate Then
    LatestFile = MyFile
    LatestDate = LMD
    Cnt = Cnt + 1
    End If

    'Get the next Excel file from the folder
    MyFile = Dir

    Loop

    'Open the latest file
    If Cnt > 0 Then
    Workbooks.Open MyPath & LatestFile
    Else
    MsgBox "No files were found in this folder...", vbExclamation
    End If

    Application.Run " '???'!edf_data_distribution"

    ActiveWorkbook.Close


    End Sub




    I could imagine that it will be something like 'current.workbook' or 'active.workbook' but it will not work. Thanks in advance!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,503
    Thanks
    212
    Thanked 853 Times in 785 Posts
    Mattie,

    The syntax you want is: Application.Run "Test\MyMessage.xls!MyMessage"
    Note: Test is a directory directly under my Excel directory which is the default. You may need to fully specify the full path to the directory to get it to work, i.e.: d:\path\workbookname.xls!macroname.

    In your case MyPath & LatestFile & "!edf_data_distribution"
    Last edited by RetiredGeek; 2011-12-09 at 08:50.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,942
    Thanks
    0
    Thanked 94 Times in 90 Posts
    FYI, there should be no need to specify a path for an open file:
    Code:
    Application.Run "'" & latestFile & "'!edf_data_distribution"
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. #4
    New Lounger
    Join Date
    Nov 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG, can you please give it in exact order? I tried it in several ways also: MyPath & LatestFile & "!edf_data_distribution" but it would not run..

  5. #5
    New Lounger
    Join Date
    Nov 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Rory thanks, it works!!

Posting Permissions

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