Access tries to import more than 255 columns from Excel worksheet with only ~10 columns. Why?

Question

Using Access to make a table by importing an Excel Worksheet, it tries to import more than 255 columns from Excel worksheet with only ~10 columns. Why?

Excel Worksheet has only the 10 columns in white, when Viewing in Print Area style, so there's only those 10 columns?

Might there by another reason other than me missing some cells of data far off from where I'm usually working?

Checking Print Preview, I should see what that shows?

Answer 1

Firstly, using the Print Preview in MS Excel is not a reliable method of checking which cells contain data, since one can easily configure the Print Area to only consider a predetermined range when printing the worksheet.

---

When importing a spreadsheet into an MS Access table, Access will likely use the UsedRange property of the worksheet in order to determine the cell range occupied by data.

However, as noted in the linked documentation:

A used range includes any cell that has ever been used. For example, if cell A1 contains a value, and then you delete the value, then cell A1 is considered used. In this case, the UsedRange property will return a range that includes cell A1.

As such, I would hazard a guess that, at some point in the lifetime of your spreadsheet, data occupied a cell in column 255 or more, and therefore this column remains included in the used range.

To remedy this:

• Select the column heading of the first empty column to the right of your data.
• Press Ctrl+Shift+→ to select all columns to the right.
• Right-click on any selected column heading and click Delete.

Excel will automatically generate blank columns to replace the deleted columns, and such blank columns will not be included as part of the used range when importing the data into MS Access.