none
Find full name of a file when using * wild card in filename RRS feed

  • Question

  • Function FileExists(FileSpec As String) As Boolean
    FileExists = Dir(FileSpec) <> ""
    End Function
    

    This simple function appears to verify that the file "balance*.csv" exists, where the * represents a dozen or so additional characters that change with every download. FileSpec includes the full path; and there are no other "balance*.csv" files in that path. However, when I run

    Workbooks.Open (FileSpec)

    it fails, returning "Method 'Open' of object 'Workbooks' failed" (run-time error 1004).

    Is there a relatively simple way for VBA to find the full name of and then open "balance*.csv"?

    Thanks in advance for any help or advice.


    Saturday, January 13, 2018 7:03 PM

Answers

  • Dir only returns the filename, not the path, so it's better to specify the path. For example:

    Function FileOpen(ByVal FilePath As String, ByVal FileSpec As String) As Workbook
        Dim strName As String
        If Right(FilePath, 1) <> "\" Then
            FilePath = FilePath & "\"
        End If
        strName = Dir(FilePath & FileSpec)
        If strName <> "" Then
            Set FileOpen = Workbooks.Open(FilePath & strName)
        End If
    End Function

    This function returns Nothing if there is no matching file, and a Workbook object otherwise.

    Example of use:

        Dim wbk As Workbook
        Set wbk = FileOpen("C:\Excel", "Balance*.csv")
        If wbk Is Nothing Then
            MsgBox "Couldn't find file", vbExclamation
            Exit Sub
        End If
        ...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Rabnud Saturday, January 13, 2018 10:03 PM
    Saturday, January 13, 2018 8:29 PM