import excel sheet causes autonumber beyond records

[BCJourney]

I am importing a sheet from excel into an existing access table. The table has to have an autonumber to work with sharepoint. My import selects an excel sheet because I have an indexed range and access doesn't recognize that. I have a query that clears the existing table in access, then imports the sheet from excel. When I do that I have the 3 records that should import and the autonumber field goes all the way to 6700. Any suggestions?

[June7]

I've never seen that when I import sheet. Could be there is something, even an empty string in a column of the last row? Not sure what you mean by indexed range. Want to provide the workbook for testing? Follow instructions at bottom of my post.

[BCJourney]

I've never seen that when I import sheet. Could be there is something, even an empty string in a column of the last row? Not sure what you mean by indexed range. Want to provide the workbook for testing? Follow instructions at bottom of my post.

I've attached the spreadsheet. It contains 3 macros. It is designed to first clear an existing list (button 1), then you would copy a list you want to import, click button 2 to paste it and remove certain characters on the end, then button 3 pastes it to sheet 2. I did this because the last four characters have to stripped if they = a certain value. the list varies in length so there may be a problem with pasting empty rows as you've suggested.

thanks!

[June7]

Don't understand. I click button 1 and both sheets are cleared. Copy a list from where?

I haven't done a lot of programming behind Excel but I have programmed ActiveX controls, not Form controls. I don't understand how Form controls execute code.

[BCJourney]

when you click button 1 it clears the list in sheet 1 and sheet 2. you copy a list of names from somewhere else that you want to add. Don't past them, click buttons 2 and then 3. you save and close the document. then you import sheet 2 into access.
button1 - clears the list on sheet 1 and 2
button 2 - removes last four characters if they meet a certain criteria
button 3 - pastes the column to sheet to.

if you look at the range named import_data, you'll see that the formula is =OFFSET(server_names!$A$1,0,0,COUNTA(server_names! $A:$A),1)
because that is the range name access won't import it. that's why i have the column copied to sheet 2 so that i can import what is on sheet to instead of a range name.

[June7]

Okay, got that. Now how do you accomplish the import? Provide code.

[BCJourney]

i have a macro in the 'on click' event of a button that runs the RunSavedImportExport action. The saved import imports the data on the second sheet. the only data on that second sheet is the name list and its heading. after it imports it adds thousands of empty records along with the real ones. they are numbered with an autonumber ID field. I have to keep that autonumber field because this is connected to sharepoint.

thank you

[June7]

Rats! Never worked with Sharepoint or saved imports and I don't use macros so I can't replicate your situation. Sorry.

I will say that the VBA equivalent also has issues with your workbook. Insists on importing columns beyond A. I deleted the import sheet and created a new one. Now import works.

[BCJourney]

Will it work for what I need do you think? I just had to import something this morning. After being imported I had to delete the extra records.

[June7]

Will what work - VBA? It won't change the results, the import still has the blank records. Something about the worksheet. Where does this worksheet come from?

[BCJourney]

i made the worksheet as a template to import the data. it acts like either the vba is selecting all rows on the 2nd sheet instead of stopping at the last entry or Access is simply importing every row on the sheet and ignoring when the data ends.

[BCJourney]

I think I got it. selected the Server_name field (which is the only field that has data), click the fields tab, then checked the 'required' box. Now it only imports records that contain something in this field. Problem solved. Thank you for helping me through this.