Bhupinder Singh
bhupico at yahoo.com
Thu Mar 1 22:21:31 CST 2007
Hi Susan, I am an Analyst and am having more then 300 excel files and with in each excel file there is one sheet that i have to import. I have imported all the sheets in new different tables( named after there file name and sheetname). each sheet is having more then 1000 records. after importing those 300+ sheetes in my DB I want to put all the records exising in different table into a single table with selected columns. 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. 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. Hope i am clear now. Waiting for your reply. Bhupinder Singh Susan Harkins <ssharkins at setel.com> wrote: I'm not sure what you're after. You're importing all these Excel records into an existing table. Why do you want to add the AutoNumber programmatically if the table already exists? Susan H. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bhupinder Singh Sent: Thursday, March 01, 2007 8: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. 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 --------------------------------- Here's 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 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.4/703 - Release Date: 2/26/2007 2:56 PM -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com --------------------------------- Here’s a new way to find what you're looking for - Yahoo! Answers