Select range from unopened workbook

  • Thread starter Thread starter Andrew
  • Start date Start date
A

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
 
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.
 
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
you are right. but nothing in his post made me think of that.
so i guess i got turned around.

regards
FSt1

Don Guillett said:
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 said:
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
Click to expand...

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

regards
FSt1

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

regards
FSt1

Don Guillett said:
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 said:
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
.
Click to expand...

.
Click to expand...
Click to expand...
 
so?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
FSt1 said:
but then if the workbook were closed(not opened) excel would insert the
entire file path.

regards
FSt1

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

regards
FSt1

Don Guillett said:
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
.


.
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

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
.
.
Click to expand...
Click to expand...
Click to expand...

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