use range on transferspreadsheet to import excel to access in vba

Question

Hi
i want import Excel to Access by vba
but i want get only odd column No even (A,C,E,... column)
my code is:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "t1", fileName, True, "a:a,c:c,e:e"

but got error could not find this object(a:a,c:c,e:e)
what is solution?

Answer 1

Have a look at this link.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6bf0e093-6e3b-460b-b678-6d35bc7ef7c6/docmdtransferspreadsheet-acimport-from-specified-workbook-tab?forum=accessdev

It seems that range is define with letters for columns and numbers for rows.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"NYTTKGAUNDERLAG", strFolder3 & "NYTTKGAUNDERLAG" & ".xlsx",_
True, "Grupperat!A1:C1000"

I do not think it is possible to get only odd columns.
You have to get them all and filter afterwards or do the request three times.

Answer 2

There is way to save columns on virtual table then get odd column?

Answer 3

No. I that is not easy.

In the link are some not ideal options.
https://www.access-programmers.co.uk/forums/threads/use-multiple-ranges-in-excel-file-import.301680/

https://www.utteraccess.com/forum/index.php?showtopic=1267215