Basic VBA question...

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

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

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

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").Range("A2")

ARRGG!

Hello Matts,

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

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

Regards,
Bernd

Originally Posted by jimmike1

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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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").Range("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" <[email protected]> wrote in
message news:[email protected]...
>
> 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").Range("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

Originally Posted by mattsvai

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)