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

If a named range exists then....

Sheets("Sheet1").Range("myNamed Range").Select

Or if a specific range

Sheets("Sheet1").Range("A1:A2").Select


--

Regards,
Nigel
(E-Mail Removed)



"Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>

If you want to select a range of cells, the worksheet that those cells are
on has to be active. But there is rarely a need to select cells to do
anything.

--
__________________________________
HTH

Bob

"Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>

Thanks, Nigel.

However, using either

xlApp.Worksheets(3-Valuation Statement).Range(R6C3:R9C3).Select



or



xlApp.Worksheets("3-Valuation Statement").Range("R6C3:R9C3").Select



does not result in the range R6C3:R9C3 on Worksheet 3-Valuation Statement
being selected.



Using



xlApp.Worksheets(3-Valuation Statement).Activate



does result in the sheet being activated whereas



xlApp.Worksheets("3-Valuation Statement").Activate



does not. From that I surmise that the quote marks are not required when a
string variable is being supplied as in



bmname = Selection.Range.Bookmarks(1).Name 'This refers to a selection in a
Word document
If InStr(bmname, "Chart") = 0 Then
MsgBox "No chart selected."
Exit Sub
End If
var3 = ActiveDocument.Variables(bmname).Value
var3 = Mid(var3, 4, Len(var3) - 11)
var2 = Left(var3, InStr(var3, "!") - 1)
var3 = Mid(var3, InStr(var3, "!") + 1)
xlApp.Worksheets(var2).Activate
xlApp.Worksheets(var2).Range(var3).Select
xlApp.Visible = True



In the above code, the string supplied to var2 is the string "3-Valuation
Statement" without the quotes and the string supplied to var3 is "R6C3:R9C3"
also without the quotes.



Any other ideas?



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Nigel" <nigel-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If a named range exists then....
>
> Sheets("Sheet1").Range("myNamed Range").Select
>
> Or if a specific range
>
> Sheets("Sheet1").Range("A1:A2").Select
>
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>

Hi Bob,

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.

Here is the segment of code that is being used

bmname = Selection.Range.Bookmarks(1).Name 'This refers to a selection in a
Word document
If InStr(bmname, "Chart") = 0 Then
MsgBox "No chart selected."
Exit Sub
End If
var3 = ActiveDocument.Variables(bmname).Value
var3 = Mid(var3, 4, Len(var3) - 11)
var2 = Left(var3, InStr(var3, "!") - 1)
var3 = Mid(var3, InStr(var3, "!") + 1)
xlApp.Worksheets(var2).Activate
xlApp.Worksheets(var2).Range(var3).Select
xlApp.Visible = True

Any other ideas?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you want to select a range of cells, the worksheet that those cells are
> on has to be active. But there is rarely a need to select cells to do
> anything.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>

Doug,

Some of that code is not clear to me, I am not a Wordie.

What is in var2 and var3 when you move to Excel?

Is it a chart sheet you are looking at, if so does

xlApp.Sheets(var2).Activate

solve it?

--
__________________________________
HTH

Bob

"Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Bob,
>
> 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.
>
> Here is the segment of code that is being used
>
> bmname = Selection.Range.Bookmarks(1).Name 'This refers to a selection in
> a Word document
> If InStr(bmname, "Chart") = 0 Then
> MsgBox "No chart selected."
> Exit Sub
> End If
> var3 = ActiveDocument.Variables(bmname).Value
> var3 = Mid(var3, 4, Len(var3) - 11)
> var2 = Left(var3, InStr(var3, "!") - 1)
> var3 = Mid(var3, InStr(var3, "!") + 1)
> xlApp.Worksheets(var2).Activate
> xlApp.Worksheets(var2).Range(var3).Select
> xlApp.Visible = True
>
> Any other ideas?
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> If you want to select a range of cells, the worksheet that those cells
>> are on has to be active. But there is rarely a need to select cells to do
>> anything.
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>>
>>
>>
>
>

Hi Bob,

After each of the lines of code, I have inserted as comments the
corresponding values of Var2 and Var3

var3 = ActiveDocument.Variables(bmname).Value
'var3 = "."3-Valuation Statement!R6C3:R9C3" \a \p
var3 = Mid(var3, 4, Len(var3) - 11)
'var3 = 3-Valuation Statement!R6C3:R9C3
var2 = Left(var3, InStr(var3, "!") - 1)
'var2 = 3-Valuation Statement
var3 = Mid(var3, InStr(var3, "!") + 1)
'var3 = R6C3:R9C3
xlApp.Worksheets(var2).Activate
xlApp.Worksheets(var2).Range(var3).Select
xlApp.Visible = True

3-Valuation Statement is the name of the worksheet and R6C3:R9C3 is the
range on that sheet that is to be selected.

However, using a modification of the ConvertFormula suggested by Peter T to
conver the R6C3:R9C3 to $C$6:$C$9$C$6:$C$9 notation, I have been able to
achieve what I was after.

--
Regards,.

Doug Robbins - Word MVP

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Doug,
>
> Some of that code is not clear to me, I am not a Wordie.
>
> What is in var2 and var3 when you move to Excel?
>
> Is it a chart sheet you are looking at, if so does
>
> xlApp.Sheets(var2).Activate
>
> solve it?
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi Bob,
>>
>> 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.
>>
>> Here is the segment of code that is being used
>>
>> bmname = Selection.Range.Bookmarks(1).Name 'This refers to a selection
>> in a Word document
>> If InStr(bmname, "Chart") = 0 Then
>> MsgBox "No chart selected."
>> Exit Sub
>> End If
>> var3 = ActiveDocument.Variables(bmname).Value
>> var3 = Mid(var3, 4, Len(var3) - 11)
>> var2 = Left(var3, InStr(var3, "!") - 1)
>> var3 = Mid(var3, InStr(var3, "!") + 1)
>> xlApp.Worksheets(var2).Activate
>> xlApp.Worksheets(var2).Range(var3).Select
>> xlApp.Visible = True
>>
>> Any other ideas?
>>
>> --
>> Hope this helps.
>>
>> Please reply to the newsgroup unless you wish to avail yourself of my
>> services on a paid consulting basis.
>>
>> Doug Robbins - Word MVP
>>
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> If you want to select a range of cells, the worksheet that those cells
>>> are on has to be active. But there is rarely a need to select cells to
>>> do anything.
>>>
>>> --
>>> __________________________________
>>> HTH
>>>
>>> Bob
>>>
>>> "Doug Robbins - Word MVP" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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
news:O%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>
>