You'll see from the example attached I have the below code which will bring back certain records I require from a tab delimited file where the first column in the txt file matches the reference in cell B2.
This works great however I want it to work via an ADO macro (module 3) as it will work faster on a txt file with massive numbers of records. I am getting an error however on the ".open str" bit of the module 3 code. Can anyone help?
Below is the code I am trying to get to work as I understand for example if my txt file was huge (e.g. 2 million plus records) this would process much faster.
Sub QueryTextFile() t = Timer Dim cnn As Object Dim str As String Set cnn = CreateObject("ADODB.Connection") cnn.Provider = "Microsoft.Jet.OLEDB.4.0" cnn.ConnectionString = "Data Source=C:\Users\Davids Laptop\Documents\Other Ad Hoc\Test Files\;Extended Properties=""text;HDR=Yes;FMT=Delimited;""" cnn.Open Dim rs As Object Set rs = CreateObject("ADODB.Recordset") str = "SELECT * FROM [test1.txt] WHERE [aa]=" & Chr(34) & Range("B2") & Chr(34) With rs .ActiveConnection = cnn .Open str Sheet1.Range("A4").CopyFromRecordset rs .Close End With cnn.Close MsgBox Timer - t End Sub