Import Excel worksheets via VB and DAO? 
Author Message
 Import Excel worksheets via VB and DAO?

I have been "reading in" text files in VB and DAO for quite some time.  I
now have a large group of Excel files that I need to read in (ie read line
by line and add/update my database with them).

Can someone tell me how I can read Excel files in via VB?

TIA

-----
Please reply via EMail *AND* Newsgroup if possible.

Paris, FRANCE                      Web: http://www.*-*-*.com/



Sat, 17 Jun 2000 03:00:00 GMT  
 Import Excel worksheets via VB and DAO?

Hi Derek,

You may either use the TransferSpreadsheet Method (see VB's on line help),
or from Excel, save your worksheets as CSV or tab delimited text files, and
read them with the VB/DAO code you've been using.
--

Regards,
Glenn

I have been "reading in" text files in VB and DAO for quite some time.  I
now have a large group of Excel files that I need to read in (ie read line
by line and add/update my database with them).

Can someone tell me how I can read Excel files in via VB?

TIA

-----
Please reply via EMail *AND* Newsgroup if possible.

Paris, FRANCE                      Web: http://www.erb.com



Sat, 17 Jun 2000 03:00:00 GMT  
 Import Excel worksheets via VB and DAO?

i would import or link the File and then read the table within the database.

Quote:

>I have been "reading in" text files in VB and DAO for quite some time.  I
>now have a large group of Excel files that I need to read in (ie read line
>by line and add/update my database with them).

>Can someone tell me how I can read Excel files in via VB?

>TIA

>-----
>Please reply via EMail *AND* Newsgroup if possible.

>Paris, FRANCE                      Web: http://www.erb.com



Tue, 20 Jun 2000 03:00:00 GMT  
 Import Excel worksheets via VB and DAO?

Terry Kreft a crit dans le message ...

Quote:
>i would import or link the File and then read the table within the

database.

I agree.  However there are about 100 files to be imported.  I wanted to
write a system which would loop through them and import each one in to a
database and then read the table from the database without user
intervention...

-----
Please reply via EMail *AND* Newsgroup if possible.

Paris, FRANCE                      Web: http://www.erb.com



Sat, 24 Jun 2000 03:00:00 GMT  
 Import Excel worksheets via VB and DAO?

In Access 95/97, you can attach XLS files as tables. You can do it
with VBA so the process can be automated.

Jim Bailey

Quote:

> Terry Kreft a crit dans le message ...

> >i would import or link the File and then read the table within the
> database.

> I agree.  However there are about 100 files to be imported.  I wanted to
> write a system which would loop through them and import each one in to a
> database and then read the table from the database without user
> intervention...

> -----
> Please reply via EMail *AND* Newsgroup if possible.

> Paris, FRANCE                      Web: http://www.erb.com



Sat, 24 Jun 2000 03:00:00 GMT  
 Import Excel worksheets via VB and DAO?

I take it that all the files are the same layout, that is field names,
order, etc.

If so you could do something along these lines.

1) Link to one of the files

2) Run code of the following type

Function ReAttachXL()
  Dim lodb As Database
  Dim lotab As TableDef
  Dim intCount As Integer
  Dim strXLFile As String

  Set lodb = CurrentDb
  Set lotab = lodb.TableDefs("XLFile")
  For intCount = 0 To 99
    strXLFile = "DelMe" & Format(intCount, "00")
    lotab.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\" & strXLFile &
".xls"
    lotab.RefreshLink
    'Do stuff here using the data in the table XLFile
  Next
End Function


Quote:
>Terry Kreft a crit dans le message ...

>>i would import or link the File and then read the table within the
>database.

>I agree.  However there are about 100 files to be imported.  I wanted to
>write a system which would loop through them and import each one in to a
>database and then read the table from the database without user
>intervention...

>-----
>Please reply via EMail *AND* Newsgroup if possible.

>Paris, FRANCE                      Web: http://www.erb.com



Sat, 24 Jun 2000 03:00:00 GMT  
 Import Excel worksheets via VB and DAO?

I have been trying to use the TransferSpreadsheet method in Access97 and
have been having problems with it.  I had a routine that was very similar to
this request in that it would let a user multiselect files from a fileopen
common dialog and then loop through all the files and TransferSpreadsheet
them into a temp table for processing.  There were up to 12 worksheets in
each selected workbook that needed to be imported and the range was A3:F475
on each sheet.  I had basically the same code working perfectly for over a
year in Access 2.0 but when I moved it to Access97 about 10% of the time the
rows would transfer in out of the order they were in the worksheet.  I tried
importing the same workbook in Access 2.0 no problem.  I was importing all
the worksheets from a single workbook into a temporary table and then
processing them all but I finally just imported one worksheet at a time for
processing because then I could process the data out of order.  I tried many
variations to track down the problem and each variation would produce
worksheets importing out of order it would just change the ones that were
doing it.  I think it is a problem in Access 97.



Mon, 26 Jun 2000 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. VB 4.0 Importing Data from Excel Worksheet into Access 2.0

2. Import Excel Worksheet(2)

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

4. Importing an Excel Worksheet

5. Newby question: import Excel worksheet

6. Import Excel Worksheet into an Access Database

7. Newby question: import Excel worksheet

8. Getting Excel worksheet names using DAO.

9. Using DAO to create new Excel worksheet - Type property lost

10. Excel Worksheet Via OLE - Problem

11. Problems using VBA to link or DAO to import Excel file into Access97

12. Help! Importing Excel using DAO

 

 
Powered by phpBB® Forum Software