Transferspreadsheet Range

[Jeefgeorge]

The DoCmd.TransferSpreadsheet range should accept non-adjacent cell references.

i.e. Range = Sheet1$B9:Sheet1$C9,Sheet1$D3
Returns Cells: B9,C9,D3

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...4d53&dg=microsoft.public.access.modulesdaovba

 

[Chris O'C via AccessMonster.com]

What you're requesting is actually two separate ranges on the same
spreadsheet. To import multiple ranges, link to these cells in another sheet
in the same workbook in contiguous cells so the values in nonadjacent cells
on Sheet1 are adjacent on Sheet2. Example:

1 - In Sheet2, cell A1 put this equation:

=Sheet1!B9

2 - In Sheet2, cell B1 put this equation:

=Sheet1!C9

3 - In Sheet2, cell C1 put this equation:

=Sheet1!D3

Save the spreadsheet. In an Access procedure use Docmd.Transferspreadsheet
with the single range A1:C1 on Sheet2, like this:

DoCmd.TransferSpreadsheet acImport, 8, "Book1", _
"C:\Book1.xls", False, "Sheet2!A1:C1"

Chris
Microsoft MVP

 

[Jeefgeorge]

This works when you have control over the source workbook, and when the cell
references are simple....my original references i would like to import are:


ColNum = 72 'Start at Column H
RowNum = 9 'Start at Row 9
Do While ColNum < 91 'Stop at Column Z
ColLet = Chr (ColNum)
Do While While RowNum < 259 'Stop at Row 258
Range = "Bidtab$B" & RowNum & ",Bidtab$D3:Bidtab$D5,Bidtab$" &
ColLet & RowNum
DoCmd.TransferSpreadsheet acImport, 8, Table, Path, False, Range
RowNum = RowNum + 1
Loop
ColNum = ColNum +2
Loop


This is much faster to code than to re-arrange and entire workbook to select
specific cells