How can I pull data from a specific cell from a closed worksheet in
VBA?

Not sure but I think that
Workbook("wb1.xls").Worksheet("Sheet1").Range("A2" ) only works if the
worksheet is open.

Any help would be appreciated,

Matts


--
mattsvai
------------------------------------------------------------------------
mattsvai's Profile: http://www.excelforum.com/member.php...o&userid=31134
View this thread: http://www.excelforum.com/showthread...hreadid=508116

If the workbook is closed you need to add the full path

this is a cell formula not a macro

='C:\YourDirectory\[yourfile.xls]Sheet1'!A1

You may need to change sheet1 to the name of your sheet and A1 is the cell
you are looking for,

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200602/1

Thanks for the reply,

unfortunately I still get the nasty "Subscript out of range" error whe
I do that:

var = Workbooks("C:\User1.xls").Worksheets("Record").Ran ge("A2")

ARRGG

--
mattsva
-----------------------------------------------------------------------
mattsvai's Profile: http://www.excelforum.com/member.php...fo&userid=3113
View this thread: http://www.excelforum.com/showthread.php?threadid=50811

No, adding a path will not work. The workbooks collection is only for open
workbooks. there is no open workbook with a name of
Workbooks("C:\User1.xls"), so you get subscript out of range.

VBA doesn't support direct references to closed workbooks.

If you only want a single value, the fastest is to put a link in a cell and
pick up the value returned. You can clen clear the link.

If your data in the closed workbook is organized like a database, you can
use ADO to retrieve the data.

--
Regards,
Tom Ogilvy


"mattsvai" wrote in
message ...

Thanks for the reply,

unfortunately I still get the nasty "Subscript out of range" error when
I do that:

var = Workbooks("C:\User1.xls").Worksheets("Record").Ran ge("A2")

ARRGG!


--
mattsvai
------------------------------------------------------------------------
mattsvai's Profile:
http://www.excelforum.com/member.php...o&userid=31134
View this thread: http://www.excelforum.com/showthread...hreadid=508116

"mattsvai" wrote in
message ...

Thanks for the reply,

unfortunately I still get the nasty "Subscript out of range" error when
I do that:

var = Workbooks("C:\User1.xls").Worksheets("Record").Ran ge("A2")

ARRGG!

No way for Excel VBA to read closed workbooks unless you
create a DAO/ADO recordset from the closed file.

If your need is for a single cell reading you can send the formula
='C:\YourDirectory\[yourfile.xls]Sheet1'!A1
to a cell and then read that cell.

e.g.
[H90].Formula = "='C:\Document\Excel\XLS\[BBCC.xls]Sheet1'!E15"
MsgBox [H90]

Bruno

Hello Matts,

I suggest to look he
http://www.erlandsendata.no/english/...acimportadotxt

The author provides other load programs, too - but this one should work
for your .xls files.

Regards,
Bernd

The simplest way would be to establish a link from the closed workboo
to the open book. Your macro then could read the value of the linke
range

--
jimmike
-----------------------------------------------------------------------
jimmike1's Profile: http://www.excelforum.com/member.php...fo&userid=3107
View this thread: http://www.excelforum.com/showthread.php?threadid=50811

jimmike1 Wrote:
The simplest way would be to establish a link from the closed workboo
to the open book. Your macro then could read the value of the linke
range.

Hummm... no quite getting what u mean there Jimmy..

--
mattsva
-----------------------------------------------------------------------
mattsvai's Profile: http://www.excelforum.com/member.php...fo&userid=3113
View this thread: http://www.excelforum.com/showthread.php?threadid=50811

mattsvai Wrote:
Hummm... no quite getting what u mean there Jimmy...
Designate a range in your open workbook as a link to the value you want
in the closed one. It would be helpful to name the range containing the
needed value. Name it, for example, WeeklySales.Then the formula for
the range in the open book would look like this:
=ClosedBook!WeeklySales.

Your VBA procedure would access the range in the open book. Say it is
Range B4 in a sheet called Sales.

It would simply refer to Range("Sales!B4").Value (or whatever)


--
jimmike1
------------------------------------------------------------------------
jimmike1's Profile: http://www.excelforum.com/member.php...o&userid=31076
View this thread: http://www.excelforum.com/showthread...hreadid=508116