Worksheet change event will not fire off!

[junfanbl]

am having a very frustrating issue. I have been trying to get this code to work for over two weeks and I have had only partial success.

I have been trying to utilize NX's remoting features so I can get Excel to talk to NX and vice versa.

I am writing it in Visual Studio Professional Edition 2012 and I plan on compiling the code as a Console Application,

I have been able to successfully link Excel to NX remotely which is a big first step for me.

However now, I am trying to use the Workbook_sheetselectionchange event as a way to activate certain events based on user input.

The problem is that the sheet selection event will not fire off!

The subroutine ProcessNXData fires off just as it should and collects the necassary data from NX and populates the Excel spreadsheet. After that, I do some clicking and nothing...

I have no idea what is wrong. Any help would be greatly appreciated. Thank you!

(64 bit is Target machine, Microsoft Office 2010)

Option Strict Off
Option Infer Off

Imports System.IO
Imports System.Runtime.Remoting
Imports System.Runtime.Remoting.Channels
Imports System.Text
Imports System.Diagnostics
Imports System.Collections
Imports System.Collections.Generic
Imports NXOpen
Imports NXOpen.Assemblies
Imports NXOpen.Utilities
Imports NXOpen.UF

' add project COM reference:  Microsoft Excel xx.y Object Library

Imports Microsoft.Office.Interop ' import Excel Interop Namespace
Imports System.Runtime.InteropServices.Marshal
Imports Microsoft.Office.Interop.Excel
Imports System.Windows.Forms


  Module remoting_client_test

Public theSession As Session = DirectCast(Activator.GetObject(GetType(Session), "[URL unfurl="true"]http://localhost:4567/NXOpenSession"),[/URL] Session)
Public ufs As UFSession = DirectCast(Activator.GetObject(GetType(UFSession), "[URL unfurl="true"]http://localhost:4567/UFSession"),[/URL] UFSession)

Sub Main()

  Dim myForm As New Form1
  Dim Target As Range

  'need to initialize value of excel object variables

  myForm.OpenWorkBook("C:\Users\User\Documents\TestFile.xlsm")

  myForm.ProcessNXData()

End Sub

Public Sub DoLog(s As [String])
  Session.GetSession().LogFile.WriteLine(s)
  Console.WriteLine(s)
End Sub

Sub Echo(ByVal output As String)

  theSession.ListingWindow.Open()
  theSession.ListingWindow.WriteLine(output)
  theSession.LogFile.WriteLine(output)

End Sub

Public Function GetUnloadOption(ByVal dummy As String) As Integer
  Return Session.LibraryUnloadOption.Immediately
End Function

End Module

Public Class Form1

Private WithEvents excel As Excel.Application
Private WithEvents workbook As Excel.Workbook
Private WithEvents worksheet As Excel.Worksheet

'some variables required for excel
Public row As Long = 2

Public Sub OpenWorkBook(path As String)
  If excel Is Nothing Then
  excel = New Excel.Application
  excel.Visible = True ' user is responsible for closing Excel
  excel.UserControl = True
  End If
  If workbook IsNot Nothing Then
  FreeCOM(workbook)
  End If
  Dim workbooks As Excel.Workbooks = excel.Workbooks
  workbook = workbooks.Open(path)
  FreeCOM(workbooks)

  If worksheet IsNot Nothing Then
  FreeCOM(worksheet)
  End If
  Dim Worksheets As Excel.Sheets = workbook.Worksheets

  worksheet = CType(Worksheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet) ' 1 based indexing

End Sub

Public Shared Sub FreeCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
  Try
  If COMObj IsNot Nothing Then
  System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
  End If
  Finally
  COMObj = Nothing
  If GCCollect Then
  GC.Collect()
  GC.WaitForPendingFinalizers()
  End If
  End Try
End Sub


