Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are already
open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

VBA doesn't support that. You can use an excel 4 macro command. You can
put a linking formula in a cell, and read the value returned, then clear the
formula (usually the fastest). You can use ADO to treat the closed workbook
as a data base. You can open the workbook, get the data, then close it.

Probably other ways as well.

--
Regards,
Tom Ogilvy


"Pete at Sappi Fine Paper"
wrote in message ...
Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are already
open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

try this....

Sub test()

Dim x As Excel.Workbook

Set x = Workbooks.Open("c:\book4.xls")
End Sub

Swap "book4.xls" for your workbook name.

Good luck.

--
www.alignment-systems.com


"Pete at Sappi Fine Paper" wrote:

Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are already
open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

Thanks John. Unfortunately this solution opens the spreadsheet in question,
which I am trying to avoid. It's a huge spreadsheet and takes forever to
open. I need to access the data in the spreadsheet without opening it.

"John.Greenan" wrote:

try this....

Sub test()

Dim x As Excel.Workbook

Set x = Workbooks.Open("c:\book4.xls")
End Sub

Swap "book4.xls" for your workbook name.

Good luck.

--
www.alignment-systems.com


"Pete at Sappi Fine Paper" wrote:

Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are already
open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

Thanks Tom. I was afraid of that. If it won't take too long to explain,
what is ADO?

"Tom Ogilvy" wrote:

VBA doesn't support that. You can use an excel 4 macro command. You can
put a linking formula in a cell, and read the value returned, then clear the
formula (usually the fastest). You can use ADO to treat the closed workbook
as a data base. You can open the workbook, get the data, then close it.

Probably other ways as well.

--
Regards,
Tom Ogilvy


"Pete at Sappi Fine Paper"
wrote in message ...
Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are already
open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

Pete at Sappi Fine Paper shared this with us in
microsoft.public.excel.programming:

Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are
already open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

Read this page:

Copy a range from closed workbooks (ADO)
http://www.rondebruin.nl/ado.htm

And this also:
http://www.rondebruin.nl/tips.htm

It's not a direct answer to your question, but a good workaround.

--
Amedee Van Gasse

"Pete at Sappi Fine Paper"
skrev i melding ...
Thanks Tom. I was afraid of that. If it won't take too long to explain,
what is ADO?

Hi Pete

ActiveX Data Objects -a great common set of objects for all kinds of
databases. See Carl Prothman's site
http://www.carlprothman.net/Default.aspx?tabid=80
for expert insight, and
http://erlandsendata.no/english/inde...dacimportwbado
on how to use this with Excel as a database.

HTH. Best wishes Harald

http://msdn.microsoft.com/library/de...mmersguide.asp

--
Regards,
Tom Ogilvy


"Pete at Sappi Fine Paper"
wrote in message ...
Thanks Tom. I was afraid of that. If it won't take too long to explain,
what is ADO?

"Tom Ogilvy" wrote:

VBA doesn't support that. You can use an excel 4 macro command. You
can
put a linking formula in a cell, and read the value returned, then clear
the
formula (usually the fastest). You can use ADO to treat the closed
workbook
as a data base. You can open the workbook, get the data, then close it.

Probably other ways as well.

--
Regards,
Tom Ogilvy


"Pete at Sappi Fine Paper"

wrote in message
...
Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are
already
open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

Thanks!

"Amedee Van Gasse" wrote:

Pete at Sappi Fine Paper shared this with us in
microsoft.public.excel.programming:

Hi folks.

The Workbooks keyword only seems to work for spreadsheets that are
already open. I can't seem to say, for example:

wkb = Workbooks("Y:\Downloads\6-7.xls")

So how do I refer to cells in that spreadsheet without opening it?

Any help would be much appreciated! :)

-Pete

Read this page:

Copy a range from closed workbooks (ADO)
http://www.rondebruin.nl/ado.htm

And this also:
http://www.rondebruin.nl/tips.htm

It's not a direct answer to your question, but a good workaround.

--
Amedee Van Gasse