December 2006

Get Excel Instance without IDTextensibility2 in .Net

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:

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

Old dates, New VBA

Here's something i did not know about VBA!

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1166424720/0#8

Cool, now i just have to get a job at the Natural History Museum.

File Level Protection demo

Last in the series of videos about workbook protection. My new wide screen seams to have effected the video, I could not get it into a editor, and it looks a bit strange in the media player too. Having said that if you need to know how to protect office or Excel files, this will show you a couple of ways.

Happy Birthday MIE Blog!

This blog is one year old!, I've made about 90 post in that time, not very impressive but hey you miss 100% of the shots you don't play!

I hope that in the next year this blog will contain more content about Excel and .Net, as well has lots more videos and VBA stuff!

hbone.jpg

Thanks for reading everyone!