Importing spreadsheet brings in too many columns and rows

  • Thread starter Thread starter Jonathan Scott via AccessMonster.com
  • Start date Start date
J

Jonathan Scott via AccessMonster.com

I am trying to import an Excel spreadsheet that has a fixed number of columns,
but unknown number of rows. For some reason importing it brings in all 256
columns despite the fact that I'm only using 10.

I have also seen cases where it brings in tons of blank rows of which I have
no use. I must be searching with the wrong criteria because I cannot find
anything about this anywhere.

Could someone please point me in the right direction? How can I keep Access
from trying to import data that doesn't exist?

Jonathan Scott
 
HI Jonathan,

By default, Access tries to import the UsedRange of the worksheet. This
is complicated to explain in detail but basically is the rectangle
enclosing all cells that contain or have ever contained formulas or
values, or had formats applied to them (though formatting an entire row
or column doesn't count).

Things you can do include:

- define a named range in the workbook covering the actual range you
want to import, then import the named range

- delete all rows below and columns to the right of the range you want
(they'll be replaced, of course, with new rows and columns that are
outside the UsedRange

- specify the actual range you want to import in the Range argument of
TransferSpreadsheet.
 
Thanks John, That fixed it.

Jonathan Scott


John said:
HI Jonathan,

By default, Access tries to import the UsedRange of the worksheet. This
is complicated to explain in detail but basically is the rectangle
enclosing all cells that contain or have ever contained formulas or
values, or had formats applied to them (though formatting an entire row
or column doesn't count).

Things you can do include:

- define a named range in the workbook covering the actual range you
want to import, then import the named range

- delete all rows below and columns to the right of the range you want
(they'll be replaced, of course, with new rows and columns that are
outside the UsedRange

- specify the actual range you want to import in the Range argument of
TransferSpreadsheet.
I am trying to import an Excel spreadsheet that has a fixed number of columns,
but unknown number of rows. For some reason importing it brings in all 256
Click to expand...
[quoted text clipped - 8 lines]
Jonathan Scott
Click to expand...
Click to expand...
 
Back
Top