Importing spreadsheet brings in too many columns and rows

  • Thread starter Jonathan Scott via AccessMonster.com
  • 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
 
J

John Nurick

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.
 
J

Jonathan Scott via AccessMonster.com

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
[quoted text clipped - 8 lines]
Jonathan Scott
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top