Simple question importing Excel to Access 
Author Message
 Simple question importing Excel to Access

Hello all,

I was wondering if anyone knows how to import Excel files
into Acces tables with straight VB code. Can this be done?
Please don't recommend the use of the Access import function
since this is not an option in my application.

Thanks
Perecli



Sat, 05 Dec 1998 03:00:00 GMT  
 Simple question importing Excel to Access

Quote:

>Hello all,
>I was wondering if anyone knows how to import Excel files
>into Acces tables with straight VB code. Can this be done?
>Please don't recommend the use of the Access import function
>since this is not an option in my application.
>Thanks
>Perecli

You certainly can do this using the Jet 3.0 engine in VB4-32. You can
open an Excel spreadsheet using a statement like
        set XlDB = opendatabase("c:\myfile.xls", False,false,"EXCEL 5.0")

You can then query the spreadsheet cells (or ranges or named ranges)
using something like
        set MyRecordSet = XlDB.openrecordset("Sheet1$A1:B10")

You must be careful however with things like automatic typing as you
may find yourself importing Text instead of numbers etc etc. (You may
find, as I am doing right now, that you may have to read each cell in
one by one, or at least read in only similarly typed ranges at a
time). There are also some registry entries that you must set
depending on what behavior you wish (such as using first row as field
names, number of rows to scan to determine input type, etc). These
entries are situated under the
(Local machine entry)\Microsoft\Software\Jet\3.0\Engines\Excel.

If anyone knows how to set these properties without using the registry
I sure would like to know about it as I'm breaking my teeth on this
problem.
Another related question is how to avoid having Nulls coming back in
the recordset when mixed types are present in the range on the Excel
spreadsheet.
Hope this helps and thanks to anyone for any help on the last two
questions. :-)

Fred Baptiste



Mon, 07 Dec 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Excel/access import question

2. Simple question from a beginner ( importing/exporting)

3. Automate Import data from an Excel Range - inexperienced question

4. Newby question: import Excel worksheet

5. Newby question: import Excel worksheet

6. Simple Question: Assigning Icon to an App : Simple Question

7. trouble importing AND exporting between access and Excel

8. Importing select Excel data into Access 97 tables

9. Import multiple excel files with inconsistent number of worksheets in each into ACCESS

10. Importing an Excel Spreadsheet in Access 2

11. Import Access Query to Excel

12. importing excel 97 srpeadsheets to access 97

 

 
Powered by phpBB® Forum Software