Excel object in different subs

Hi,
Apologies if this is really basic!


I am creating an Excel object in one sub as so:-

sub form_load
Set objExcel = CreateObject("Excel.application")
<loads of other stuff.
end sub


Within that sub I can get Excel to do whatever I need..just great.

But, my program contains about a dozen subs some of which also need to
use the same instance of the Excel object as they will be used to read/
write/format the
same sheet as the sub above.

I keep getting errors when subs, other than the one that is creating
objExcel, execute
saying could not find or execute objExcel.

How do I make objExcel available to all my other subs?

Thanks,
Stuart

Pegasus
Sun Mar 23 05:20:20 CDT 2008


"Kryten" <Kryten68@googlemail.com> wrote in message
news:3592876d-1293-4d50-93eb-0789458c85c4@13g2000hsb.googlegroups.com...
> Hi,
> Apologies if this is really basic!
>
>
> I am creating an Excel object in one sub as so:-
>
> sub form_load
> Set objExcel = CreateObject("Excel.application")
> <loads of other stuff.
> end sub
>
>
> Within that sub I can get Excel to do whatever I need..just great.
>
> But, my program contains about a dozen subs some of which also need to
> use the same instance of the Excel object as they will be used to read/
> write/format the
> same sheet as the sub above.
>
> I keep getting errors when subs, other than the one that is creating
> objExcel, execute
> saying could not find or execute objExcel.
>
> How do I make objExcel available to all my other subs?
>
> Thanks,
> Stuart

Place the statement
Set objExcel = CreateObject("Excel.application")
into your main program instead of the subroutine.

Kryten
Sun Mar 23 05:46:51 CDT 2008

Hi Pegasus,

Here is what I have :-

Sub Main
Dim objExcel
Set objExcel = createobject("Excel.Application")
objExcel.visible = False
End Sub

Sub CommandButton1_Click
Dim a
a = objExcel.Range("A1").value
textbox1.Text = a
End Sub


When CommandButton1_Click is executed it gives an
"Object Required : objExcel" error.

Any thoughts?

Thanks,
Stuart

Kryten
Sun Mar 23 05:58:54 CDT 2008

Pegasus,
Just to confirm, when I move the content in sub main into sub
CommandButton1._Click
the whole thing works perfectly..but is not scaleable.

Appreciate the help..I am a VBScript newbie.

Stuart

ekkehard
Sun Mar 23 06:32:05 CDT 2008

Kryten schrieb:
> Hi Pegasus,
>
> Here is what I have :-
>
> Sub Main
> Dim objExcel
> Set objExcel = createobject("Excel.Application")
> objExcel.visible = False
> End Sub
>
> Sub CommandButton1_Click
> Dim a
> a = objExcel.Range("A1").value
> textbox1.Text = a
> End Sub
>
>
> When CommandButton1_Click is executed it gives an
> "Object Required : objExcel" error.

I'm not sure about the context your programs runs in. In your first
post you mentioned

sub form_load
Set objExcel = CreateObject("Excel.application")
<loads of other stuff.
end sub

now you have a Sub Main(). Who calls this?

To get you started, try:

' global Excel object (I'd prefer a name like goExcel do indicate "global"
' and "object"
Dim objExcel

' 'first' Sub to call
Sub Main() ' maybe form_load()
...
Set objExcel = createobject("Excel.Application")
...
End Sub

' other Sub(s) using global object
Sub CommandButton1_Click
Dim a
a = objExcel.Range("A1").value '
textbox1.Text = a
End Sub

' 'last' Sub to call
Sub form_unload()
...
... save changed workbook ??
objExcel.Quit
Set objExcel = Nothing ' if you insist on this
...
End Sub

Pegasus
Sun Mar 23 07:36:04 CDT 2008


"Kryten" <Kryten68@googlemail.com> wrote in message
news:6779af05-5cc6-4068-a77c-7ba37964d238@t54g2000hsg.googlegroups.com...
> Hi Pegasus,
>
> Here is what I have :-
>
> Sub Main
> Dim objExcel
> Set objExcel = createobject("Excel.Application")
> objExcel.visible = False
> End Sub
>
> Sub CommandButton1_Click
> Dim a
> a = objExcel.Range("A1").value
> textbox1.Text = a
> End Sub
>
>
> When CommandButton1_Click is executed it gives an
> "Object Required : objExcel" error.
>
> Any thoughts?
>
> Thanks,
> Stuart

As ekkehard mentioned, your code appears to have a problem
with its main/subroutine structure. You also have to open up a
worksheet. Try this sample code:

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.visible = True
objExcel.WorkBooks.Add
CommandButton1_Click

Sub CommandButton1_Click
Dim a
a = objExcel.Range("A1").value
objExcel.Range("B5") = 55
End Sub

Kryten
Sun Mar 23 09:06:40 CDT 2008

Thanks guys,
Got to the bottom of it now.

I'm actually writing the code in NSBasic/Desktop:-
http://www.nsbasic.com/desktop/

VBScript runs in this IDE with no modification..but the windows form
controls create all manner of subs, which you detected in my samples.

The answer for me was to place the relevant code outwith any subs at
all -apparently that makes them global to all subs in NSBasic. I
thought they would have needed to be in 'sub main' but I was wrong.

Thanks for your help and assistance all the same.

Cheers,
Stuart

Kryten
Sun Mar 23 09:10:09 CDT 2008

Actually, re-reading your first response Pegasus - you were bang on
the money there and then!
For some reason I read "sub" where you actually did specify "program".

After this project I think I'll stick to Powershell !

Cheers,
Stuart

noone
Sat Mar 29 08:24:37 CDT 2008

Il giorno Sun, 23 Mar 2008 03:10:09 -0700 (PDT), Kryten <Kryten68@googlemail.com> ha
scritto:
>I am creating an Excel object in one sub as so:-
>
>sub form_load
>Set objExcel = CreateObject("Excel.application")
><loads of other stuff.
>end sub
>Within that sub I can get Excel to do whatever I need..just great.
>But, my program contains about a dozen subs some of which also need to
>use the same instance of the Excel object as they will be used to read/
>write/format the
>same sheet as the sub above.
>I keep getting errors when subs, other than the one that is creating
>objExcel, execute
>saying could not find or execute objExcel.
>How do I make objExcel available to all my other subs?

I would declare objExcel otuside every sub, in the General Declarations.
dim objExcel
set objExcel=nothing

Not tested, but I think this way the content of objExcel will survive after pointing to
excel when the SET is executed in the Form_load subroutine.

--
Giovanni Cenati (Bergamo, Italy)
Write to user "Reventlov" and domain at katamail com
http://digilander.libero.it/Cenati (Esempi e programmi in VbScript)
--