Hi,

I have been importing excel spreadsheets succesfully for a long time now and this new problem I have never seen before. I don't know how to deal with it. I have had a look on here but could find nothing similar, I have not really had a any luck searching google, mainly because I am at wits end to find a suitable search term.

Here's what's happening:
When i upload the sheet as is, it works fine, bar a "data conversion error" for one column which contains mainly numbers but a few cells of letters.

as before, I have inserted a line at the top of the spreadsheet (below the column headings), and put a 1 in for each numeric column and a letter for each alphanumeric and text based column.

Now when i import to access, I get no conversion error, but access has inlcuded about 20'000 blank lines between the headings and the actual data.

I really don't know what is going on.

I'm not clear precisely what your problem is from your description. However, one way to limit what is imported from the spreadsheet is to define a "Name" in the spreadsheet that macthes the cells that you wish to include. Now, assumin you are using the TransferSpreadsheet Action within a Macro in Access, then you enter the name given to the cell range in the Range field on the Macro Sheet.

So:

Transfer Type would be "Import"
Spreadshet Type would be "Microsoft Excel 8-10" (or whatever)
Table Name - the table into which you are importing
File Name - the location of the file and its name (.xls)
Has Field Names - No or Yes depending on which is true (Do you have COl Heads or not)
Range - The NAME of the cell range - e.g. IMPORT_AREA

Hope that is of assistance. If not, try abd describe your issue somewhat more.

A

Basically the problem was this:
Instead of showing the first line of records in access in row 1, it showed it somewhere on row 20000 or something with all rows between 1 and 20000 being blank.
in excel all records started in row 1.

Reading your post helped me sort it though, as i noticed the print range in my spreadsheet was somewhere well off, where no records where.

I changed the printrange to encompass my records, and hey presto, the data import worked! :)

The blank lines show up before your data because the table is most likely sorted in increasing order. Blanks just come first.

I have had the same problem. I bypassed it without setting a range (I didnt want my user to have to do this) by importing the data to a tempTable and then I Delete all records where the the column the will be my primary key is Null. Then I set up sum update and insert queries to move the data from my tempTbl to the permanent table. This way I only took the data I needed, and could do some data validation, manipulation, and conversion during my insert and update. I then deleted the temp table.

Late Reply, but I just joined