INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

A quicker way to get data into EXCEL from an ODBC data source by taupirho
Posted: 29 Jun 04



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

mydata.SetText Mid(data_block, 1)
mydata.PutInClipboard
Cells(1, 1).Select
Activesheet.paste

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.


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close