Automation: Excel instance references

[AppStaff]

I have a program that is structured like this

sub Main
Dim objXL as excel.application
Dim objwkb as excel.workbook
dim objsht as excel.worksheet

Set Objxl=..
set objwkb=..
set objsht=...

Do stuff here

Call Sub Procedure

Call Sub Procedure

set objsht=nothing
set objwkb=nothing
set objxl=nothing

end Main

My Problem/Question is with the sub Procedures.

If I run the procedure with Excel closed everything works fine. If excel is open then it seems to get confused about which instance of excel to use. I tried to reference the work book name and pass it on to the sub procedures but it still doesn't recognize the correct instance. How do I ensure that the different parts of my program know the right instance to act against?

I'm fairly inexperienced at automation so any code samples or links are greatly appreciated! Thanks!

 

[SkipVought]

Hi,

Can't say without looking at how you are referencing the excel objects in each procedure.

That's the code that you need to post.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[AppStaff]

This is one way i tried in the sub procedures.

objwkb is a string name of the workbook being acted on.

Set db = CurrentDb
Set objXL = Excel.Application
Set objsht = objXL.Workbooks(objwkb).Worksheets(2)

This works fine unless I have another instance of excel open when i run it. Then it errors out indicating it can't find the workbook.

I also tried objXL= Getobject(,"Excel.application"). Same result. Thanks for responding skip.

 

[SkipVought]

Are you sure that objwbk is a string and not a workbook object, in which case you could ought to be...

Set objwbk = objxl.Workbooks.Open "some workbook name"
Set objsht = objwkb.Worksheets(2)

What else do you do in this procedure?

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[AppStaff]

Yup, its a string. Unconvential naming I know. In my experimenting I didnt change my convention to match my method.

It works fine unless I run it with excel already running and I don't have clear enough understanding of how the various instances of an application object are declared and recognized.

 

[SkipVought]

Look at the HELP on GetObject. There is a function there that tests if Excel is running.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]