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

Ideally, it would be great if I could do one of the two following
things:
SCENARIO 1:
- use a dynamic range 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

 

[Clif McIrvin]

Hi Lorin

It is possible to write Access VBA code to do your current step 1)
directly using automation. I'm too much of a beginner to try and advise
you here; try searching the Access newsgroups for references to
Automation and / or Excel.

As to your SCENARIO 2, what is the criteria that the user looks at to
identify the range to select? It might be possible to build a parameter
query that uses that criteria to return the proper range from the
spreadsheet. If this sounds workable I'd suggest

1. Use a Linked Table to 'see' the worksheet data.
2. Use a Select Query (with parameters) to return the range you're
interested in from the Linked Table.
3. Use an Append Query instead of the TransferSpreadsheet.

This could all be driven from VBA code behind a form control.

Just a guess: microsoft.public.access.externaldata might be a good place
to try if you don't find a solution here, I've also seen Excel Import /
Link discussions in the microsoft.public.access forum.

 

[JP]

Could a technique like this work for you?

http://www.codeforexcelandoutlook.com/blog/2008/02/import-data-to-access-from-excel/

You would just need to modify the code to save only the named range
area before import.

HTH,
JP

 

[yax131]

Thanks so much for your input. i'll try these suggestions...

 

[yax131]

Hi JP,
Thanks for passing that along. That would definitely work, but the
problem really is in selecting (and naming) the range in the first
place. Any suggestions?

 

[JP]

There are many ways you could do it. Part of it depends on how the
import range is determined. For example, is it a text block in an
otherwise empty worksheet?

For example, if you needed to import a dynamic number of rows, and it
was always the top N rows of a worksheet (starting in A1), and there
was nothing else on the worksheet, you could figure out the range
borders with a technique like this:

Dim myLastRow As Long
Dim myLastColumn As Long
Dim myLastCell As String

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address
MyRange = "$A$1:" & myLastCell
ActiveSheet.Names.Add Name:="MyRange", RefersTo:= "=" & MyRange

(Note: This is air code, please test it first)

From Access, you could create an instance of the Excel object, then
use this method to name the range, then import the worksheet in
whatever way seems best to you.


HTH,
JP