Activate Workbook WITHOUT filename but using the extension. Please & Thank You

  • I have a many reports and we use a web interface to generate specific data. the file names are always randomly generated when exported, but they are always (read only) .xls files.


    I have tried other VBA commands that i have searched heavily but none work. using the "*.xls" doesnt seem to work. these have no file path, but are the only .xls workbook open when running this report.


    I would appreciate any assistance.

  • I assume that the xls workbooks are open. See if this macro helps:

    Code
    Sub Test()
        Dim WB As Workbook
        For Each WB In Workbooks
            If Mid(WB.Name, InStrRev(WB.Name, ".") + 1) = "xls" Then
                MsgBox (WB.Name & " has an 'xls' extension.")
            End If
        Next WB
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I assume that the xls workbooks are open. See if this macro helps:

    Code
    Sub Test()
    Dim WB As Workbook
    For Each WB In Workbooks
    If Mid(WB.Name, InStrRev(WB.Name, ".") + 1) = "xls" Then
    MsgBox (WB.Name & " has an 'xls' extension.")
    End If
    Next WB
    End Sub


    Just added WB.Activate after the MsgBox line and it was perfect! thank you very much.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!