resolving reference - Range of what?

[Krisztian Pinter]

Hello,

I'm automating Excel. I have a string, and I want to use it as a range
reference. for example the string will be
"MySheet!A2:A4;MyOtherSheet!C2". I have a Workbook object, and I need
to access the data.

the references are for the current workbook, but as you can see, it
can point to several worksheets of that book.

if I use the Range property of any Worksheet object, it does not allow
me to refer to other sheets. if I try to, an exception is raised.

(1) WB.Worksheets(1).Range(MyRef) ' will raise error if not for
Sheet1

there is a Range property of the Application object. if I use it in
VBA inside Excel, it seems to work fine, but I'm not really sure what
workbook it will use if my workbook is not the selected one.

(2) WB.Application.Range(MyRef) ' what workbook does it refer to?

is there a simple way to resolve a complex range reference?

Thanks in advance
Krisztian

 

[Chip Pearson]

Krisztian,

A Range object can refer to cells only on a single worksheet, so you can't
create a range from the string "MySheet!A2:A4;MyOtherSheet!C2".

 

[Tom Ogilvy]

A single range reference in VBA can not refer to more than one sheet. I am
not sure what you are getting using the Application.Range - it usually
refers to the activesheet.

 

[Krisztian Pinter]

Whoa, I didn't know that. Thanks for the information.

However, the question is still open. If the range does not contain
references to more than one sheet, it still can have a sheet name in
it, and I don't know how to get the Range object I want.