Select range from unopened workbook

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
 
J

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

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)
 
F

FSt1

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

FSt1

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

Don Guillett

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to reference a cell from anther workbook without opening it 0
Creating a defined name's Refersto range 2
All Workbook Saved except This Workbook - Part2 3
Copy data from workbook 2 to workbook 3 mystery! 2
Error Message - Macro to Unlink MS Queries from Workbook 0
Save As and new workbook new name used in existing code 8
ERROR: subscript out of range 6
Open four Wbooks, copy four columns from each to Master Wbook 16

Top