Importing 10+ excel worksheets

[jwahlton]

I need to import daily excel worksheets into 1 access file and then
compare/count the records. Is there a way to do this? Thank you

 

[Klatuu]

In a loop, link each worksheet as a table.
To get the number of record in the linked worksheet use:
currentdb.TableDefs("TableName").RecordCount

use the same method to get the current record count of the destination table.

Use an append query to append the records from the linked worksheet to the
destination table.
Check the record count again the difference between the current count and
the count before the append should be the same as the record count for the
worksheet.
delete the linked worksheet (it doesn't delete the file, just the link)

 

[Chris]

What if I need to import different excel sheets in one access database
without the use of linking them together. Is there another alternative? What
other methods are there for inputting more data from an excel sheet to an
existing database?

 

[Klatuu]

Sorry, Chris. I don't understand the question.
Without linking what together? Can you give me some detail, please.

 

[gllincoln]

Hi,

By 1 access file, do you mean you wish to put the contents of several excel worksheets into one individual table in Access?

This sometimes gets complicated - are all of the sheets in the same format? That is, do they have the same number of columns, in the same order, with the same heading names, all the time?

Cordially,
Gordon

 

[Chris]

Sorry about that. They were talking about linking. Is there any other
alternatives to importing multiple excel sheets into one database. The
database is already in existence and I need to add multiple records. How can
that be done?

 

[Chris]

I have multiple worksheets but with different counts of columns. Is it still
possible to import the information on the excel.

 

[gllincoln]

Hi Chris,

It is nearly always possible but to recommend a best course of action I
would have to know more about what tables, what worksheets, workbooks are
involved.

Are you wanting to import every column of data contained in each worksheet,
more than one worksheet per workbook, how consistent are the workbook names?
How consistent are the worksheet formats? Is this all going into one table
or does each column arrangement have a corresponding table of its own? How
many rows of data in each worksheet?

Let's assume you only want certain fields/columns and their header in the
worksheet is always the same, although the individual worksheets may contain
various other columns in various ordinal positions, but we never have more
than 50 or 100 rows of data in any given worksheet.

In this case, I would create an import folder, put all my to be imported
workbooks in that folder, opt for using the Excel Application object to
literally read the header rows of each worksheet in the target workbooks.
If I find my headers/column/field names then I would save the col positions
of each field I want, probably use a variant array. I would plow down the
rows until I found an empty row, save that row count as my last row of data
(-1).

Then I would stuff the column data I wanted into my array - open up my
target table and insert the array values in the appropriate fields, close
the target, clear the array, check the next worksheet, repeat until I run
out of worksheets, check the next workbook until I run out of workbooks.

Hope this gives you some ideas,
Gordon