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

Steve,

If you do happen to end up doing it programmatically, at least set up named
ranges.  Setting up a named range that matches the size of (rows / columns)
the  data set allows blasting the entire data set out at once instead of
visiting each cell.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
sgoodhall at comcast.net
Sent: Thursday, March 01, 2007 11:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to import row number from Excel Sheet ???



For some reason, the text fell out of this message.  I am trying again.

When I have had to do this in the past, I had to do the import cell by cell,
opening the Excel worksheet as an ODBC Data source or opening an
Excel.Application object and running through the cells programmatically.  If
somebody knows how to do this with the transfer command I would be very
interested as I could never figure it out.  There is some code on my web
site
(www.goodhall.info) that will open an Excel worksheet with ODBC.  The sample
code is in an Excel file, but it should be pretty easy to move it over to
Access.

Regards,

Steve Goodhall

 -------------- Original message ----------------------
From: sgoodhall at comcast.net
> Regards,
> 
> Steve Goodhall
> 
>  -------------- Original message ----------------------
> From: Bhupinder Singh <bhupico at yahoo.com>
> > 
> > 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.
> >    
> >   Following is the code snippt.
> >    
> >   acImport, acSpreadsheetTypeExcel9, strTablename, sXlsFileName, 
> > True, StrSheetName & "!"
> >    
> >       '//Create ID Column
> >     sAppendRegionCol = "Alter table [" & strTablename & "] add 
> > column [RowID] number"
> >     CurrentDb.Execute sAppendRegionCol
> >    
> >   Dim rst As DAO.Recordset
> > Dim iRowID As Integer
> > 'Dim iCount As Integer
> > Dim sRowIDUpdate As String
> >   
> > iRowID = 1
> >   sRowIDUpdate = "Select * from [" & strTablename& "]"
> > Set rst = CurrentDb.OpenRecordset(sRowIDUpdate)
> > rst.MoveFirst
> > Do While (Not rst.EOF)
> >     rst.Edit
> >     rst.Fields(argRowID).Value = iRowID
> >     rst.Update
> >     iRowID = iRowID + 1
> >     rst.MoveNext
> > Loop
> >    
> >   I tried to give the serial number to newly generated table thru 
> > the above code.
> >   But the records in the newly generated table are not in the same 
> > order as
> they
> > are in the sheet.
> >    
> >   kindly let me how to tackle with the above problem.
> >    
> >    
> >    
> >    
> >   Thanx in advance.
> >   
> > Bhupinder Singh
> > 
> > 
> >  				
> > ---------------------------------
> >  Heres a new way to find what you're looking for - Yahoo! Answers
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>