Public Sub ProcessNXData()
  Dim displayPart As Part = theSession.Parts.Display
  Dim workPart As Part = theSession.Parts.Work

  Dim markId1 As NXOpen.Session.UndoMarkId = theSession.SetUndoMark( _
  NXOpen.Session.MarkVisibility.Visible, "Reorder Components")

  Dim theOrders As Assemblies.ComponentOrder() = Nothing
  displayPart.ComponentAssembly.GetComponentOrders(theOrders)

  Echo(displayPart.Leaf & " has " & theOrders.Length & " ComponentOrder objects")

  For Each anOrder As Assemblies.ComponentOrder In theOrders

  anOrder.Activate()
  theSession.UpdateManager.DoUpdate(markId1)

  Echo(anOrder.Name & ":")
  WalkAssembyTree(displayPart.ComponentAssembly.RootComponent, anOrder, "")

  ufs.Ui.DisplayMessage(anOrder.Name, 1)

  theSession.UndoToMark(markId1, "")
  Next

  theSession.DeleteUndoMarksUpToMark(markId1, "", False)

End Sub

Sub WalkAssembyTree(ByVal theComponent As NXOpen.Assemblies.Component,
  ByVal theOrder As NXOpen.Assemblies.ComponentOrder,
  ByVal indent As String)

  Dim kids As Assemblies.Component() = theComponent.GetChildren()
  If kids.Length = 0 Then Return ' Not an assembly or sub-assembly

  indent = indent + "  "

  ' This does not always work - see PR 7421390
  ' Testing in the Toycar assembly only Chronological returns any Children

  kids = theOrder.AskChildrenOrder(theComponent)


  For ii As Integer = 0 To kids.Length - 1
  Echo(indent & kids(ii).DisplayName)



  '========================================================

  If kids(ii).HasUserAttribute("DB_PART_NAME", NXObject.AttributeType.String, -1) = True Then

  excel.Cells(row, 2) = (indent & kids(ii).GetStringAttribute("DB_PART_NAME"))

  Else

  'do nothing

  End If

  excel.Cells(row, 3).Value = (kids(ii).ToString())


  ' Process parts here

  Dim thisPart As Part = kids(ii).Prototype()

  If theOrder.OrderType = Order.Type.ChronologicalComponent Then

  processThisPart(thisPart, indent)

  row = row + 1

  Else

  Continue For

  End If

  '========================================================


  WalkAssembyTree(kids(ii), theOrder, indent)


  Next


End Sub

Public Sub processThisPart(ByVal p As Part, ByVal ind As String)

  Dim theBodies() As Body = p.Bodies.ToArray()
  Echo(ind & "Bodies in part: " & theBodies.ToString())

  For Each thisBody As Body In theBodies

  'Echo(ind & thisBody.ToString())

  '======================== Other desired processing here


  excel.Cells(row, 3).Value = (ind & thisBody.ToString())


  Next



End Sub


Sub Echo(ByVal output As String)
  theSession.ListingWindow.Open()
  theSession.ListingWindow.WriteLine(output)
  theSession.LogFile.WriteLine(output)
End Sub

Public Function GetUnloadOption(ByVal arg As String) As Integer
  Return Session.LibraryUnloadOption.Immediately
End Function



Private Sub worksheet_SelectionChange(Target As Excel.Range) Handles worksheet.SelectionChange

  Dim theSession As Session = DirectCast(Activator.GetObject(GetType(Session), "[URL unfurl="true"]http://localhost:4567/NXOpenSession"),[/URL] Session)
  Dim ufs As UFSession = DirectCast(Activator.GetObject(GetType(UFSession), "[URL unfurl="true"]http://localhost:4567/UFSession"),[/URL] UFSession)

  Dim workPart As Part = theSession.Parts.Work
  Dim dp As Part = theSession.Parts.Display

  Const undoMarkName As String = "NXJ journal"
  Dim markId1 As Session.UndoMarkId
  markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, undoMarkName)


  '=======================================================================
  'Remoting Worksheet: Cycles through component objects by name, and then when finding it, highlights it.
  '=======================================================================

  'Dim theCompName As String
  Dim theCompTag As NXOpen.Tag = NXOpen.Tag.Null
  Dim RemoteValue As Range

  Try
  Debug.Print("worksheet selection change" & Target.Address) ' for testing only, will do nothing in an executable
  RemoteValue = Target.Value  ' you can test if target is a valid value here or in remotevalue later


  Left(RemoteValue, Len(RemoteValue))

  ufs.Obj.CycleByNameAndType(dp.Tag, RemoteValue, _
  UFConstants.UF_component_type, True, theCompTag)

  If theCompTag.Equals(NXOpen.Tag.Null) Then
  Echo("No component found with that name.  Exiting...")
  Return
  End If

  Catch e As NXException
  Echo("NX Exception is: {0} " + e.Message)
  Catch e As Exception
  Echo("Exception is: {0} " & e.Message)

  Echo("DONE!" & vbLf)
  Finally
  ReleaseComObject(Target)
  End Try

