OLEDB Connection fails with Invalid Path

Question

Using Application.FileDialog(msoFileDialogPicker) to select a file to make an OLEDB connection in Excel VBA. The full filename returned (dummied-up to protect the guilty) is: D:\MyUserName\Documents\SubFolder1\SubFolder2\SubFolder3\My Filename.csv

ConnectionString is: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyUserName\Documents\SubFolder1\SubFolder2\SubFolder3\My Filename.csv;Extended Properties='text;HDR=YES;FMT=Delimited';

Connection.Open ConnectionString fails, reporting the path is not valid. I can copy/paste the filename part of ConnectionString (less the file name) into the address bar of File Explorer and it successfully goes to the folder.

Putting the filename part of ConnectionString (including the filename) into the address bar opens the file.

Any thoughts/suggestions?

CDP1802 CDP1802 · Accepted Answer · 2021-08-13T10:23:59

The folder is a database, the files are the tables.

Sub test()

    Dim conn As ADODB.Connection, rs As ADODB.Recordset
    Dim sConn As String, SQL As String

    Const FOLDER = "D:\MyUserName\Documents\SubFolder1\SubFolder2\SubFolder3\"
    Const TABLE = "My Filename.csv"

    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & FOLDER & ";" & _
            "Extended Properties='text;HDR=YES;FMT=Delimited';"

    SQL = "SELECT * FROM [" & TABLE & "]"

    Set conn = New ADODB.Connection
    With conn
        .Open sConn
        Set rs = .Execute(SQL)
        Sheet1.Range("A1").CopyFromRecordset rs
        .Close
    End With
    MsgBox "OK"

End Sub

OLEDB Connection fails with Invalid Path

1 Answers