reference excel object created in sub 1

[danomaniac]

Hello all,
I've created a VB6 app. The first form sets up the parameters for an Excel app and the Excel object is created. After that, another form pops up with text boxes for the user to type in some data and in turn is dumped into the spreadsheet to create a real-time chart. My problem is, once the first form's sub is complete, I can't reference Excel. How do I create the Excel object so that I can reference it from anywhere in my VB app?

Thanks

 

[vb5prgrmr]

Declare your variables in a module with the public declaration. Then in your sub in the form you will be able to reference them.

 

[CajunCenturion]

Rather than declare your Excel objects in the Sub, create them with global scope (as public within a module) so that they may be accessed anywhere within the project. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ulwitch]

I figured I needed to do that, but I'm having trouble with getting it to work...

I've added a standard code module called module1.bas

in module1 under General Declarations, I've placed:

Public objexcel As Excel.Application
Public objWorkbook As Excel.Workbook
Public objWorksheet As Excel.Worksheet

Now, on my form, I have a command button that sets the variables:

Set objexcel = GetObject("", "excel.application&quot
Set objWorkbook = objexcel.Workbooks.Add
Set objWorksheet = ActiveSheet

This part works fine, but once the button is clicked, the variables are set, the sub ends, and my second form pops up and when I try to access the workbook, I get 'Object or With Block Variable not set.

Am I not declaring it correctly?

 

[johnwm]

Using code similar to what you've shown, try this:

On form1, command or whatever:

Dim myRange As Excel.Range
Set objexcel = GetObject("", "excel.application")
Set objWorkbook = objexcel.Workbooks.Add
Set objWorksheet = ActiveSheet
Set myRange = objWorksheet.Range("a1", "a2")
myRange.Cells(1, 1) = 3

on form 2 stick this in form load:

Dim myRange As Excel.Range
Set objexcel = GetObject("", "excel.application")
Set objWorkbook = objexcel.ActiveWorkbook
Set objWorksheet = ActiveSheet
Set myRange = objWorksheet.Range("a1", "a2")
MsgBox myRange.Cells(1, 1)

Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[danomaniac]

thanks johnwm,
I've gotten it to work, I don't think I did it the most 'elegant' way, but I ended up with this:

in a module, I declare the excel object, workbook, and worksheet.

Public objexcel As Excel.Application
Public objWorkbook As Excel.Workbook
Public objWorksheet As Excel.Worksheet


On form1, in a button, I set the variables with

Set objexcel = GetObject("", "excel.application&quot
Set objWorkbook = objexcel.Workbooks.Add
Set objWorksheet = ActiveSheet

Then, (here's the part that I had trouble with) just before form1's button click sub, I added:

Set form2.objexcel = objexcel
Set form2.objWorkbook = objWorkbook
Set form2.objWorksheet = objWorksheet

And that seemed to make it work.

Did I do the same thing as you, johnwm? Is one way better than the other, I'd like to know the best practice, so I don't teach myself bad habits.

Thanks for your help.

 

[danomaniac]

I just re-read my last post and it didn't make sense.. I left out a word:

I wrote this -
"Then, (here's the part that I had trouble with) just before form1's button click sub,"

But should have written this -
"Then, (here's the part that I had trouble with) just before form1's button click sub ENDS,"

makes more sense that way, sorry.

 

[johnwm]

Yep, we're both doing more or less the same thing.

I prefer to do my SET in the form where I'm using it, rather than on the previous form. That way if I re-use the idea (or code) elsewhere I can see what I did last time! Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[danomaniac]

That's a good idea. Thanks again for the help.