
VSTO Excel 2016 vb.net template document self-reference?
This has probably been answered, but there are so many questions that I would have to go through thousands of posts to find it. If someone can answer this, I would appreciate it. I don't know if the problem is because I am debugging through Visual Studio or something else, but I cannot seem to get a valid object instance for the active workbook.
There are many examples of how to open excel and create a workbook, which makes it easy to define objects for the application and the workbook. But when the code is part of a template project, Excel is already started. How do I create an object for the active workbook when Excel is already running?
My current code snippet is:
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Worksheet
Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")
If ExcelInstances.Count() = 0 Then
Exit Sub
End If
Dim ExcelInstance As Excel.Application = TryCast(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
If ExcelInstance Is Nothing Then Exit Sub
wb = ExcelInstance.ActiveWorkbook
For Each wsheet As Worksheet In wb.Worksheets
Dim wsName As String = wsheet.Name
If wsName = "Invoice" Then ws = wsheet
Next
It seems that it doesn't like how I have defined "wb" because it crashes at ActiveWorkbook . I have tried looping through the workbooks in my ExcelInstance class and I have also tried getting it by its name, which is problematic when the project is a template file and the workbook hasn't been saved yet.
I considered that part of the issue could be that I put my code into a class file rather than inside the workbook. I tried it the other way and it still does not work when I try to debug through VS. It seems to create the ExcelInstance without problem, it is the retrieving of the active workbook that has problems.
I have all of the usual office vb.net namespace imports included. Any suggestions? Thanks.
Microsoft 365 and Office | Development | Other
