[AccessD] How to import row number from Excel Sheet ???

  As each sheet has more then 16 columns but i need to analyse only 4
columns.

  While putting all the records in the final table it the records goes
through so many criterias and conditions.

============Does each sheet contain the same 4 columns? My first suggestion
is to import just the 4 columns you need unless the other columns contain
data that you're using in later criteria expressions. Also, if the columns
are the same, you can import each "range" or subset of columns into the same
table -- you don't need to import them into separate tables unless you need
to work with the data as subsets before you combine them into one table.
Omitting the many tables would certainly simply your task, but I understand
that you might not be able to. 
   
  Now in final table(Collated table) if i need to know that from where the
particular records has come. 
   
  For that i am appending a new column in each table immediately after i
import it by "DoCmd.TransferSpreadsheet"  command and putting the value of
the source sheet's name ,location and file name.
   
  But i am not able to trace the row number in excel sheet for a particular
record in collated table(final table).
   
  To search for the particular record in the sheet i have to search the
record by find command, which is time consuming.

============You can add an AutoNumber to each table, but you must remember
to reset the seed number as you import tables. Otherwise, the AutoNumber in
each new table will start with the same value, and you will have the same
problem -- no way to identify which table the record comes from without
visually checking -- still too much trouble. Even still, the AutoNumber will
only identify the record in the Access table -- it won't point back to a
specific record in the originating spreadsheet. Is that Okay? If you need to
track each record, once in Access, back to a specific row number in the
originating Excel sheet, you may have to add some kind of identifying value
to your records there, before importing into Access. Is that possible? I'm
not familiar enough with Excel to know if you can retrieve a row number from
each row in a particular range. You know it seems like you ought to be able
to do that though. The following code returns the row number of the first
cell in a range named FirstRange -- you'd have to use a For loop to work
through the range: 

Function Test()
  Dim rg As Range
  Set rg = Range("FirstRange")
  Debug.Print rg.Row
End Function 

That sounds terminally slow especially considering you have 300 sheets!
You'd have to import row by row instead of just importing the range or sheet
in a single retrieve. I think it would be simpler to just add a unique value
column to each sheet and import that column. Is this a possibility?

Susan H.