I need to connect to CSV file in my downloads folder using SQL query from Excel VBA. This is the code I found from internet
I have never worked with SQL in excel and I am confused about this code, I get an error saying provider not found it may not be installed correctly. So I wanted to know How can I fix that and also should I mention that to connect to CSV file with SQL queries with VBA?
Sub SQLconnectCSV() Dim xlcon As ADODB.Connection Dim xlrs As ADODB.Recordset Set xlcon = New ADODB.Connection Set xlrs = New ADODB.Recordset csvfile = Environ$("USERPROFILE") & "\Downloads\data.csv" xlcon.Provider = "Microsoft.Jet.OLEDB.4.0" xlcon.ConnectionString = "Data Source=" & csvfile & ";" & _ "Extended Properties=""text;HDR=Yes;FMT=Delimited;""" xlcon.Open SQLQuery = "SELECT * FROM [data.csv]" xlrs.Open SQLQuery, xlcon Worksheets("MortgageDefaultData").Cells(1, 1).CopyFromRecordset xlrs xlrs.Close xlcon.Close Set xlrs = Nothing Set xlcon = Nothing End Sub
I have never worked with SQL in excel and I am confused about this code
xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"