referring to a closed workbook

[Jim H. Olesen]

Hi all

How do I refers to a range in a closed workbook in VBA?

Jim

[Bob Umlas]

You doesn't. You must opens the workbook.
Jim H. Olesen wrote in message <35A49E...@dong.dk>...

[Theo Haffmans]

If you make a reference to a single cell in the closed workbook, it can be
read wihout opening.
You can do that also via VB coding.
But the reference should be placed in a worksheet of your open workbook.

Jim H. Olesen <j...@dong.dk> wrote in article <35A49E...@dong.dk>...

[Keith N. Johnson]

You can *access* a known range from a closed workbook easily enough, whether
single-celled or not, and this may be enough to solve your problem. The
following all work fine -

MsgBox ExecuteExcel4Macro("'C:\[NotOpen.xls]Sheet1'!R1C1")
MsgBox ExecuteExcel4Macro("COUNTA('C:\[NotOpen.xls]Sheet1'!R1C1:R3C3)")
MsgBox ExecuteExcel4Macro("AVERAGE('C:\[NotOpen.xls]Sheet1'!R1C1:R2C2)")

What apparently cannot be done is to *operate* on a range from a closed
workbook, as in

MsgBox
ExecuteExcel4Macro("AVERAGE('C:\[NotOpen.xls]Sheet1'!R1C1:R2C2)^2")

or extract a multi-celled range *directly* into a VBA variant or array, ie
by somehow telling ExecuteExcel4Macro to return an array, as you would tell
Excel to enter an array-formula.

Theo Haffmans wrote in message
<01bdab48$82a21fe0$5069...@th570.sigutr.nl>...