Method of 'column' of object 'IImexgrid' failed

[Guest]

Can anyone tell me what this error message means? I'm trying to import an
Excel file into Access and get this message.

 

[Guest]

If I name my range and import without the headings, I am able to transfer but
still get error messages. It appears that account numbers that are all
numeric but formatted as text in Excel are converted to number type in Access
and accounts with hyphens in do not transfer. How can I keep the numbers
formatted as text in Access?

 

[John Nurick]

To get round this problem, either

- create your table in Access with the field types you want, and then
import the data from Excel, or

-export from Excel to a CSV file, and import the CSV into a new Access
table, choosing field types in the process.

 

[John Nurick]

Hi Kim,

If anyone really understands what causes "Method 'Columns' of object
'IImexGrid' failed" they're keeping their heads down. I've seen three
suggestions for the cause.

The first (which I feel is most likely) is that the import routine has
got confused about what it should import. One user found his own
solution and posted this:

Basically I had empty columns in the excel worksheet I was
trying to use. Although the cells were empty they had
formatting in them as a time. Access kept picking up on
these cells as though they had data in them.

This is plausible; by default the import routine tries to import the
entire UsedRange of the worksheet, which is (very roughly speaking) the
rectangle starting at A1 and including the furthest-right and
bottom-most cells that have ever contained data or been formatted.
Here's how to re-set the UsedRange, based on a post in another group by
Excel MVP Ron de Bruin:

Excel can think that the UsedRange is bigger then it is.
You can see how big your usedrange is on every sheet with CTRL-END.

If the row/column is not the last row/column with data then:

1 Select the first row below your last row with data
2 CTRL-SHIFT-DOWN ARROW
3 Right click on the selection and choose delete

And then for the columns

1 Select the first column next to your last column with data
2 CTRL-SHIFT-RIGHT ARROW
3 Right click on the selection and choose delete

Save the file and close it

An alternative to resetting the UsedRange is to define a named range of
cells and tell Access to import them. This is what Michael de Noto of
Microsoft tech support suggested to another user with this problem:

This error may be due to protection set in the Worksheet, is
this the case? Open the sheet in Excel, go to Tools..Protection... and if
it said "Protect Sheet...", "Protect Workbook...", it means there is no
protection set. If protection was set, it would say Unprotect sheet.

If not protected, select the data we wish to import, and Name
the range, by going to Insert..Name..Define, giving the range a name, and
saving the workbook. In Access, you can set the Import Spreadsheet Wizard
to import named ranges; selected it and try to import again.

The next possibility is that the worksheet or workbook is protected. To
check, open the workbook in Excel and go to the worksheet. In the
Tools|Protection submenu, if it says "Unprotect Sheet" or "Unprotect
Workbook", protection is turned on.

Finally, I've seen a couple of reports that suggest that you can also
get this message if (a) you choose the "first row contains headers"
option in the import wizard and (b) not all the cells in the first row
of the Excel data contain valid Access field names. (But this may be a
red herring.)