Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
"K Dales" wrote in message
... 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").v alue) It just seems a bit clunky and that there should be an easy way to reference a variable, but perhaps not. Thanks, Alan. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
"mark" wrote in message
... 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").Va lue 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
"Peter T" <peter_t@discussions wrote in message
... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a variable from another instance of excel?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access to Variable | Excel Discussion (Misc queries) | |||
Import into Access from Excel, passing a variable for the field... | Excel Worksheet Functions | |||
Transpose a variable length list into Excel / Access Table | New Users to Excel | |||
Trying to access different files depening on a variable | Excel Discussion (Misc queries) | |||
I Need VBA Assistance regarding passing a variable from Access | Excel Programming |