How do I access an unopened spreadsheet from a program?

[Pete at Sappi Fine Paper]

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

[Tom Ogilvy]

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

[John.Greenan]

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

[Pete at Sappi Fine Paper]

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

[Pete at Sappi Fine Paper]

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

[Amedee Van Gasse]

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

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Pete at Sappi Fine Paper" <[email protected]>
wrote in message news:[email protected]...
> 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"
<[email protected]>
> > wrote in message
news:[email protected]...
> > > 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
> >
> >
> >

[Harald Staff]

"Pete at Sappi Fine Paper" <[email protected]>
skrev i melding news:[email protected]...
> 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

[Pete at Sappi Fine Paper]

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
>