sgoodhall at comcast.net
sgoodhall at comcast.net
Thu Mar 1 10:03:25 CST 2007
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: 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 > > > > --------------------------------- > 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