Re: importing named ranges in Excel into tables in Access - how to
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Fri, 20 Oct 2006 05:51:50 +0100
You can't link an external data source to an existing table. Link the
spreadsheet as a table with a name of its own, and then construct an
append query (in the normal Access way, not using the special syntax I
describe in my other post) to move the data into your existing table.
On Thu, 19 Oct 2006 15:15:03 -0700, TechyTemp
<TechyTemp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
John,
I have tried to link the spreadsheet in Excel which I just created today to
the Customer table in Access and am told that Access can't overwrite the
table. Why would this be?
"TechyTemp" wrote:
John,
I've never done this before and the directions you've given me don't look
the same as the book I have (Access 2002 Bible). I don't see any examples of
straight syntax used. Would you mind walking me thru this in a little more
detail? I'm confused.
"John Nurick" wrote:
PMFJI,
If it's a question of importing selected, non-contiguous columns from a
contiguous rectangular range, you can either
1) link or import the entire range to a temporary table, and use an
append query to select the data from the desired columns and insert it
into the table where you need it.
2) use an append query that links directly to the contiguous range with
syntax like these examples:
INSERT INTO MyExistingTable
SELECT Field1, Field3, Field5
FROM [Excel 8.0;HDR=Yes;database=C:\Book.xls;].[RangeName]
;
INSERT INTO MyExistingTable
SELECT F1 As Field1, F3 AS NextField, F5 AS OtherField
FROM [Excel 8.0;HDR=No;database=C:\Book.xls;].[Sheet1$A1:E100]
;
On Tue, 17 Oct 2006 08:58:02 -0700, TechyTemp
<TechyTemp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Van T. Dinh,
No. The ranges aren't contiguous. Does that mean I need to use another
method such as an append query? Would that work? If so, how would I write
the syntax? I've never done anything that advanced before. Could you walk
me thru that?
"Van T. Dinh" wrote:
Are your Excel ranges contiguous?
IIRC, Access can only recognise contiguous ranges from Excel ...
--
HTH
Van T. Dinh
MVP (Access)
"TechyTemp" <TechyTemp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:47913791-F3E9-43D7-BC50-2E7468D7A9D0@xxxxxxxxxxxxxxxx
I have set up named ranges in Excel which correspond to tables in Access
and
used the file/get external data/import feature in Access. When I select
the
named ranges option my ranges don't appear in the selection box. What am
I
doing wrong and/or is there another way to import the data from excel to
access?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- Follow-Ups:
- References:
- Re: importing named ranges in Excel into tables in Access - how to?
- From: Van T. Dinh
- Re: importing named ranges in Excel into tables in Access - how to
- From: John Nurick
- Re: importing named ranges in Excel into tables in Access - how to
- From: TechyTemp
- Re: importing named ranges in Excel into tables in Access - how to
- From: TechyTemp
- Re: importing named ranges in Excel into tables in Access - how to?
- Prev by Date: Re: importing named ranges in Excel into tables in Access - how to
- Next by Date: Re: import multiple text files into Access 2003(different file names)
- Previous by thread: Re: importing named ranges in Excel into tables in Access - how to
- Next by thread: Re: importing named ranges in Excel into tables in Access - how to
- Index(es):
Relevant Pages
|
