Attn: John Nurick-- appending data from several linked .xls files into an access table

[Rocky]

John;

I need to appending data from linked .xls (20 sheets) into an access table
, so I can run a report and determine duplicates from different work sites.

I saw the example with Klatuu. Are there sites I can see actual examples so
I can learn the syntax better, and what do I do about the rtanges from
diffrent spread sheets some have 50 rows some have 500 rows of data. I 'll
make sure all have the same naming of the fields in excel.


I also need to know ; do I have to create an access table with the same
field names of the excel files.

I appreciate your help in advance

Thanks

Mo

 

[John Nurick]

Hi Mo,

I need to appending data from linked .xls (20 sheets) into an access table
, so I can run a report and determine duplicates from different work sites.

What do you mean by "linked .xls files"? If you already have linked
tables connected to the 20 worksheets, base your append queries on
those. Otherwise please explain just how the files are "linked".

I saw the example with Klatuu. Are there sites I can see actual examples so
I can learn the syntax better,

I don't know a site where it's properly explained, which doesn't mean
there isn't one somewhere. If you find it, please post back here.
Meanwhile, if you search at http://groups.google.com for
"HDR=" "jamie collins" OR onedaywhen group:microsoft.public.access.*
you'll find plenty of examples.

and what do I do about the rtanges from
diffrent spread sheets some have 50 rows some have 500 rows of data. I 'll
make sure all have the same naming of the fields in excel.

If the data range starts in cell A1 you can usually just specify the
sheet name. This causes Jet to attempt to import the sheet's UsedRange.
Otherwise, it may be enough to specify a range that includes more rows
than you will ever need, and count on Jet to ignore the blank rows below
the actual data (again, the UsedRange is relevant here). For certainty,
you have to write code that examines the worksheet to determine the
exact range to be imported.

It's not essential to ensure that all the Excel "tables" use the same
column headers, because it's possible to alias field names in the
queries. But it does make things simpler.

I also need to know ; do I have to create an access table with the same
field names of the excel files.

It's not essential that the field names are the same. But usually the
simplest way to ensure that the Access table has exactly the field
*types* you want is to create it yourself rather than rely on make-table
queries.