I developed a VBA app recently that was using ODBC to read text files and display their contents on a spreadsheet. Anyway the normal procedure is to open a connection to your ODBC source, read the data into a recordset, then loop around the recordset and assign the values into you spreadsheet. Something like:
Set db_data = New Connection db_data.CursorLocation = adUseClient db_data.Open "PROVIDER=MSDASQL;dsn=Text Files;uid=;pwd=;database=;"
Set ado_data = New Recordset
' Assume we retrieve two columns of data that are to go into column A and B respectively ' of the spreadsheet
ado_data.Open "select col1, col2 from myfile.txt" ,adOpenStatic, adLockOptimistic
if ado_data.recordcount <= 0 then msgbox("No data found") end sub end if
ado_data.movefirst
For i = 0 To ado_data.RecordCount - 1 Thisworkbook.cells(I+1,1) = ado_data.Fields.Item(0).Value) Thisworkbook.cells(I+1,2) = ado_data.Fields.Item(1).Value) Ado_data.MoveNext Next
The above approach works fine but I got the idea that an even quicker way would be to use the paste buffer to write the contents into the spreadsheet. So instead of writing the retrieved data line by line into the spreadsheet we just concatenate the lines together. Copy it to the paste buffer then paste the buffer into the spreadsheet. Here's the same example as above but using the paste buffer technique.
Dim mydata As DataObject Set mydata = New DataObject Set db_data = New Connection db_data.CursorLocation = adUseClient db_data.Open "PROVIDER=MSDASQL;dsn=Text Files;uid=;pwd=;database=;"
Set ado_data = New Recordset
' Assume we retrieve two columns of data that are to go into column A abd B respectively ' of the spreadsheet
ado_data.Open "select col1, col2 from myfile.txt" ,adOpenStatic, adLockOptimistic
if ado_data.recordcount <= 0 then msgbox("No data found") end sub end if
ado_data.movefirst
For i = 0 To ado_data.RecordCount - 1 Db_block = db_block & ado_data.fields(0).value & vbTAB & ado_data.fields(1).value & vbcrlf Ado_data.MoveNext Next
Slightly more complicated to code but much, much faster especially for large data voulmes. See also my other FAQ for ways in which the string concatenation in the above example can be made much faster too if required.
Talk To Other Members