Visual Basic, Excel Workbook vs. Worksheet

Mike Haverdi
#1 / 4

Excel Workbook vs. Worksheet

I have just recently been experimenting with using VB 4.0 Professional
to open an Excel file and interact with it. So far, things are going
pretty well. I've been able to access and change cell contents,
export charts, etc. There is one thing that has been bothering me
though. When I open an existing Excel Workbook, I get a Worksheet
object (the first worksheet in the workbook) instead of the Workbook
object. My code looks like this:

Dim X as Object

Set X = GetObject(myPath + myXLSfile)

The "X" Object I get back is a Worksheet. Shouldn't it be a Workbook?
Has anybody else noticed this problem? I'd appreciate any input on
this. Thanks,

Sat, 26 Dec 1998 03:00:00 GMT

Mike Thomps
#2 / 4

Excel Workbook vs. Worksheet

says...

Quote:

>though. When I open an existing Excel Workbook, I get a Worksheet
>object (the first worksheet in the workbook) instead of the Workbook
>object. My code looks like this:

> Dim X as Object

> Set X = GetObject(myPath + myXLSfile)

>The "X" Object I get back is a Worksheet. Shouldn't it be a Workbook?
>Has anybody else noticed this problem? I'd appreciate any input on
>this. Thanks,

X is actually a workbook, but like in Excel, only one worksheet can be active
at a time (and the default is the first sheet in the book). You can still
access the data in other sheets:
Var1 = X.Sheets("Sheet2").Range("A1").Value
Var2 = X.Sheets("Sheet9").Range("A3").Value

Or you can change which sheet is active, so you do not have to specify which
sheet you want data from:

X.Sheets("Sheet2").Select
Var1 = X.Range("A1").Value
X.Sheets("Sheet9").Select
Var2 = X.Range("A3").Value

Mike Thompson
BHP NZ Steel
Auckland
New Zealand

Sun, 27 Dec 1998 03:00:00 GMT

Brian Dav
#3 / 4

Excel Workbook vs. Worksheet

Quote:

>>object. My code looks like this:

>> Dim X as Object

>> Set X = GetObject(myPath + myXLSfile)

>>The "X" Object I get back is a Worksheet. Shouldn't it be a Workbook?
>>Has anybody else noticed this problem? I'd appreciate any input on
>>this. Thanks,

>X is actually a workbook, but like in Excel, only one worksheet can be active
>at a time (and the default is the first sheet in the book). You can still
>access the data in other sheets:
> Var1 = X.Sheets("Sheet2").Range("A1").Value
> Var2 = X.Sheets("Sheet9").Range("A3").Value

>Or you can change which sheet is active, so you do not have to specify which
>sheet you want data from:

> X.Sheets("Sheet2").Select
> Var1 = X.Range("A1").Value
> X.Sheets("Sheet9").Select
> Var2 = X.Range("A3").Value

Good tip Mike, but I have another problem. I use an OLE OBJECT instead of
creating X. This is basically so I can see the sheet as I change values and
attributes. However, if I use the Open command I still get the blank workbook
and sheet. How do I set the visible object to be myworbook/worksheet? Do I
need to imbed an actual Excel object and not use the generic OLE OBJECT? Or
do I need to DIM X and somehow set the OLE object to X? This is really
bugging me.

-brian davis

Fri, 08 Jan 1999 03:00:00 GMT

Mike Thomps
#4 / 4

Excel Workbook vs. Worksheet

Quote:

>Good tip Mike, but I have another problem. I use an OLE OBJECT instead of
>creating X. This is basically so I can see the sheet as I change values and
>attributes. However, if I use the Open command I still get the blank workbook
>and sheet. How do I set the visible object to be myworbook/worksheet? Do I
>need to imbed an actual Excel object and not use the generic OLE OBJECT? Or
>do I need to DIM X and somehow set the OLE object to X? This is really
>bugging me.

> -brian davis

The way I have done this is to create an excel object which I can manipulate
and change, and also have the workbook linked to an OLE object. AFAIK there is
no way to set the OLE object to the excel object. This is how I have done it:

Public xl As Excel.Application
Public xlb As Excel.Workbook

Set xl = CreateObject("Excel.Application")
Set xlb = xl.Workbooks.Open("C:\Blah\Blah.xls")

OLE1.SourceDoc = "C:\Blah\Blah.xls"
OLE1.SourceItem = "Sheet1"
OLE1.Action = 1

To give me more control over when the updates to the workbook are shown I also
set the Ole containers UpdateOptions property to Manual. Then the only time it
is updated is when I issue the 'OLE1.Update' command.

I hope this helps, let me know if you need more help.

Mike Thompson
BHP NZ Steel Ltd
Auckland
New Zealand

Sun, 10 Jan 1999 03:00:00 GMT