importing named ranges in Excel into tables in Access - how to?

[Guest]

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?

 

[Van T. Dinh]

Are your Excel ranges contiguous?

IIRC, Access can only recognise contiguous ranges from Excel ...

 

[Guest]

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]

Sorry, I never had to import non-contiguous ranges and I don't have time for
full testing.

For a bit of related sample code, see the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/209924

 

[John Nurick]

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]
;

 

[Guest]

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.

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]
;

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?

 

[Guest]

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?

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.

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]
;

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?

:

Are your Excel ranges contiguous?

IIRC, Access can only recognise contiguous ranges from Excel ...

--
HTH
Van T. Dinh
MVP (Access)



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]

To use append queries as I suggested:

1) In Excel, name a range that covers the entire rectangle you're
interested in. Don't worry about the columns you want to skip, but make
sure there are no merged cells in the range. Make sure that the first
row of the range contains a heading for each column (which must all be
legal Access field names). The headings of the columns you want to
import must be the same as the names of the fields in your Access table.

2) In your database, create a new query. Don't add a table, but switch
the query into SQL view.

3) Paste this
INSERT INTO MyTable
SELECT Field1, Field3, Field5
FROM [Excel 8.0;HDR=Yes;database=C:\Book.xls;].[RangeName]
;
into the query. Replace "MyTable" with the actual table name, replace
"Field1, Field3, Field5" with the names of the actual fields you want,
replace "C:\Book.xls" with the actual path and name of your workbook,
and replace "RangeName" with the actual name of the range.

4) Switch the query into datasheet view to make sure it's delivering the
expected results. If necessary you can use calculated fields in the
query (e.g. to change a data type to suit the Access field).

5) When you get the query correct, run it and the data will be appended
to the Access table.

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.

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]
;

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?

:

Are your Excel ranges contiguous?

IIRC, Access can only recognise contiguous ranges from Excel ...

--
HTH
Van T. Dinh
MVP (Access)



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]

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.

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?

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.

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

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?

:

Are your Excel ranges contiguous?

IIRC, Access can only recognise contiguous ranges from Excel ...

--
HTH
Van T. Dinh
MVP (Access)



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?

 

[Guest]

I linked the excel spreadsheet as a table in Access but the customer id
column in the Access table is not reflecting the data type as text so the
"numbers" aren't showing properly. The customer id column in excel is
formatted as text. Why wouldn't the formatting transfer over? What should I
do to fix it?

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.

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?

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.

:

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

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?

:

Are your Excel ranges contiguous?

IIRC, Access can only recognise contiguous ranges from Excel ...

--
HTH
Van T. Dinh
MVP (Access)



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?