?
Solved

Excel tables as a Recordset?

Posted on 2003-02-19
3
Medium Priority
?
294 Views
Last Modified: 2010-04-17
Hello,

I have like 100 tables exported from Access into Excel (one on each sheet) where the first row has the field names and the preceding rows have the data.  Is there a way to access these tables the same way you can access a recordset.  I'm using Visual Basic 6 with DAO  (NOT ADO)

(This is what I want to be able to do)
example:  
   LastName = MyExcelTable![lastname]
   MyExcelTable.MoveNext

Thanks,
Bret
0
Comment
Question by:VisualBret
3 Comments
 
LVL 16

Expert Comment

by:twalgrave
ID: 7983065
Reverse the instructions from the following link:
http://archive.devx.com/free/articles/vb2themax/10tips/fb0201-5.asp
0
 
LVL 3

Accepted Solution

by:
VBtom earned 200 total points
ID: 7983717
Hi VisualBret

It's perfectly possible to access data in excel with DAO (and it works much faster than accessing the data with excel!).
An example:

Dim db as dao.database
Dim rst as dao.recordset
Set db = opendatabase("C:\My Documents\MyExcel.xls",false, false, "Excel 8.0;")
Set rst = db.openrecordset("SELECT * FROM [Sheet1$];")
'do whatever you need with the recordset
rst.Close
db.Close

*you should fill in all 4 arguments of opendatabase or it won't work
*the problem is sometimes finding the connect-string e.g. "Excel 8.0;", this can be a different number than your excel-version. Check registry/hkey_local_machine/software/microsoft/jet/x.0/Isam formats for the possibilities and add a ";" at the end. See under Jet/3.0 if you use DAO 3.5, under Jet/4.0 if you use DAO3.6 or higher. Or the easy way to find this out: put a data contol on a form en see what's available for "connect".
*The tables (tabledefs) in the excel-file you can use are
A)the sheets (with a $ at the end of the name!, e.g. Sheet1 --> Sheet1$)
B)named ranges (same name as in excel)
*Row 1 of the sheet or named range is used for the field titles (if it's blank, the fields will be called F1, F2 etc.)
0
 
LVL 3

Expert Comment

by:FaithRaven
ID: 9307444
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept VBtom's comment
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

FaithRaven
EE Cleanup Volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Progress
Introduction to Processes

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question