-----Original Message-----
Hi Bonnie,
Unless I've gone temporarily insane, the Advanced button on the wizard
and import specs aren't available when importing from Excel files.
As for the "ImexGrid" error, 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.)
Hi there. Using A02 on XP. Have a strange problem. Made
some changes in an Excel file that I import using specs.
Tried to get to the import wizard so I could alter my
specs and save the changes but I got the error in my
subject line above and now my Advanced... button doesn't
even appear for any Excel files!!! Not in ANY of my
DB's!!! I can see the button if I try to import a .csv
or .txt file though. Ugh! What do I do? Must I reinstall
Access? Perhaps a reference is broken?
Can I get to import/export specs any other way for my
Excel files? I've tried creating a new DB and importing in
all my objects but, no good.
Thanks in advance for any help or advice!
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
