importing SOME columns of excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
John Nurick wrote ...
Access cannot import a range of non-contiguous cells.
Click to expand...

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.

--
 
Back
Top