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

Here is an entirely different way to achieve what you want. This code
example assumes the data is a flat file with 17 fields on a worksheet called
"output_data" and that the desired records (for this example) begin in row 2
column A and that the next row following the end of the recordset is empty
(that is how the loop knows it is finished). It also assumes your table is
structured the same as the worksheet you are importing (ie field1 of table =
column1 of spreadsheet) except I have added an extra field in the table
(field18) to hold the row number which ".ActiveCell.Cells.Row" returns.

The advantage of this method is that each data element can be
changed/manipulated/validated before it is read into the table. To
illustrate, I have included an "if" statement that allows switching signs
and rounding  numbers in some columns before entering them in the table. You
can also generate additional data to include in the table which is what is
done to produce the desired row number. Using Access objects on one side of
the "=" sign and Excel objects on the right side can be extraordinarily
powerful.

Although I have not tested it I suspect the major disadvantage is that
reading each cell in each  file is a lot slower than most other import
methods. However, I have used this method on large groups of files with
reasonable time results. Also, Activecell is used to keep the code simple
for this example but this means you cannot use other Excel spreadsheets
while the code is running because the Activecell will be on your spreadsheet
which obviously totally confuses the code. If you know or can determine the
number of rows to import it is better to use the row component of the offset
function and set up another loop instead of ".ActiveCell.Offset(1,
0).Select" but that is a discussion for another day. HTH

Jim Hale


With appexcel

.Sheets("OutPut_Data").Select

'Read results into tblOutPut_Data table
Set dbs = CurrentDb()

'opens the Plan table
Set rstbase = dbs.OpenRecordset("tblOutPut_Data", dbOpenTable)
.Range("A2").Select 'select first cell of first record to read

Do While Not IsEmpty(ActiveCell)
    rstbase.AddNew 'create records in output table
    For x = 0 To 16
    If x < 4 Then rstbase.Fields(x) = .ActiveCell.Offset(0, x)
        'change sign on Jan-Dec revenue
        If x > 3 Then
          rstbase.Fields(x) = -Round(.ActiveCell.Offset(0, x), 3)        
    	End If
    End If
    Next x
	rstbase.Fields(18) = .ActiveCell.Cells.Row
    rstbase.Update            
    .ActiveCell.Offset(1, 0).Select 'find first cell of next record
Loop
End With

-----Original Message-----
From: Bhupinder Singh [mailto:bhupico at yahoo.com] 
Sent: Thursday, March 01, 2007 7:33 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] How to import row number from Excel Sheet ???


hi ,
  I am working with MS Access 2000 and Excel. I need to keep track of row
number of the record that i have imported from excel using 
"DoCmd.TransferSpreadsheet"  (VBA)
  As it does not import the rows number.
  it imports all the records randomly.
   
  Apart from this could you pls tell me how can i add a autonumber column to
an existing table programiticaly or by a query so that i can call it in my
program.
   
  as MS Access does not support "identity".
   
  Purpose: I am importing more then 300 sheets from different excel files. 
  After improting them to different tables i collate all the data in a
singel table. In the collated table I want to know the source row. As i can
keep trak of the excel file.
  from where they are imported .BUT not able to keep track of each record
within the sheet. 

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.