VBA Open most recent file LIKE in directory

[Lres81715]

I've been at this for hours with google sending me to obsolete and older code that no longer works for office 2013. *sigh

Ok, so I have a simple request. I need to look in Dir$("P:\path"), find most recent file of "Internal-EOM-201*" & ".xlsx", open it and define it for later reference.

This bit of code seems the most likely candidate to what I need to do, but application.filesearch no longer works past 2007 apparently. Now I'm stuck.

    With Application.FileSearch        .NewSearch
        .LookIn = "P:\path"
        .Filename = "Internal-EOM-201*" & ".xlsx"""
        If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then
            For i1 = 1 To .FoundFiles.Count
                Workbooks.Open (.FoundFiles(i1))
            Next i1


        End If


    End With

I don't need anything fancy, the path won't ever change and the starting file name won't ever change. Just need to always open the most recent file or if none is present (it gets archived once a week), return MsgBox "No report found".

 

[Lres81715]

Update: I've literally been at this for hours.
I've gotten this far.

    Dim xpath       As String    Dim xfile       As String
    Dim InternalWB  As Workbook
    Dim LatestFile  As String
    Dim LatestDate  As Date
    Dim LMD         As Date


    xpath = "P:\Asset Management\_ReportGenerator\" 'path


    If Right(xpath, 1) <> "\" Then xpath = xpath & "\"
        xfile = Dir$(xpath & "Internal-EOM-201*" & ".xlsx", vbNormal) '* Wildcard is for date/hour/min parameters.


    If Len(xfile) = 0 Then
        MsgBox "No Current Internal-EOM Reports found... Run Report Generator", vbExclamation
        Exit Sub
    End If


    Do While Len(xfile) > 0
        LMD = FileDateTime(xpath & xfile) 'process for finding all files above using FileDateTime function. loops thru until most recent date AND name match "like" above
        If LMD > LatestDate Then
            LatestFile = xfile
            LatestDate = LMD
        End If
        xfile = Dir
    Loop
    
        Set InternalWB = Workbooks.Open(xpath & LatestFile) 'Opens file above.

This works for opening the most recent file and then giving it the "InternalWB" name for my purposes.
I need to now create code that will help me determine if Said File determined above is already open. Right now, I get a pop-up alert telling me "file" is already open. Reopening will cause any changes ... Do you want to reopen "file"? Yes No

I get a Run-time error '1004':
Method 'Open' of object 'Workbooks' failed.

I need help as I just don't get how to use that popular IsWorkbookOpen function I see floating about. It doesn't seem to work on a filename that isn't constant.

 

[tonyyy]

You might give this a try...

[COLOR=#808080]Loop[/COLOR]
If Workbooks(LatestFile) Is Nothing Then
    [COLOR=#808080]Set InternalWB = Workbooks.Open(XPath & LatestFile) 'Opens file above.[/COLOR]
    Else
    MsgBox LatestFile & " is already open."
End If

Cheers,

tonyyy

 

[Lres81715]

Hello Tonyyy,

Thanks for responding. (long weekend out sick, sorry for taking so long to get back to you)

I tried your code and I'm still getting a Run-time error '9': Subscript out of range.
I've played with the code a bit more this morning and I haven't made any headway. Problem still there

*Edit* to clarify, I'm getting the error here

    If Workbooks(LatestFile) Is Nothing Then

 

[tonyyy]

I've made a few tweaks to the code...

[COLOR=#a9a9a9]Sub MostRecentFile()

Dim xpath       As String
Dim xfile       As String
Dim InternalWB  As Workbook
Dim LatestFile  As String
Dim LatestDate  As Date
Dim LMD         As Date
[/COLOR]Dim wb          As Workbook

Set wb = Nothing[COLOR=#a9a9a9]
xpath = "P:\Asset Management\_ReportGenerator\" 'path
If Right(xpath, 1) <> "\" Then xpath = xpath & "\"
xfile = Dir$(xpath & "Internal-EOM-201*" & ".xlsx", vbNormal) '* Wildcard is for date/hour/min parameters.

If Len(xfile) = 0 Then
    MsgBox "No Current Internal-EOM Reports found... Run Report Generator", vbExclamation
    Exit Sub
End If

Do While Len(xfile) > 0
    LMD = FileDateTime(xpath & xfile) 'process for finding all files above using FileDateTime function. loops thru until most recent date AND name match "like" above
    If LMD >= LatestDate Then
        LatestFile = xfile
        LatestDate = LMD
    End If
    xfile = Dir
Loop

[/COLOR]On Error Resume Next
Set wb = Workbooks(LatestFile)
On Error GoTo 0

If wb Is Nothing Then
    Set InternalWB = Workbooks.Open(xpath & LatestFile) 'Opens file above.
    Else
    MsgBox LatestFile & " is already open."
End If[COLOR=#a9a9a9]

End Sub[/COLOR]

Please let me know if this works for you.

Thanks,

tonyyy

 

[Lres81715]

Tonyyy,

So far that works great for opened files. I have a really stupid question but how can I change the Else to set the already opened file to InternalWb?
The rest of my macro already has 80+ references to it.

So far, I tried

    If wb Is Nothing Then        
        Set InternalWB = Workbooks.Open(xpath & LatestFile, ReadOnly:=True) 'Opens file above.
        Else
    set InternalWB = Workbooks(xpath & LatestFile) 'Run-time error '9': Subscript out of range
    set InternalWB = wb 'gives me no error but nothing referencing InternalWB works.  I get an automation error because xpath was not present.
    set InternalWB = xpath & wb 'Run-time error '438: Object doesn't support this property or method

any suggestions as to how to reference that already opened file to be set as InternalWB? I'm just out of my element with VBA syntax.
Thanks btw.

 

[tonyyy]

If wb Is Nothing Then
    Set InternalWB = Workbooks.Open(xpath & LatestFile) 'Opens file above.
    Else
'    MsgBox LatestFile & " is already open."
    Set InternalWB = Workbooks(LatestFile)
    MsgBox "InternalWB: " & InternalWB.Name
End If

 

[Lres81715]

tonyyy!

You are a lifesaver. I can't believe I didn't think of that when you referenced just that in an earlier post.
Thank again. My VBA knowledge gets me only so far until I fall into an endless spiral of ARGH!! JUST WORK!

So far it's working great! Thanks again

 

[tonyyy]

You're welcome. Glad it's working.

tonyyy