How to define a range in a different Excel App

[nigelh]

I have an Excel 2010 VBA based application where one of the report outputs is to be annotated by the user and then sent out by email. Because the user wants to refer to open workbooks when annotating the report, I have been asked to generate the report in a new instance of Excel - it means they can keep the report open on one screen, while reviewing the source data on another. I can create the report, and fill out the details without too many problems, my problem comes when I try to format the report - and particularly add borders around various ranges. Usually, I set a range to be the cells I want to format, and everything works fine. However, when I try to define the range in the second Excel instance, I get "Global method of range failed..."

The code snippet below is extracted from the application, and shows the main steps of what I am trying to achieve. It is the indicated line that causes the problem.

<code>
Sub Test()
Dim subApp As Object
Dim wSubSheet1 As Worksheet
Dim oListRange As Range

Application.Caption = "Main Window"
Set subApp = New Excel.Application
With subApp
.Visible = True
.Caption = "Sub Window"
End With

AppActivate "Sub Window"
Set wSubSheet1 = subApp.Worksheets(1)

' Create the list
With wSubSheet1
.Cells(1, 1).Value = "List"
.Cells(2, 1).Value = "ABC"
.Cells(3, 1).Value = "DEF"
End With

' Define the range
'********* THIS IS THE LINE THAT FAILS ***********
Set oListRange = Range(wSubSheet1.Cells(1, 1), wSubSheet1.Cells(3, 1))

' Add Borders to the range
With oListRange
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
' and the rest ........
End Sub
</code>

Any thoughts or ideas gratefully received

 

[RoryA]

You just need to qualify the Range property too:

Set oListRange = wSubSheet1.Range(wSubSheet1.Cells(1, 1), wSubSheet1.Cells(3, 1))

 

[nigelh]

Thanks, Rory. Works a treat. It was the one combination of qualifiers I never thought to try!