Problems Importing from Excel to Access

[Guest]

I have been trying to use the transferspreadsheet option in an Access macro.
When I specify the range (C3:C3) I get an error saying that F1 (which is not
the cell I'm trying to import) cannot be found in the table I'm importing
into. If I try to specify a multi range like A1:I51 and tell it to create a
new spreadsheet it names all the columns as F1, F2, etc. I have told the
macro that transfer type is import and that the file does NOT have field
names. What I am needing to do is is to extract specific cells from an excel
spreadsheet and populate the data into an access table. Can someone help me?
I am using Access and Excell 2003 Professional Edition. I haven't used Access
in a couple of years so am a bit rusty. PLEASE HELP!

 

[John Nurick]

I have been trying to use the transferspreadsheet option in an Access macro.
When I specify the range (C3:C3) I get an error saying that F1 (which is not
the cell I'm trying to import) cannot be found in the table I'm importing
into.

When you're importing from Excel into an existing Access table, the
field names in the table must match the column headings in the worksheet
(or vice versa). If you don't have column headings in the worksheet
range you're importing, Access's Jet database engine assigns default
headings F1, F2... - so if you specify a one-cell range to import, the
only possible field name is F1.

Rather than use TransferSpreadsheet, I'd import in this situation by
creating an append query that fetches the data from the worksheet and
insert it into the table. The syntax is like this:

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$A1:E100]
;

Or for a single cell

INSERT INTO TheTable
SELECT F1
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$C3:C3]
;

and you can of course use it to import non-contiguous columns, e.g.

SELECT F1, F3, F5
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$A1:E100]

 

[Guest]

Part of the issue is that from the spreadsheet I need to get different types
of data from same column. Example from Column C cell 3 is a date but cell 10
is a number. I have tried the syntax you gave me but it doesn't work when I
try to go get the other cells I need. WIll I need to write multiple queries
because of the way the data is laid out or can I not put it all in same
query? I tried to put in same query but keep getting a FROM error. Apparently
I'm rustier than I thought.

I have been trying to use the transferspreadsheet option in an Access macro.
When I specify the range (C3:C3) I get an error saying that F1 (which is not
the cell I'm trying to import) cannot be found in the table I'm importing
into.

When you're importing from Excel into an existing Access table, the
field names in the table must match the column headings in the worksheet
(or vice versa). If you don't have column headings in the worksheet
range you're importing, Access's Jet database engine assigns default
headings F1, F2... - so if you specify a one-cell range to import, the
only possible field name is F1.

Rather than use TransferSpreadsheet, I'd import in this situation by
creating an append query that fetches the data from the worksheet and
insert it into the table. The syntax is like this:

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$A1:E100]
;

Or for a single cell

INSERT INTO TheTable
SELECT F1
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$C3:C3]
;

and you can of course use it to import non-contiguous columns, e.g.

SELECT F1, F3, F5
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$A1:E100]

If I try to specify a multi range like A1:I51 and tell it to create a
new spreadsheet it names all the columns as F1, F2, etc. I have told the
macro that transfer type is import and that the file does NOT have field
names. What I am needing to do is is to extract specific cells from an excel
spreadsheet and populate the data into an access table. Can someone help me?
I am using Access and Excell 2003 Professional Edition. I haven't used Access
in a couple of years so am a bit rusty. PLEASE HELP!

 

[John Nurick]

You can do stuff like this, though it easily gets messy:

SELECT C3.F1 AS TheNumber, C5.F1 AS TheName, C6.F1 AS TheDate
FROM
[Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$C3:C3] AS C3,
[Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$C5:C5] AS C5,
[Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$C6:C6] AS C6
;

Can you add a worksheet to the workbook with a rectangular block of
formulas that gather the data you need from its scattered locationsinto
a neat and easy-to-import range?

PS: I should have said mentioned in my last post that you can alias the
default field names to your real ones:

SELECT F1 AS Something, F2 AS SomethingElse ...

Part of the issue is that from the spreadsheet I need to get different types
of data from same column. Example from Column C cell 3 is a date but cell 10
is a number. I have tried the syntax you gave me but it doesn't work when I
try to go get the other cells I need. WIll I need to write multiple queries
because of the way the data is laid out or can I not put it all in same
query? I tried to put in same query but keep getting a FROM error. Apparently
I'm rustier than I thought.

I have been trying to use the transferspreadsheet option in an Access macro.
When I specify the range (C3:C3) I get an error saying that F1 (which is not
the cell I'm trying to import) cannot be found in the table I'm importing
into.

When you're importing from Excel into an existing Access table, the
field names in the table must match the column headings in the worksheet
(or vice versa). If you don't have column headings in the worksheet
range you're importing, Access's Jet database engine assigns default
headings F1, F2... - so if you specify a one-cell range to import, the
only possible field name is F1.

Rather than use TransferSpreadsheet, I'd import in this situation by
creating an append query that fetches the data from the worksheet and
insert it into the table. The syntax is like this:

INSERT INTO TheTable
SELECT F1, F2, F3, F4, F5
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$A1:E100]
;

Or for a single cell

INSERT INTO TheTable
SELECT F1
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$C3:C3]
;

and you can of course use it to import non-contiguous columns, e.g.

SELECT F1, F3, F5
FROM [Excel 8.0;HDR=No;Database=C:\Book.xls;].[Sheet1$A1:E100]

If I try to specify a multi range like A1:I51 and tell it to create a
new spreadsheet it names all the columns as F1, F2, etc. I have told the
macro that transfer type is import and that the file does NOT have field
names. What I am needing to do is is to extract specific cells from an excel
spreadsheet and populate the data into an access table. Can someone help me?
I am using Access and Excell 2003 Professional Edition. I haven't used Access
in a couple of years so am a bit rusty. PLEASE HELP!