Excel Sheet Selection Event will not Fire Off!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

junfan

Thread Starter
Joined
Jun 19, 2015
Messages
18
I 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 am writing some functionality that utilizes .NET's remoting features to make Microsofts's Excel talk to another application. The other application is a CAD program called NX.

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)

Code:
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), "http://localhost:4567/NXOpenSession"), Session)
Public ufs As UFSession = DirectCast(Activator.GetObject(GetType(UFSession), "http://localhost:4567/UFSession"), 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), "http://localhost:4567/NXOpenSession"), Session)
  Dim ufs As UFSession = DirectCast(Activator.GetObject(GetType(UFSession), "http://localhost:4567/UFSession"), 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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top