CODEWITHSUNDEEP
excelvbaoledb
SmileyFtW
SmileyFtW

Reputation: 336

OLEDB Connection fails with Invalid Path

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?

Upvotes: 0

Views: 544

Answers (1)

CDP1802
CDP1802

Reputation: 16194

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

Upvotes: 0

Related Questions