[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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.



0
hcchang
Asked:
hcchang
  • 2
  • 2
1 Solution
 
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
 
TimCotteeCommented:
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
 
TimCotteeCommented:
Glad to help.
0
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now