Hi flemanski,
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 recent 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.)
