Multiple Range TransferSpreadsheet

[Jeefgeorge]

I am trying to import a non-adjacent range of values. using a do loop and the
transferspreadsheet command. I am able to select the correct range, but I get
a runtime Error 3011 - could not find 'B9,D3,D4,D5,H9'

The table being populated has fields [StdNo], [ProjNo], [CIP], [LetDate],
[UnitCost], where [StdNo] is in cells B9:B##, and [UnitCost] is in cells
H9:H##. The [ProjNo] - D3, [CIP] - D4, and [LetDate] - D5 are only shown in
the 'header' of the spreadsheet, but this data needs to be attached to each
unit cost entered into the table (UnitCosts are entered from all projects,
and calculations are made based on the let date).

CODE:
Dim Path, Range As String, Row As Integer
Path = "P:\Projects - Current\ProjBook.xls"
Do
Row = 9
Range = "B" & Row & ",D3,D4,D5,H" & Row
DoCmd.TransferSpreadsheet acImport, 8, "BidDataImportTest",_
Path, False, Range
Loop Until Row = 20 'Stop Condition for row counter

 

[Jeefgeorge]

I have also tried
Range = "Bidtab!B" & Row & ",Bidtab!D3,Bidtab!D4,Bidtab!D5,Bidtab!H" & Row

Where Bidtab is the name of the worksheet within the workbook...
Also there is an error in my code i posted - Just before the Loop Unil Line
is
Row = Row + 1

 

[Klatuu]

You syntax for the range is not correct. It needs to resolve to something like
"SheetName$B1:Q99"
or "B:F"

 

[Jeefgeorge]

Is there a way to have non-adjacent cells in a range with out naming a range

 

[Klatuu]

Not in one transfer.

Even if you identify multiple named ranges, you can only import one at a time.
What you are doing would probably be best handled using Automation.

 

[Alex Dybenko]

Hi,
but perhaps if you identify one name range for non-adjacent range - it will
work?
Anyway - agree that Automation is the way to go

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com