Query CSV file using ADO - Run-time error '...is not a valid path' - help with connection string

[OldNick]

Hello there,

I have used ADO often to fetch Data from SQL Server and .xlsx Workbooks, but I can't seem to figure out what the problem with my connection string is here.

Error message : 'C:\Users\Firstname.Lastname\Desktop\temp.csv' is not a valid Path

Sub test()

Dim myConn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim myPath As String
Dim connStrng As String
Dim qryStrng As String
Dim ReiterName As String

myPath = "C:\Users\Firstname.Lastname\Desktop\temp.csv"

connStrng = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & myPath & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""

Debug.Print connStrng

myConn.Open connStrng  '<--- Errror

 

[bobsan42]

I strongly suspect that your username folder is not really called Firstname.Lastname
If this is really part of your code this is probably why you get an error.
you can use this:

myPath = Environ$("userprofile") & "\Desktop\temp.csv"

 

[OldNick]

you can use this:

myPath = Environ$("userprofile") & "\Desktop\temp.csv"

Thanks for the reply, but no luck yet. I'm sure there's a typo in my connection string, but can't seem to figure out where/what

 

[JertsConsulting]

It’s likely that last line of your connection string. I’ve run into issues when using multi-quotation marks when building connection strings, even when they look right in the immediate window.

I suggest you assemble the string as above but where you want quote marks use chr(34) instead.

conStrng = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & myPath & ";" & _
          "Extended Properties=" & chr(34) & “text;HDR=YES;FMT=Delimited" & chr(34)

The other thing that has sometimes tripped me up building connection strings is when I’ve copied the string from a website. Sometimes a quote mark copied from a website isn’t the same as one you’d type into the IDE. Thats probably not the case here, but thought I’d put it out there,