importing SOME columns of excel file

[Guest]

I want to import SOME columns of excel file (i.e. a1:a100;c1:c100;e1:e100) to access table with some number of columns(i.e. three). I tried do with "DoCmd.TransferSpreadsheet" like this:

XLSheet = "Sheet1!A1:A100;Sheet1!C1:C100;Sheet1!E1:E100"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
tableName, XLFile, False, XLSheet

But it doesn't work(I get error message)
Any suggestions of how can I do it

 

[Ivan]

Look at: http://www.mvps.org/access/modules/mdl0037.htm

Ivan

-----Original Message-----
I want to import SOME columns of excel file (i.e.

a1:a100;c1:c100;e1:e100) to access table with some number
of columns(i.e. three). I tried do
with "DoCmd.TransferSpreadsheet" like this:

 

[Guest]

FTP ????

Look at: http://www.mvps.org/access/modules/mdl0037.htm

Ivan
a1:a100;c1:c100;e1:e100) to access table with some number
of columns(i.e. three). I tried do
with "DoCmd.TransferSpreadsheet" like this:

 

[John Nurick]

Hi Kostas,

Access cannot import a range of non-contiguous cells. Instead, you can
either link or import the whole range and ignore the columns you don't
want, or else import the whole range and then delete the columns you
don't want.

 

[Jamie Collins]

John Nurick wrote ...

Access cannot import a range of non-contiguous cells.

MS Access can do this via a query e.g.

To import into an existing table:

INSERT INTO
MyExistingTable
SELECT
F1, F3, F5
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

To import into a new table:

SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
INTO
MyNewTable
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

Jamie.

--