Access-Excel Import Wizard problem

  • Thread starter Thread starter Ron West
  • Start date Start date
R

Ron West

This Excel VBA code gives the correct number of Rows and Columns being used
in my Excel worksheet:-

Dim x As Worksheet
Set x = Worksheets("Store")
Debug.Print x.Cells.Rows.End(xlDown).Row
Debug.Print x.Cells.Columns.End(xlToRight).Column

However, due to accidental over-scrolling, the displayed Excel scrollbars
represent a range of about 40 columns (instead of my sheet's correct number
of 27) and 65535 rows (instead of my sheet's correct number of 1253).

This is a major problem with Office 2007 (I don't remember it happening with
earlier versions) because when you try to import the worksheet into an Access
2007 table, you get 65535 (mostly blank) rows imported instead of 1253 and
you have to mess around identifying the blank rows and deleting them.

You also get error messages that Field28 doesn't exist, etc, if you try to
import to an existing table instead of a new one.

How do you correct the Access 2007 import wizard (and/or Excel's internal
scroll range) to match "Cells.Rows.End(xlDown).Row" and
"Cells.Columns.End(xlToRight).Column"?
 
Name the range for import or set print area and use that for import or more
than likly you have a cell with something in it way down at the bottom,
could be a charcter you cannot see. Goto the last cell bottom right of
the range you have problems with and select all the way back to your last
real data cell. Right click on a row in the selected range and delete rows.
Should solve your problem. I have this happen often with frequently edited
Excel files. Users accidently put something in a cell you can't see.
Usually shows up if you clear print area and select fit 1 to 1 page then
preview print. It will be unreadable as it is trying to print everything,
even things you can't see.
 
Back
Top