?
Solved

Excel tables as a Recordset?

Posted on 2003-02-19
3
Medium Priority
?
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Starting up a Project

752 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