Dear All,
EDIT: Added a non-Excel dependent version to the second post…
I am trying to create a (VB) component that issues a solution recalculation on a change of a referenced Excel workbook (cells) using Excel interop services. I have attached a model with a minimum set of components and lines of code to demonstrate the issue I am struggling with.
Imports Microsoft.Office.Interop
Private Sub RunScript(ByVal P As System.Object, ByRef B As Object)
RhinoApp.WriteLine("--- Script staring...")
app = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
wb = app.Workbooks.Open(P)
B = "Now"
End Sub
Private app As Excel.Application
WithEvents Private wb As Excel.Workbook
Private counter As int32
Private Sub SheetChange(ByVal Sh As Object, ByVal Target As Object) Handles wb.SheetChange
counter = counter + 1
RhinoApp.WriteLine("Workbook changed " & counter & " times, issuing ExpireSolution in 5 secs...")
Component.OnPingDocument.ScheduleSolution(5000, AddressOf Schedule_CB)
End Sub
Private Sub Schedule_CB()
RhinoApp.WriteLine("Recalculating, SolutionState: " & Component.OnPingDocument.SolutionState)
Component.ExpireSolution(True)
End Sub
So far:
- The event handler works fine. Actually, if I put
ExpireSolution
there, the model performs pretty well and does its job. - However, of course, in that case if the next change event occurs earlier than the solution finishes, it will throw the
object expired during a solution
exception (which is definitely reasonable) - As I read I should use
ScheduleSolution
to handle such cases, and issue theExpireSolution
from its callback. I tried to implement it, but ended up having an endless loop of recalculations at the first time the event occurs. - Callback reached fine, timing is ok. Examining SolutionState does not help, it switches from 2 (
PostProcess
) to 0 (PreProcess
) but never reaches 1 (Process
). - It seems to me it is not an issue of instantly rescheduling a solution (as there is no delay), but the callback function getting called over and over again.
To reproduce using the attached model:
- Probably need to reference the ‘Microsoft.Office.Interop.Excel.dll’ library on your system. Look for folders in the Global Assembly Cache (GAC) tree of Windows. Different Office versions (i.e. using 14 instead of 15) should work fine.
- Create and save an empty Excel workbook to a location of your choice (of course you can use an existing one)
- Modify the ‘Path/File’ container in the model accordingly
- Make a change in any sheets of the Excel workbook
- If all goes right (haha), you will end up with a (warning!) crashed Rhino.
(Technical details: Rhino 6.16.19190.7001, 07/09/2019, Grasshopper 1.0.0007, Windows 10, Office 2016)
Any help is truly appreciated, thank you!
Marton
ScheduleSolutionExcel.gh (4.3 KB)