Displaying an Excel Range
 

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

Displaying an Excel Range
 

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




"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

Displaying an Excel Range
 

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" 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

Displaying an Excel Range
 

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" wrote in message
...
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




"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

Displaying an Excel Range
 

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" wrote in message
...
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" 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

Displaying an Excel Range
 

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

Displaying an Excel Range
 

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" wrote in message
...
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" wrote in message
...
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" 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

Displaying an Excel Range
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" 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

Displaying an Excel Range
 

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