Select range from unopened workbook

[Andrew]

I'm trying to take data from an unopened workbook. I copied the
following code directly from my "Excel 2003 VBA" book.

range("A12") =
Workbooks("BILLRATE.xls").Worksheets("BILLRATE").Cells(1,
1).range("A1")

I get a "subscript out of range error on this. The workbook
"BILLRATE" is in the working folder. And I can call it if I use
Workbooks("BILLRATE.xls").Open. But when I try to access the range, I
get the subscript out of range error.

Any ideas?
thanks

 

[JLGWhiz]

Make sure your spelling of the names is accurate. Make sure you actually
have a sheet named BILLRATE in the workbook. As a side note, why not just
use Range("A1") for your cell reference rather than the duplicative Cells(1,
1).Range("A1")? It does the same thing and saves space.

 

[FSt1]

hi
the line of code you are using is for open workbooks. to get data from a
closed workbook, you will have to use ADO.
See this site.
http://www.rondebruin.nl/ado.htm


Regards
FSt1

 

[Don Guillett]

Unless

range("A12").formula="=[BILLRATE.xls].BILLRATE!A1"

-- =[20100201.xls]Sheet1!$E$13

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[FSt1]

hi
you are right. but nothing in his post made me think of that.
so i guess i got turned around.

regards
FSt1

Unless

range("A12").formula="=[BILLRATE.xls].BILLRATE!A1"

-- =[20100201.xls]Sheet1!$E$13

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

hi
the line of code you are using is for open workbooks. to get data from a
closed workbook, you will have to use ADO.
See this site.
http://www.rondebruin.nl/ado.htm


Regards
FSt1

.

 

[FSt1]

but then if the workbook were closed(not opened) excel would insert the
entire file path.

regards
FSt1

hi
you are right. but nothing in his post made me think of that.
so i guess i got turned around.

regards
FSt1

Unless

range("A12").formula="=[BILLRATE.xls].BILLRATE!A1"

-- =[20100201.xls]Sheet1!$E$13

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

hi
the line of code you are using is for open workbooks. to get data from a
closed workbook, you will have to use ADO.
See this site.
http://www.rondebruin.nl/ado.htm


Regards
FSt1

:

I'm trying to take data from an unopened workbook. I copied the
following code directly from my "Excel 2003 VBA" book.

range("A12") =
Workbooks("BILLRATE.xls").Worksheets("BILLRATE").Cells(1,
1).range("A1")

I get a "subscript out of range error on this. The workbook
"BILLRATE" is in the working folder. And I can call it if I use
Workbooks("BILLRATE.xls").Open. But when I try to access the range, I
get the subscript out of range error.

Any ideas?
thanks
.

.

 

[Don Guillett]

so?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

but then if the workbook were closed(not opened) excel would insert the
entire file path.

regards
FSt1

hi
you are right. but nothing in his post made me think of that.
so i guess i got turned around.

regards
FSt1

Unless

range("A12").formula="=[BILLRATE.xls].BILLRATE!A1"

-- =[20100201.xls]Sheet1!$E$13

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
hi
the line of code you are using is for open workbooks. to get data
from a
closed workbook, you will have to use ADO.
See this site.
http://www.rondebruin.nl/ado.htm


Regards
FSt1

:

I'm trying to take data from an unopened workbook. I copied the
following code directly from my "Excel 2003 VBA" book.

range("A12") =
Workbooks("BILLRATE.xls").Worksheets("BILLRATE").Cells(1,
1).range("A1")

I get a "subscript out of range error on this. The workbook
"BILLRATE" is in the working folder. And I can call it if I use
Workbooks("BILLRATE.xls").Open. But when I try to access the range,
I
get the subscript out of range error.

Any ideas?
thanks
.


.

 

[Andrew]

so?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

but then if the workbook were closed(not opened) excel would insert the
entire file path.

regards
FSt1

hi
you are right. but nothing in his post made me think of that.
so i guess i got turned around.
regards
FSt1
:
Unless
range("A12").formula="=[BILLRATE.xls].BILLRATE!A1"
-- =[20100201.xls]Sheet1!$E$13
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
hi
the line of code you are using is for open workbooks. to get data
from a
closed workbook, you will have to use ADO.
See this site.
http://www.rondebruin.nl/ado.htm
Regards
FSt1
:
I'm trying to take data from an unopened workbook.  I copied the
following code directly from my "Excel 2003 VBA" book.
range("A12") =
Workbooks("BILLRATE.xls").Worksheets("BILLRATE").Cells(1,
1).range("A1")
I get a "subscript out of range error on this.  The workbook
"BILLRATE" is in the working folder.  And I can call it if I use
Workbooks("BILLRATE.xls").Open.  But when I try to access the range,
I
get the subscript out of range error.
Any ideas?
thanks
.
.

Thanks. I used the formula in the cell and that worked.