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

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

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com

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

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.

--
Regards,
Tom Ogilvy

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

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.


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