none
MACRO - How to open a file with unknown extension? (xls, xlsx, xlsm) RRS feed

  • Question

  • Hi there,

    I am trying to open file in my MACRO script as follows:

    Dim source As String
    source = ThisWorkbook.Path & "\Source file\Source" & "*.xls*"

    But it turns out saying there is no such a file.

    What I mean is I would like to open that "Source" file, no matter it is in ".xls" or ".xlsx" or ".xlsm".
    Is there any code may help?

    Thanks in advance!
    • Edited by Jackynck Sunday, September 9, 2012 7:36 AM
    Sunday, September 9, 2012 7:36 AM

Answers

  • Try

        Dim source As String
        source = Dir(ThisWorkbook.Path & "\Source file\Source" & "*.xls*")
        If source = "" Then
            MsgBox "No file found.", vbCritical
            Exit Sub
        Else
            source = "ThisWorkbook.Path & "\Source file\" & source
        End If


    Regards, Hans Vogelaar

    • Marked as answer by Jackynck Sunday, September 9, 2012 9:13 AM
    Sunday, September 9, 2012 8:51 AM

All replies

  • Try

        Dim source As String
        source = Dir(ThisWorkbook.Path & "\Source file\Source" & "*.xls*")
        If source = "" Then
            MsgBox "No file found.", vbCritical
            Exit Sub
        Else
            source = "ThisWorkbook.Path & "\Source file\" & source
        End If


    Regards, Hans Vogelaar

    • Marked as answer by Jackynck Sunday, September 9, 2012 9:13 AM
    Sunday, September 9, 2012 8:51 AM
  • Oh I see
    I have tried Dir but I forget the last line that you have put.
    Thanks Hans!
    Sunday, September 9, 2012 9:14 AM