Ranged Cells to Access from Excel?

[Guest]

I would like to import specific data from Excel through a query, but am
having difficulty figuring this out on my own.

What I would like to do is Import cells CURRENT EMPL!A7:A180, CURRENT
EMPL!C7:C180, CURRENT EMPL!H7:H180 from an existing excel Datasheet
(VIHVEHAPP.xls), into a table (Table1) in Access, and I haven't got a clue
how to do this.

any detailed help on this would be ever-so-appreciated.

 

[Van T. Dinh]

Import through a Query? I haven't heard of that.

You can use the Menu File / Get External Data ... / Import to import the
data from Excel.

 

[Guest]

Maybe Import was the wrong word. What I'm looking to do is use an INSERT INTO
type of statement so that I can place certain data from a certain range of
cells into certain fields within a table. Does that make more sense? I'm
terrible at explaining what I think.

 

[Gary Walter]

Hi kremesch,

I am not an Excel expert, but
I believe you will need to define
range over *all columns*, then use
"Fx" to select just the 3 columns:

so if the "full range" is

A7:H180

and you only want the non-contiguous cols A,C, H
within that range

then,

use for
F1 -- A column <--***
F2 -- B column
F3 -- C column <--***
F4 -- D column
F5 -- E column
F6 -- F column
F7 -- G column
F8 -- H column <--***

so, if

name/path of excel file = C:\f.xls
sheet name = "yoursheet"
your sheet does not have a header row
table name = "yourtable"
you want to put col "A" data in field "fA" of "yourtable"
you want to put col "C" data in field "fC" of "yourtable"
you want to put col "H" data in field "fH" of "yourtable"


then your insert query might look like:

INSERT INTO yourtable (fA, fC, fH)
SELECT
F1,
F3,
F8
FROM
[Excel 8.0;HDR=NO;Database=C:\f.xls;].[yoursheet$A7:H180];

 

[Van T. Dinh]

There are a few alternatives:

1. Simply link the Excel spreadsheet to your database. The Excel
spreadsheet will appear as a linked Table and you can use this linked Table
for your INSERT INTO ... SQL

2. Instead of linking above, you can use the IN Clause in your INSERT INTO
.... SQL. Check the JET Reference Help on the IN Clause.

3. Use TransferSpreadsheet Method. With this mehod, you need to name the
Range in Excel. I know you can use the named Range in TransferSpreadsheet
but I am not sure whether you can use a named Range which is non-contiguous.