End Sub

Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs)  Handles Me.FormClosed

If worksheet IsNot Nothing Then
  FreeCOM(worksheet)
End If
If workbook IsNot Nothing Then
  FreeCOM(worksheet)
End If
If excel IsNot Nothing Then
  FreeCOM(excel, True)
End If
End Sub


End Class

 

[IRstuff]

Wouldn't it make more sense to post this in the NX forum?
forum561

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[junfanbl]

Well I thought about that but it seems to me that NX isn't the problem (at least from what I can tell). I thought the problem was with the worksheet change event. Should I just post it in there?

 

[IRstuff]

Well, then, shouldn't you post in the spreadsheet forum, or the VBA forum?

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[SkipVought]

Regarding the worksheet change event, did you put a break in the first executable statement in this event to determine if the event is actually firing?

Is your code running in Excel or some other application? If so what application?

Did you search your ALL code for any EnableEvents code?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[junfanbl]

So I went ahead and threw in a msgbox inside my worksheet selection event subroutine to check and see if it will launch. It didn't. I also used the break point on the start of the subroutine itself, and it didn't make a difference. However, after inserting excel.EnableEvents = True inside the worksheet subroutine it worked! Thanks so much for suggesting that.

 

[SkipVought]

Well somewhere in your code, you have excel.EnableEvents = FALSE. You may have other issues as well.

You need to be very careful about turning off & on event processing. You need to have an intimate and complete knowledge of what your code is doing, and event processing can be extremely tricky.

Also, using reserve words as object variables, like worksheet or workbook for instance, is an extremely poor practice.

BTW, if you want good advise regarding programming related VBA issues from programming professionals, visit Eng-Tips's sister site,

http://www.Tek-Tips.com

and the VBA Visual Basic for Applications (Microsoft) forum for instance.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[junfanbl]

Hey SkipVought, thanks for your help and your advice, I appreciate it. I do have another question; it seems to me that sometimes the event fires off and sometimes it doesn't. I am having trouble nailing down any specific reasons that it isn't consistent. I tried clicking a cell while the subroutine processNXData is running and that results in an error every time. It stops running the code. That isn't a big deal, as the solution is simple; don't click anywhere until the code finishes running.

However, After the processNXData subroutine runs and I click in a cell, nothing happens. It seems that the code is finished executing and even the Worksheet selection change event will not respond. Other times it works when I click after the other subroutines finish. Could it be that there is a short window of time for me to activate the event? I thought that as long as the Excel object is still running the worksheet change event would respond?

Like you said, event processing can be extremely tricky.

 

[SkipVought]

As I stated earlier, it seems that your code is disabling events in one or more places. You must find ALL EnableEvents statements in your code and fully understand what they are inteded to do and what they actually are doing. Did you write the code? If not who did and can you consult with them? It is obvious that the intention of the coder is not what appears to be happening.

I assume that your code is running in the workbook in question.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[junfanbl]

Hey SkipVought, I got it to work consistently finally! Thanks for your help.

 

[SkipVought]

Could you please share what you did to get it to work consistently. The process would be informative to other members like me.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}