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
- The event handler works fine. Actually, if I put
ExpireSolutionthere, 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 solutionexception (which is definitely reasonable)
- As I read I should use
ScheduleSolutionto handle such cases, and issue the
ExpireSolutionfrom 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 (
- 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!
ScheduleSolutionExcel.gh (4.3 KB)