How do I access a variable from another instance of excel?

[Alan]

Hi All,

I have the following situation (simplified for this posting):

I have created a new instance of excel which is performing some
actions
that take a few minutes. I would like to have a variable (just an
integer or string perhaps - doesn't really matter which) that is
updated in that instance that I can then access from the first
application object to give the user progress information.

Example:

In the main application:

NewExcel is an Excel.application object (new)

I would like to be able to do something like this:

Application.Statusbar = NewExcel.ProgressCounter


Within the NewExcel Application:

Sub CodeStuff()

Dim ProgressCounter as Integer

Rem Start of code

Rem Do stuff

ProgressCounter = 1

Rem Do more stuff

ProgressCounter = 2

Rem Blah Blah

End Sub




Within the main application, the statusbar would be showing 1, 2, 3
etc as the NewExcel application make progress.

However, I cannot work out how to access the value of ProgressCounter
from within the main excel application - any ideas?

Hope that makes sense!

Thanks,

Alan.

[K Dales]

When you start newExcel, are you opening an existing workbook that has the
code module that contains "CodeStuff()"? How is CodeStuff() run; i.e. is it
an event procedure or is it triggered by user intervention (e.g.
commandbutton), or are you calling it from code?

Where I am going with this:
Is it possible to move CodeStuff() to your original (controlling)
application's code module instead of a module within the automated newExcel
app? Unless it is an Event Procedure, I don't see why you could not move the
code within your original calling procedure. You can use the same code as
long as you qualify all your references back to the Application by using
newExcel; e.g. NewExcel.Workbooks(1).ActiveSheet....

That way you could directly change the statusbar text within CodeStuff():

Sub CodeStuffAsPartOfOriginalStuff()

Rem Do what you would have done before starting up newExcel
Rem Then start up newExcel

Rem newExcel.Dostuff

Application.StatusBar = 1

Rem newExcel.Do more stuff

Application.StatusBar = 2

Rem Blah Blah

End Sub

If, however, CodeStuff needs to respond to events in newExcel, it is a bit
tougher. I don't know how to access a variable - even a global variable -
from an automated session (or if it is even possible). You might need to
define newExcel through a new Class module declared "With Events": In that
case you could even make ProgressCounter a property of the class... I suppose
this could work but would take a lot of coding.

"Alan" wrote:

>
> Hi All,
>
> I have the following situation (simplified for this posting):
>
> I have created a new instance of excel which is performing some
> actions
> that take a few minutes. I would like to have a variable (just an
> integer or string perhaps - doesn't really matter which) that is
> updated in that instance that I can then access from the first
> application object to give the user progress information.
>
> Example:
>
> In the main application:
>
> NewExcel is an Excel.application object (new)
>
> I would like to be able to do something like this:
>
> Application.Statusbar = NewExcel.ProgressCounter
>
>
> Within the NewExcel Application:
>
> Sub CodeStuff()
>
> Dim ProgressCounter as Integer
>
> Rem Start of code
>
> Rem Do stuff
>
> ProgressCounter = 1
>
> Rem Do more stuff
>
> ProgressCounter = 2
>
> Rem Blah Blah
>
> End Sub
>
>
>
>
> Within the main application, the statusbar would be showing 1, 2, 3
> etc as the NewExcel application make progress.
>
> However, I cannot work out how to access the value of ProgressCounter
> from within the main excel application - any ideas?
>
> Hope that makes sense!
>
> Thanks,
>
> Alan.
>
>
>
>

[mark]

Another way to do it would be to throw the variable from your running Excel
process out to a text file, and read it in to the other Excel instance.

But, when I your posting the first time, I was wondering if you had thought
about using a Userform with it's ShowModal property set to 0. If you did
that, the modeless form would continue to display as your job processed, and
anywhere you wanted to, you could pass the modeless form new data about the
process of the job.

As long as you put in a DoEvents just after the update of the modeless form,
it should visibly show up fine.

"Alan" wrote:

>
> Hi All,
>
> I have the following situation (simplified for this posting):
>
> I have created a new instance of excel which is performing some
> actions
> that take a few minutes. I would like to have a variable (just an
> integer or string perhaps - doesn't really matter which) that is
> updated in that instance that I can then access from the first
> application object to give the user progress information.
>
> Example:
>
> In the main application:
>
> NewExcel is an Excel.application object (new)
>
> I would like to be able to do something like this:
>
> Application.Statusbar = NewExcel.ProgressCounter
>
>
> Within the NewExcel Application:
>
> Sub CodeStuff()
>
> Dim ProgressCounter as Integer
>
> Rem Start of code
>
> Rem Do stuff
>
> ProgressCounter = 1
>
> Rem Do more stuff
>
> ProgressCounter = 2
>
> Rem Blah Blah
>
> End Sub
>
>
>
>
> Within the main application, the statusbar would be showing 1, 2, 3
> etc as the NewExcel application make progress.
>
> However, I cannot work out how to access the value of ProgressCounter
> from within the main excel application - any ideas?
>
> Hope that makes sense!
>
> Thanks,
>
> Alan.
>
>
>
>

[Alan]

"K Dales" <[email protected]> wrote in message
news:[email protected]...
>
> When you start newExcel, are you opening an existing workbook that
> has the code module that contains "CodeStuff()"? How is CodeStuff()
> run; i.e. is it an event procedure or is it triggered by user
> intervention (e.g. commandbutton), or are you calling it from code?
>

Hi,

Yes - the workbook being opened as an object within NewExcel is an
existing workbook with substantial code already therein. The progress
counter already forms part of that code, I just want to be able to
access it within the code that created the NewExcel instance.

>
> Where I am going with this:
> Is it possible to move CodeStuff() to your original (controlling)
> application's code module instead of a module within the automated
> newExcel app? Unless it is an Event Procedure, I don't see why you
> could not move the code within your original calling procedure. You
> can use the same code as long as you qualify all your references
> back to the Application by using newExcel; e.g.
> NewExcel.Workbooks(1).ActiveSheet....
>

{Snipped code example to keep this shorter}

>
>

I guess that would be an option, but not a particularly palatable one!

Also there is code in the subsidiary workbook (running under NewExcel)
that is events related (change events in particular).

>
> If, however, CodeStuff needs to respond to events in newExcel, it is
> a bit tougher. I don't know how to access a variable - even a
> global variable - from an automated session (or if it is even
possible).
> You might need to define newExcel through a new Class module
> declared "With Events": In that case you could even make
> ProgressCounter a property of the class...
> I suppose this could work but would take a lot of coding.
>

{Gulp!}

I have been meaning to get into class modules at some point, but I was
hoping for a simpler solution to this problem.

One other option would be to use a worksheet cell in the workbook
running under NewExcel and pass the progress counter to that cell,
which I can then reference from the main application
(NewExcel.Workbooks(1).Worksheets(1).Range("A1").value)

It just seems a bit clunky and that there should be an easy way to
reference a variable, but perhaps not.

Thanks,

Alan.

[Alan]

"mark" <[email protected]> wrote in message
news:[email protected]...
>
> Another way to do it would be to throw the variable from your
> running Excel process out to a text file, and read it in to the
> other Excel instance.
>

Hi Mark,

I thought of that, and then considered that using a worksheet cell
might be even simpler:

NewExcel.Workbooks(1).Worksheets(1).Range("A1").Value

A1 (probably in a very hidden worksheet) would then be passed the
current value of ProgressCounter as it changes and I can read the
value from there easy enough.

However, that seems a little clunky!

>
> But, when I your posting the first time, I was wondering if you had
> thought about using a Userform with it's ShowModal property set to
> 0. If you did that, the modeless form would continue to display as
> your job processed, and anywhere you wanted to, you could pass the
> modeless form new data about the process of the job.
>
> As long as you put in a DoEvents just after the update of the
> modeless form, it should visibly show up fine.
>

I agree that a UserForm could work well. However, I would *prefer* to
use the statusbar if possible. It is a posibility though - thanks for
the suggestion! I may change my mind about the statusbar in which
case a userform is the likely next option - I will cogitate on that!


Thanks for your help,

Alan.

[Peter T]

Hi Alan,

Another idea for you. First I'd better outline my understanding of your
task:

- Code in OldExcel starts new instance of Excel
- Open a workbook in NewExcel that does a lot of stuff (presumably using
RunAutoMacros ?)
- Update the statusbar in OldExcel while doing stuff

One way would be to put the name of a workbook that’s open in OldExcel into
NewExcel, then let code in the newly opened workbook use GetObject to grab a
reference to its parent - OldExcel.

''''''''''''''''''''''
'code in "C:\My Documents\Excel\CrossRefXL.xls"
'that will be opened in a new Excel

Option Explicit
Dim xlOld As Excel.Application

Sub auto_open()
Dim bGotXL As Boolean
Dim s As String

s = Application.StatusBar
Application.StatusBar = False

If s <> Application.StatusBar Then
On Error Resume Next
Set xlOld = GetObject(s).Parent
bGotXL = Not xlOld Is Nothing
End If

myProc bGotXL

Set xlOld = Nothing ' if not needed again

End Sub

Sub myProc(bStatus As Boolean)
Dim i As Long
Dim s As String
' in real life only update status every [say] 1% of the loop

s = "doing stuff "
For i = 1 To 10000
If bStatus Then xlOld.StatusBar = s & i
Next
If bStatus Then xlOld.StatusBar = False
End Sub

Sub auto_close()
Set xlOld = Nothing
End Sub

''''''''''''''''''''''''''''

''code in Old Excel
Sub test()
' might want wb & xlNew as Public if needed in future
Dim wb As Workbook
Dim xlNew As Excel.Application
Dim sName
Application.StatusBar = False
sName = "C:\My Documents\Excel\CrossRefXL.xls"

Set xlNew = New Excel.Application
xlNew.StatusBar = ThisWorkbook.Name

On Error Resume Next
Set wb = xlNew.Workbooks.Open(sName)
If wb Is Nothing Then
If MsgBox("Error loading " & sName & vbCr & _
"Quit hidden Excel Instance", vbYesNo) = vbYes Then
xl.Quit
End If
Else
wb.RunAutoMacros xlAutoOpen
xlNew.WindowState = xlNormal
xlNew.Visible = True
Set wb = Nothing
End If

Set xlNew = Nothing
End Sub
'''''''''''''''''''''''''''''''''

Small point - if there's any possibility that the workbook, whose name has
been passed to the statusbar in NewExcel, is open in multiple instances then
open a new workbook in OldExcel and pass that name (eg "Book5"). Beforehand,
make the new workbook hidden, close it later when done.

Big point - you say your "stuff" takes a few minutes. DON'T update in every
loop, every 1% or a few times per second is enough. So not the way shown in
my example.

Regards,
Peter


"Alan" <[email protected]> wrote in message
news:#[email protected]...
>
> Hi All,
>
> I have the following situation (simplified for this posting):
>
> I have created a new instance of excel which is performing some
> actions
> that take a few minutes. I would like to have a variable (just an
> integer or string perhaps - doesn't really matter which) that is
> updated in that instance that I can then access from the first
> application object to give the user progress information.
>
> Example:
>
> In the main application:
>
> NewExcel is an Excel.application object (new)
>
> I would like to be able to do something like this:
>
> Application.Statusbar = NewExcel.ProgressCounter
>
>
> Within the NewExcel Application:
>
> Sub CodeStuff()
>
> Dim ProgressCounter as Integer
>
> Rem Start of code
>
> Rem Do stuff
>
> ProgressCounter = 1
>
> Rem Do more stuff
>
> ProgressCounter = 2
>
> Rem Blah Blah
>
> End Sub
>
>
>
>
> Within the main application, the statusbar would be showing 1, 2, 3
> etc as the NewExcel application make progress.
>
> However, I cannot work out how to access the value of ProgressCounter
> from within the main excel application - any ideas?
>
> Hope that makes sense!
>
> Thanks,
>
> Alan.
>
>
>

[Alan]

"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
>
> Hi Alan,
>
> Another idea for you. First I'd better outline my understanding of
> your task:
>
> - Code in OldExcel starts new instance of Excel
> - Open a workbook in NewExcel that does a lot of stuff (presumably
> using RunAutoMacros ?)
> - Update the statusbar in OldExcel while doing stuff
>
> One way would be to put the name of a workbook that's open in
> OldExcel into NewExcel, then let code in the newly opened workbook
> use GetObject to grab a reference to its parent - OldExcel.
>
> ''''''''''''''''''''''
> 'code in "C:\My Documents\Excel\CrossRefXL.xls"
> 'that will be opened in a new Excel
>
> Option Explicit
> Dim xlOld As Excel.Application
>
> Sub auto_open()
> Dim bGotXL As Boolean
> Dim s As String
>
> s = Application.StatusBar
> Application.StatusBar = False
>
> If s <> Application.StatusBar Then
> On Error Resume Next
> Set xlOld = GetObject(s).Parent
> bGotXL = Not xlOld Is Nothing
> End If
>
> myProc bGotXL
>
> Set xlOld = Nothing ' if not needed again
>
> End Sub
>
> Sub myProc(bStatus As Boolean)
> Dim i As Long
> Dim s As String
> ' in real life only update status every [say] 1% of the loop
>
> s = "doing stuff "
> For i = 1 To 10000
> If bStatus Then xlOld.StatusBar = s & i
> Next
> If bStatus Then xlOld.StatusBar = False
> End Sub
>
> Sub auto_close()
> Set xlOld = Nothing
> End Sub
>
> ''''''''''''''''''''''''''''
>
> ''code in Old Excel
> Sub test()
> ' might want wb & xlNew as Public if needed in future
> Dim wb As Workbook
> Dim xlNew As Excel.Application
> Dim sName
> Application.StatusBar = False
> sName = "C:\My Documents\Excel\CrossRefXL.xls"
>
> Set xlNew = New Excel.Application
> xlNew.StatusBar = ThisWorkbook.Name
>
> On Error Resume Next
> Set wb = xlNew.Workbooks.Open(sName)
> If wb Is Nothing Then
> If MsgBox("Error loading " & sName & vbCr & _
> "Quit hidden Excel Instance", vbYesNo) = vbYes Then
> xl.Quit
> End If
> Else
> wb.RunAutoMacros xlAutoOpen
> xlNew.WindowState = xlNormal
> xlNew.Visible = True
> Set wb = Nothing
> End If
>
> Set xlNew = Nothing
> End Sub
> '''''''''''''''''''''''''''''''''
>
> Small point - if there's any possibility that the workbook, whose
> name has been passed to the statusbar in NewExcel, is open in
> multiple instances then open a new workbook in OldExcel and pass
> that name (eg "Book5"). Beforehand, make the new workbook hidden,
> close it later when done.
>
> Big point - you say your "stuff" takes a few minutes. DON'T update
> in every loop, every 1% or a few times per second is enough.
> So not the way shown in my example.
>
> Regards,
> Peter
>

Hi Peter,

That is a great idea - I will definately run with this one to see
where it takes me.

In terms of your 'Big Point' - the counter actually only updates at
the end of each specific task (about 12 in total) so that should not
be a problem (I am fine if it only every shows say,
8%, 16%, ..., 96%, 100%). The point is that the user knows that
something is happening and it hasn't just crashed.

One question:

If I use GetObject to create a reference in NewExcel to OldExcel (and
then I can presumably directly control any object in OldExcel from
within NewExcel including OldExcel.StatusBar) I will have a reference
in both directions (OldExcel will contain NewExcel and NewExcel will
contain an Object that is OldExcel).

In general is that an issue?

Conceptually (I am weak on this), I tend to think of object 'trees'
with each object being a branch off of its parent. However, in this
case we have a circular loop of 'containers'. Is that likely to store
up potential issues?

Thanks,

Alan.

[Peter T]

Hi Alan,

> That is a great idea - I will definately run with this one to see
> where it takes me.

Glad you like it.

Re my "Big point", I meant to add that updating progress 000's of times
might take longer than the actual routine on its own - though I see not an
issue for you.

> One question:
>
> If I use GetObject to create a reference in NewExcel to OldExcel (and
> then I can presumably directly control any object in OldExcel from
> within NewExcel including OldExcel.StatusBar) I will have a reference
> in both directions (OldExcel will contain NewExcel and NewExcel will
> contain an Object that is OldExcel).

Exactly, cross referrenced instances of Excel, each can manipulate the
other.

> In general is that an issue?

Possibly, let's take the least problematic ref first - "xlOld" in NewExcel.

I don't know if you want this as Public for later use, or as a one off at
procedure level while doing your stuff. In either case eventually it should
be set to Nothing. In the example I declared it Public, not knowing how you
want to use it as a precaution I set it to nothing in the Auto_close. But do
that where ever is most appropriate.

"xlNew" in ExcelOld is the one to be concerned with if has been used to
create the new instance. Eventually it should be set to nothing but NOT
until after NewExcel is closed, either programatically or manually. In the
worst case, if NewExcel is not visible, you won't be able to close it
without using the task manager, or Ctrl Alt Del and looking for "Excel" and
End task.

If you are opening your wb in a new instance to do a specific task (visible
?) after which you no longer need the new instance, do this:

wb.close false ' or true ?
set wb = nothing
xlNew.quit
set xlNew = nothing

If wb and/or NewExcel could have been closed by other means, run the above
under "On Error resume next".

Re-reading my Test proc I see that I set xlNew to nothing with NewExcel
still open. Not a major problem as I also made NewExcel visible. However
running this several times might lead to a memory leak. Having said that I
didn't notice any problems testing a few times.

> Conceptually (I am weak on this), I tend to think of object 'trees'
> with each object being a branch off of its parent. However, in this
> case we have a circular loop of 'containers'. Is that likely to store
> up potential issues?

I don't see these cross ref's as a "circular loop of 'containers'", so
providing the above precautions are followed I don't think there should be
any problems. But if anyone wants to step in and say otherwise ... I'll be
interested myself.

Regards,
Peter T