Importing dynamic range into Access

[Bongard]

I have a dynamic range that I would like to use as a linked table into
Access. The problem is that Access doesn't seem to want to to
recognize the dynamic range when you click on "show named ranges" in
the Link Spreadsheet wizard. The reason I want to use a dynamic range
is because when the data in my excel spreadsheet changes the linked
table will import blank rows that were previously used from the bottom
of the spreadsheet. I can't figure out how to get rid of these extra
rows without using an additional query to filter on the blank cells.

Any ideas?

Thanks,
Brian

[Stuart McCall]

"Bongard" <b.*******@gmail.comwrote in message
news:11*********************@d55g2000hsg.googlegro ups.com...

>I have a dynamic range that I would like to use as a linked table into
Access. The problem is that Access doesn't seem to want to to
recognize the dynamic range when you click on "show named ranges" in
the Link Spreadsheet wizard. The reason I want to use a dynamic range
is because when the data in my excel spreadsheet changes the linked
table will import blank rows that were previously used from the bottom
of the spreadsheet. I can't figure out how to get rid of these extra
rows without using an additional query to filter on the blank cells.

Any ideas?

Thanks,
Brian

In Excel, select all the cells you wish in your 'table', then name the range
by typing it into the cell reference box (it will currently be showing D16
or something). Press Return - this is important because Excel will 'forget'
your name if you don't.

Now you can link to the range from Access.

[Bongard]

Thanks for the response Stuart but this is not going to work. I have
created a dynamic range (expands and contracts depending on data size)
to allow for the constant changing of data within the Excel file. If I
just use a typical named range it will not change size to include or
exclude cells as necessary.

[Salad]

Bongard wrote:

Thanks for the response Stuart but this is not going to work. I have
created a dynamic range (expands and contracts depending on data size)
to allow for the constant changing of data within the Excel file. If I
just use a typical named range it will not change size to include or
exclude cells as necessary.

Maybe ask in a microsoft.public.excel newsgroup for some vba code to
define dynamic ranges.

You may find some things on dynamic ranges in google, such as
http://groups.google.com/group/micro...3035e1d472421e

Maybe there's some answers at google or the Excel newsgroups.

Would it not be possible to create a named range on the fly and then do
the link? Perhaps delete a named range, recreate dynamic, save the
coordinates as a new range, then import?

[The Frog]

Hi Bongard,

This is a problem that I have never found an exact solution to, but I
can give you a pragmatic one. If your dynamic named range is not
expanding to extra columns then this will work:

1/ Select all the columns in Excel (eg: A B C D E.....)

2/ Type in a name for the 'fixed' range. This is going to refer to the
same cells as the dynamic at the same time that the dynamic range is
referring to them.

3/ Using ADO to connect to the spreadsheet, you can then import the
data into a local table as follows.....

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & FileName & "; Extended
Properties = Excel 8.0;"
.Open
End With

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
SQL = "select * from [" & NamedRangeName & "] where ColumnName <>
null"

With rs
.CursorLocation = adUseClient
.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
.ActiveConnection = Nothing
End With

cnn.Close
Set cnn = Nothing

Dim db As DAO.Database
Set db = CurrentDb

Dim DRS As DAO.Recordset
Set DRS = db.OpenRecordset("RawImport")

Dim exists As Boolean
exists = False

Dim adoFLD As ADODB.Field
Dim daoFLD As DAO.Field

Do Until rs.EOF
DRS.AddNew
For Each adoFLD In rs.Fields
For Each daoFLD In DRS.Fields
If adoFLD.Name = daoFLD.Name Then
DRS(daoFLD.Name) = rs.Fields.Item(adoFLD.Name).Value
End If
Next
Next
DRS.Update
rs.MoveNext
Loop

You can obviously set up some routines in there to check the records
in the 'rawimport' table to see if the records already exists. Once
the data is in a local table you can pretty much do with it what you
like.

The key here is that the number of columns in the source Excel
spreadsheet cant change. If you add a new column to the spreadsheet
then there must be a matching field added to the access table to
receive the data or it will be missed. You shouldnt get any blank
records here because the SQL select statement used to rip the data
from the spreadsheets named range specifies a column of your choosing
to not be null, so a blank wont be imported. Lastly, make sure your
data types match and that the field in Access has enough size to
accomodate the incoming Excel data - really watch for this with text
fields.

I hope that this helps you out.

Cheers

The Frog