export excel Data by ADO

Dear Everyone.
I'm using ADO at VB&VBA.
I have to import data from excel sheet as recordset format.
I tied to do as the same way to expert Access Data but I failed.
How can I expert excel data and open recordset.

I need to read excel files from VB application. My first question what I should use ADO. When I've tried ADO I can choose only mdb files, if I choose excel file(.xls) I got message that file is in unrecognized file format.
Of course I can create and link access data base with exl file and after that use this access database, but I didn't think that it is good solution.
So how to use ADO to read excel files ?
And How to open recordset?

Please teach me with detail VB code.
Thank you for your time.



hcchangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hcchangAuthor Commented:
This is very omportant to me.
Please help me.
And please teach me easily and detaily with detail vb code.
I couldn' t file this question on MSDN library. If you find it please teach me how can I find this topic on MSDN.

Thank you very much for your time.
0
TimCotteeHead of Software ServicesCommented:
hcchang:

This should help to get you going. Make sure that you have references to ADO in your project.

'Declare necessary objects
  Dim db As ADODB.Connection
  Dim adoPrimaryRS As ADODB.Recordset
'Open the connection to the Excel Spreadsheet
  Set db = New ADODB.Connection
  db.CursorLocation = adUseClient
'Change the DBQ= and DefaultDir= sections to point to your spreadsheet.

  db.Open "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DBQ=U:\easter calculation.xls;DefaultDir=U:\;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

'Create the recordset using the name of the worksheet as the tablename followed by "$" and surrounded by "[..]".

  Set adoPrimaryRS = New ADODB.Recordset
  adoPrimaryRS.Open "select * from [Sheet1$]", db, adOpenStatic, adLockOptimistic

' You can then use this recordset as any other e.g.,

  With adoPrimaryRS
    .MoveFirst
    Do
      msgbox adoPrimaryRs.Fields(0).Name & " / " & adoPrimaryRS.Fields(0).Value
      .MoveNext
    Loop Until .EOF
  End With

'Close the recordset and connection
  adoPrimaryRS.Close
  db.Close
'Destroy references to free memory reserved
  Set adoPrimaryRs = Nothing
  Set db = Nothing
0
hcchangAuthor Commented:
Thank you Mr.TimCottee
Your help make me success to make program.
Thank you again.

0
TimCotteeHead of Software ServicesCommented:
Glad to help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chnelsonCommented:
Hi TimCottee and hcchang,

i try using the above code but i have an vb runtime error while at the db.open statement.

The error is:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [-2147467259]

Do you have any idea?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.