Thanks, Peter.

That got me going, but I had to use:

var3 = xlApp.ConvertFormula(var3, xlR1C1, xlA1)

to convert from R1C1 notation to A1 notation.

I do realise that when using vba to do something selecting the range of
cells is not required. However in this instance, the Excel range is being
obtained from a Document Variable in Word and the idea is that the code
should select the Excel range so that the user can modify it.


--
Regards,

Doug Robbins - Word MVP

"Peter T" <peter_t@discussions wrote in message
...
If, as it appears, the cell_ref is in R1C1 notation, convert it to A1
style

sAddrA1 = xlApp.ConvertFormula(sAddrR1C1, xlA1, xlR1C1)

if you are using late binding change xlA1 & xlR1C1 to 1& and -4150
respectively

Assuming the above gets things working, you could do something like the
following

var = "Sheet1!R1C1:R6C3"
var = xlApp.ConvertFormula(var, xlA1, xlR1C1) 'Sheet1!$A$1:$C$6

Set datarange = xlApp.ActiveWorkbook.Range(var)
datarange.parent.activate ' assumes var includes sheetname
datarange.select
datarange.copy

The activate and selection lines are not required, above assumes the
requisite workbook is already active.

Regards,
Peter T


"Doug Robbins - Word MVP" wrote in message
...
Automating Excel from Word (2007), I have a variable var3 that contains a
reference of the form SheetName!R#C#:R#C#

Using, and without Activating Excel

xlApp.GoTo Reference:=var3
Set datarange = xlApp.Selection
datarange.Copy

I can copy the required information to the clipboard so that it can be
pasted into Word.

If however, I make Excel visible, the nearest that I can come to actually
selecting the range is to use

xlApp.Visible = True
var2 = Left(var3, InStr(var3, "!") - 1)
xlApp.Worksheets(var2).Activate
xlApp.GoTo Reference:=var3

and adding

Set datarange = xlApp.Selection
datarange.Copy

to the above, does not necessarily cause the required range of cells to
be copied (not that I really need to copy them in this situation,)

Activating the Worksheet was necessary to get the Worksheet containing
the range to be made the active Worksheet.

When Excel is visible, is there a way to get it to actually select a
specific range of cells?

Thanks for any assistance.
--

Doug Robbins - Word MVP