Importing from Excel by cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

m stroup - 04 May 2007 18:17 GMT

I have an excel file which contains data in non-contiguous cells. I want to
import data from these cells (which amounts to multiple ranges) to a
temporary table in access which would then feed into update queries. Is
there a way to designate multiple ranges:
DoCmd.TransferSpreadsheet acImport, , "TempImport", "C:\flight.xls",
False, "D3 And G3" or something to that affect?

Signature

Teach me to fish! Thanks for the help.
Pax, M

Reply to this Message

MyMel - 04 May 2007 20:51 GMT

Hi,

I hope this link will help you solve your problem
http://www.accessmonster.com/Uwe/Forum.aspx/access-formscoding/1139/Excel-Import

> I have an excel file which contains data in non-contiguous cells. I want to
> import data from these cells (which amounts to multiple ranges) to a
> temporary table in access which would then feed into update queries. Is
> there a way to designate multiple ranges:
> DoCmd.TransferSpreadsheet acImport, , "TempImport", "C:\flight.xls",
> False, "D3 And G3" or something to that affect?

Reply to this Message

John Nurick - 04 May 2007 23:15 GMT

No, you can't import from a non-contiguous range.

You can import non-contiguous columns from a contiguous range by using a
query with syntax like this:

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

and you can import from one-cell ranges using the same syntax.

Or you could add a sheet to the workbook which brings the scattered
values into a contiguous and importable range.

>I have an excel file which contains data in non-contiguous cells. I want to
>import data from these cells (which amounts to multiple ranges) to a
>temporary table in access which would then feed into update queries. Is
>there a way to designate multiple ranges:
> DoCmd.TransferSpreadsheet acImport, , "TempImport", "C:\flight.xls",
>False, "D3 And G3" or something to that affect?

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message