Get Excel Instance without IDTextensibility2 in .Net

27 December 2006 by Ross McLean

Here is a little .Net app that shows how you can get hold of an excel instance so that you can use that Excel object from code - but with out using IDT. This is useful when writing classes rather than managed addins. I will hopefully make a post about how we can use .net classes from VBA in a little bit, but this is worth a post in it's own right.

GetInstance.JPG

here is the example app

Know, the most interesting method here is the bind to moniker one as that lets us get the application that a particular file is active in, and so give use the most certainty that we are targeting the Excel instance we wish to. I have stuck this into a function:

PLAIN TEXT
VB.NET:
  1. Imports System.Runtime.InteropServices
  2. Imports xl = excel.application
  3.  
  4. '''Class for Getting an Excel Intance
  5.  
  6. Public Class GetExcelInstance
  7.  
  8. Public Function GetTargetXLApplication(ByVal sPassedFilePath As String) As Excel.Application
  9. Dim sFilePath As String = sPassedFilePath
  10. Dim wkbBindToBook As Excel.Workbook
  11. Dim blXLBelongsToUs As Boolean ''FLAG FOR KILLING XL APP LATER
  12. Dim cFiles As System.IO.FileInfo
  13. Dim sWkbName As String
  14.  
  15. ''get filename from path
  16. cFiles = My.Computer.FileSystem.GetFileInfo(sFilePath)
  17. sWkbName = cFiles.Name
  18.  
  19. Try
  20.  
  21. ''Set our target wkb from the file path to our
  22. ''.net wkb ojbect dimed here
  23. wkbBindToBook = Marshal.BindToMoniker(sFilePath)
  24. ''set the appliction referance to the host excel sever this workbook is running in
  25. GetTargetXLApplication = wkbBindToBook.Application
  26.  
  27. ''the following code manipulates the excel clients window state.
  28. If GetTargetXLApplication.Visible = False Then
  29. ''we opened excel
  30. GetTargetXLApplication.Visible = True
  31. GetTargetXLApplication.Windows(sWkbName).Visible = True
  32. blXLBelongsToUs = False  ''FLAG FOR QUITING IF WE STARTED IT
  33. '     MsgBox("Moniker from no intance") ''for debug
  34. Else
  35. ''excel was opened but we loaded a hidden file into it, lets make it visable
  36. If GetTargetXLApplication.Windows(sWkbName).Visible = False Then
  37. GetTargetXLApplication.Windows(sWkbName).Visible = True
  38. '         MsgBox("Moniker from existing intance")  ''for debug
  39. End If
  40. '     MsgBox("Moniker from existing intance")      ''for debug
  41. End If
  42.  
  43. Catch ex As Exception
  44. MsgBox(ex.Message)
  45. GetTargetXLApplication = nothing 'sets function to null object if error happen
  46. End Try
  47. End Function

The VS project files are here

Any questions feel free to leave a comment, cheers Ross

Related posts:

  1. Using the immediate window from within the Excel desktop
  2. Selection to CSV
  3. Map Point from Excel

Tags: , ,
Categories: VB.Net • Permalink • No Comments »

Submit a Comment