CODEWITHSUNDEEP
vbaexcelado
user2888590
user2888590

Reputation: 67

Copying ADO recordset into excel worksheet

I'm trying to open a CSV file and query it and return the results into column A of the second worksheet of "ThisWorkbook". I'm not getting any errors so I do not see why it is not copying the record set into excel.

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

Dim currentDataFilePath As String
Dim currentDataFileName As String

 Dim nextRow As Integer

currentDataFilePath = "C:\Users\M\folder\"
currentDataFileName = "csv-file"

con.Open "Provider=Microsoft.JET.OLEDB.4.0;" & _
           "Data Source=" & currentDataFilePath & ";" & _
           "Extended Properties=""text;HDR=NO;FMT=Delimited;IMEX=1"""


'rs.ActiveConnection = con

 rs.Open "SELECT Name FROM [" & currentDataFileName & ".csv] WHERE Datatype ='TYPE3'", 
 con

ThisWorkbook.Worksheets("Sheet2").Range("A:A").CopyFromRecordset rs
 rs.Close
con.Close

Set rs = Nothing
Set con = Nothing


End Sub

Upvotes: 4

Views: 30679

Answers (1)

telemark
telemark

Reputation: 91

You might refer to the CopyFromRecordset() method.

Based on your code above, after the rs.Open command you would add something like this:

ActiveWorksheet.Range("A1").CopyFromRecordset rs

See more here: http://msdn.microsoft.com/en-us/library/office/ff839240%28v=office.15%29.aspx

Upvotes: 4

Related Questions