transferspreadsheet row numbers 1

[PizMac]

I am importing an excel sheet with the transferspreadsheet command and I need to be able to reference the original Excel row number - is there any way I can add an autonumber key as I can when File-importing (specify "let access add primary key" and it gives an autonumber ID) ? Without the ID it doesn't always import to the table in the original row number order ....

Any help gratefully received

 

[JerryKlmns]

Record ordering in a table has no meaning for a database. Plus Autonumber field wont restart at 1 if you delete the records and reimport nor does it reorder them when you delete a record. If you need the ordinal position of the record at the imported worksheet, add a collumn there and import that too!

 

[PizMac]

unfortunately I am not in control of the imported spreadsheet so I can't make other people add a numbering column. If I import manually using File-Import I have the option to add an autonumber and this is in record order - I merely want to replicate this in code.....

 

[PWise]

before you do the transferspreadsheet open the spread sheet
and add field

 

[SkipVought]

"and I need to be able to reference the original Excel row number"

maybe if you explain WHY you need this, it might help in arriving at a solution from another direction.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PizMac]

OK Skip - I am doing quite complex validation on the data in the spreadsheet and need to be able to produce an error report pointing the user to the ROW in error, nothing else uniquely identifies the row in question.

 

[SkipVought]

Does not the DATA ITSELF identify the row in Excel?

If you return the error list, you could identify the errors in Excel, for instance, by using a FLAG, wherby an AutoFilter could isolate those rows, or by using Conditional Formatting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PizMac]

yes - but too complex I think - the spreadsheet comes by email from another source (there can be many files from many sources with many lines in each)- I just wanted to be able to say tho the sender "you've got xxxxxx error in column C in Row 6" - but doesn't look as if it's possible - I still think it's odd that File-import gives the option of adding an autonumber (in row order) but transferspreadsheet doesn't .. ah well, thanks everyone for trying

 

[PHV]

Just after the transfer to a new table:
DoCmd.RunSQL "ALTER TABLE yourNewTable ADD COLUMN RowNum COUNTER(1,1)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PizMac]

thanks so much PHV - just what the doctor ordered! - by the way - can't find it in the help anywhere - what does the (1,1) do? tried (2,1) and got the same result

 

[PHV]

can't find it in the help anywhere
Even in the JetSQL help ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PizMac]

didn't have any help for JetSQL but after some investigation have downloaded it from MS and now I've got it - thanks again