Y
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
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