allow user to select range in Excel worksheet from Access form

[yax131]

Hi all,
My apologies if this is the wrong place to post this; please let me
know a better place if not. (I already tried
microsoft.public.office.developer.vba and was referred here).

I am trying to put the finishing touches on an Access program that
I've built to import data from an Excel spreadsheet. Currently, it
works like this:

1) user opens spreadsheet in Excel, selects import range, uses macro
(in personal.xls) to name the selected range
2) user closes Excel, opens Access
3) user invokes an access form with a button that (among other things)
uses TransferSpreadsheet to import the data (with hard coding of the
name of the range selected in step 1).

Obviously, this is cumbersome and, well, not a bit of a pain.

The reason this procedure seems to be necessary is because the range
of cells to be imported can vary by the number of rows (not by
column). The number of rows to be imported is not known (to the user)
before the user opens it; however something along the lines of
=OFFSET($A$1,0,0,COUNTA($A:$A),8) would return the correct range).

Ideally, it would be great if I could do one of the two following
things:
SCENARIO 1:
- use the OFFSET dynamic range above as the "Range" parameter in the
TransferSpreadsheet function (which I haven't been able to do
successfully)

or
SCENARIO 2:
- user opens form in access
- form contains a subform in which the data from excel is represented
- user selects range of import data from subform

***
I'm not married to either of these two scenarios; they just seem like
the way to go. If you have a better suggestion, I'd love to hear it.
Any help anyone can provide me on this would be MUCH appreciated !!!
I've been banging my head against the wall on this one for a while
now.

Thanks again!
Lorin

 

[Klatuu]

It appears you are always starting in A1 and trying to determine how many
rows to return. Unless there is something you are not stating as a problem,
that should not be necessary. TransferSpreadsheet will normally start in A1
and import all contiguous columns and rows. It stops importing columns with
the first empty column and stops importing rows with the first empty row.

 

[yax131]

Hi Dave,
oops... sorry. you're right. The initial top left cell in the range is
usually, but not always, A2. Does that change things?

 

[Klatuu]

Yes it changes things.

First, are you starting in A2 because row 1 is header info?
That is okay, but be aware in TransferSpreadsheet, one of the options is
whether or not the first row imported is the field name for the column. If
you are importing into an existing table, you set that to False and it will
use the field names defined in the table. If you import to a new table, you
will end up with field names F1, F2, F3, etc.

But the simple answer is you can define the range in the
TransferSpreadsheet. For example, if you wanted to start at row 20 and
import columns C through Z, you would use somethingl like:

docmd.TransferSpreadsheet acImport,,"_boo","c:\documents and
settings\XXXX\my documents\Project Listing Report 2008.xls",false,"C20:Z"

 

[yax131]

Thanks Dave! that worked. (for the record, row 1 wasn't the header
row .. the header row started on a variable row for each file to be
imported. BAD DESIGN, but not under my control. )