Importing spreadsheet brings in too many columns and rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jonathan Scott - 14 Jul 2005 04:21 GMT

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

Reply to this Message

John Nurick - 14 Jul 2005 06:27 GMT

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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Jonathan Scott - 14 Jul 2005 07:50 GMT

Thanks John, That fixed it.

Jonathan Scott

>HI Jonathan,
>
[quoted text clipped - 26 lines]
>
>Please respond in the newgroup and not by email.

Reply to this Message