Importing data from another spreadsheet stops before end

[davidm_uk]

I have a membership spreadsheet database with about 950 entries, one row per member.

I'm trying to import the names and address columns into a new spreadsheet to send to a printers for membership cards. I'm using the Data-From Other Sources-From Microsoft Query-Excel files* menu sequence, then selecting the file to import from and the name and address columns. No filters or sorting are specified.

When I then run it, only the first 620 rows are imported and it stops. The behaviour is consistent over a number of attempts. I've checked the data in the cells in the source spreadsheet where it stops, they are all just text, and I can't see anything odd in them. There are no blank rows, or blank cells in the columns specified for import.

The membership spreadsheet is saved in .xls compatibilty mode, and I'm importing into a 2010 .xlsx spreadsheet, but don't think that should be a problem.

Any idea what the problem is, or what I can do to investigate further.

(W7 Professional X64, Excel 2010, CPU Intel i5-3570k, 16Gb RAM)

[trenars]

Do you want to copy all the data from one excel sheet to other? If so, why you do not use Move/Copy option on your Tab menu? (Right click on the tab you want to Move/copy)?

[davidm_uk]

No, I want to copy the data in just some of the columns into a new workbook, and was planning on getting this as automatic as possible by saving the new workbook as a template complete with the VBA code to do the import.

[davidm_uk]

I've played around with this some more, and eventually just created a test input spreadsheet, with one worksheet containing one column of text headed NAME followed by a 0001 .....to a 1000 down the column.

No matter what I try the data input to a new workbook stops at record 622.

Both workbooks are in xlsx format, I've checked Query window that there's no limit set of the number of records imported, and no error messages are displayed.

Any ideas?

[davidm_uk]

Can anyone help please?

[aydeegee]

Not sure I understand the problem.

If you want only certain columns in the new spreadsheet, from the new spreadsheet press "=" and point to the first item in the old spreadsheet. Repeat for each column you want.
Copy down for 1000 rows and remove excess lines from new sheet.
Copy everything and paste values only.

This solution works for my perception of the problem :>)

Alastair

[davidm_uk]

Yes this does work, but as said above

a) I only want certain columns from the source spreadsheet and
b) I'm trying to automate the process for simplicity when having to carry out the task on a regular basis, such that opening the destination spreadsheet automatically causes it to be populated with the required data. So I don't want to just put a load of copying functions in the destination spreadsheet as I want values in there and not formula, plus I don't want to have to keep extending the formula down the columns as the list of member in souce spreadsheet gets longer.

All the "tutorials" I've found via googling indicate that it should be simple using data import approach, I just can't figure out why it stops before all the data has been imported.

[aydeegee]

This worked for me using Data > (Get external data) Existing Connections (rather than Microsoft Query-Excel files*)

(Ok so using Microsoft Query-Excel files* also worked, but it's another approach)

If it does not work for you, perhaps you should post your data? (sensitive items removed)

Alastair

[davidm_uk]

I think I've solved this.

Using Microsoft Query-Excel files*, select the file to import from, then on Query Wizard - Choose Columns, select Options, then on Table Options make sure that the System Tables box is ticked, click ok. The Choose Columns window now lists all of the worksheets in the available tables and columns, select the worksheet then columns* you want to import. The import now works as it should.

*I did notice that under the available tables and columns list, for the Member Name column there are entries for 'Member Data$' and 'Member Data$'FilterDatabase. The latter contains entries for only the column names in the sheet, the former contains these plus F25 thro F254. I don't know what these are. Filters have been used on the source worksheet, but none are active at the moment.