VSTO Excel 2016 vb.net template document self-reference?

David Eisenbeisz 1 Reputation point
2021-05-11T18:49:56.793+00:00

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
Developer technologies | Visual Studio | Debugging
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.