Importing nonadjacent cells (range) from Xcel

  • Thread starter Thread starter Hpolakoff
  • Start date Start date
H

Hpolakoff

Would appreciate help on this.

Have xcel spread sheet that will be growing and that
weekly needs to be appended to an access table.

To test my procedure, I created a three row spread sheet
and a row for column headers (as row 1). I then created a
range name using ctrl key to select and highlight rows 2
thru 4 (data rows) and then with control key, selected
header row. While all 4 rows were selected, I clicked
name box on formula bar and keyed in a name. After hitting
enter, the name was gone. I then satisfied myself that the
range name was there, while still in xcel.

Then went to access. File, then Getdata, then Import and
pulled up target Xcel table. Chose select range and no
ranges show up. When I select worksheet, it's all there.

Since I will be appending data weekly, I thought this
would be the most efficient procedure.

1st ?. Why isn't access displaying my xcel range name?
2nd ?. Is this the best way to do this?

Thank you to all for reading this.

Howard
 
Hi Howard,

Access just can't import non-contiguous ranges.

If the range you want to import is always going to be rows 2 to n with
column names in row 1, define the range as rows 1 to n.

If on the other hand you'll need to import rows 2 to n this week and n+1
to m next week, forget about including the column names; just ensure
that the order of the spreadsheet columns and the types of data in each
column match the fields in the table.

If (another if!) however the spreadsheet is the main data store,
consider using a linked table rather than importing the data each time.
That way, Access will always have the same data as is on the
spreadsheet.

Would appreciate help on this.

Have xcel spread sheet that will be growing and that
weekly needs to be appended to an access table.

To test my procedure, I created a three row spread sheet
and a row for column headers (as row 1). I then created a
range name using ctrl key to select and highlight rows 2
thru 4 (data rows) and then with control key, selected
header row. While all 4 rows were selected, I clicked
name box on formula bar and keyed in a name. After hitting
enter, the name was gone. I then satisfied myself that the
range name was there, while still in xcel.

Then went to access. File, then Getdata, then Import and
pulled up target Xcel table. Chose select range and no
ranges show up. When I select worksheet, it's all there.

Since I will be appending data weekly, I thought this
would be the most efficient procedure.

1st ?. Why isn't access displaying my xcel range name?
2nd ?. Is this the best way to do this?

Thank you to all for reading this.

Howard
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top