VB.Net Add Workbook to Running EXCEL Instance-CodePudding

I am working on AutoCAD plugin. Trying to implement function where it first looks for already running EXCEL instances so I just Add new workbook to existing instance instead of always creating new process.

My code fails at the point where it tries to find running process. For some reason it always detects running EXCEL process, I checked it at task manager, it is not there so that's why my plugin crashes at Marchal.GetActiveObject method because it's trying to get that running process...

My functions' code so far:

Private Function GetExcelWorksheet() As Excel.Worksheet

    Dim excel As Excel.Application
    Dim activeWorksheet As Excel.Worksheet = Nothing
    Dim wb As Excel.Workbook = Nothing
    Dim ws As Excel.Worksheet = Nothing


    Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")
    If ExcelInstances.Count() = 0 Then
        Exit Function
    End If

    excel = TryCast(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
    If excel Is Nothing Then Exit Function

    excel.Visible = True
    wb = excel.Workbooks.Add
    ws = wb.Worksheets(1)
    Return ws

End Function

CodePudding user response：

'Excel.vb
Imports System
Imports System.Runtime.InteropServices

Public Module AnythingYouWantToCallIt


    Sub Main
        Dim Excel as Object
        Dim wb as Object
        Dim WS as Object
        On Error Resume Next
        Excel = GetObject(, "Excel.Application")
        If Err.number = 429 then
            Msgbox("No Excel running")
            err.clear
            Excel = CreateObject("Excel.Application")
            If err.number = 429 then
                MsgBox("Excel Not Installed")
                err.clear
            Else
                MsgBox("New Excel has started")
            End If
        Else
            Msgbox("Existing Excel connected to")               
        End If
        WB = Excel.Workbooks.Add
        WS = wb.Worksheets(1)
        MsgBox(ws.name)
        'If in a function
        'Main = WS
    End Sub 
End Module

This does the BASIC way.

To compile above file paste following lines into a batch file and name above file excel.vb. Put the bat file in same folder as excel.vb and double click batch file.

REM Excel.bat
REM This file compiles Excel.vb to Excel.exe
REM To use 
REM    Excel
Rem Example 
Rem 
Rem     Excel 
"C:\Windows\Microsoft.NET\Framework\v4.0.30319\vbc.exe" /target:winexe /out:"%~dp0\Excel.exe" "%~dp0\Excel.vb"
pause

Does not require Visual Studio

CodePudding user response：

One would only add a Workbook, if a Workbook wasn't already open. Try the following (comments are throughout the code):

Create a new project Windows Forms App (.NET Framework)

Add Reference:

VS 2022:

In VS menu, click Project
Select Add Reference...
Click COM
Check Microsoft Excel xx.x Object Library (ex: Microsoft Excel 16.0 Object Library)
Click OK

Add the following Imports

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

GetExcelWorksheet:

Private Function GetExcelWorksheet() As Excel.Worksheet

    Dim excel As Excel.Application
    Dim activeWorksheet As Excel.Worksheet = Nothing
    Dim wb As Excel.Workbook = Nothing
    Dim ws As Excel.Worksheet = Nothing

    'get existing Excel processes
    Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")

    For Each instance In ExcelInstances
        Debug.WriteLine($"ExcelInstances: ProcessName: {instance.ProcessName} Id: {instance.Id}")
    Next

    If ExcelInstances.Count() = 0 Then
        Debug.WriteLine("No Excel instances found")
        Exit Function
    End If

    Debug.WriteLine("Getting Active Excel instance...")
    excel = TryCast(Marshal.GetActiveObject("Excel.Application"), Excel.Application)

    If excel Is Nothing Then Exit Function

    Debug.WriteLine("Setting Excel visible")

    'ensure Excel is visible
    excel.Visible = True

    'get active workbook
    wb = excel.ActiveWorkbook

    If wb Is Nothing Then
        Debug.WriteLine("Adding Workbook...")
        wb = excel.Workbooks.Add()
    End If

    Debug.WriteLine($"wb.Sheets.Count: {wb.Sheets.Count}")

    'set worksheet
    ws = DirectCast(wb.Sheets(1), Excel.Worksheet)

    'activate worksheet
    ws.Activate()

    'add new Worksheet
    ws = DirectCast(wb.Sheets.Add(After:=wb.Sheets(1)), Excel.Worksheet)
    ws.Name = "My Worksheet Name" 'set worksheet name

    Debug.WriteLine($"wb.Sheets.Count: {wb.Sheets.Count}")

    'set value - for A1 also known as 1,1
    ws.Cells(1, 1) = $"{DateTime.Now.ToString("HH:mm:ss.fff")}"

    Return ws
End Function

Here are some additional methods that may be useful:

Note: If your application has open resources to Excel, Excel may still be running even after clicking the X(in the upper-right corner) of the Excel window. If one opens Task Manager, Microsoft Excel can be seen under "Background processes". To quit Excel, see the code below.

Public Function IsExcelRunning() As Boolean
    'get existing Excel processes
    Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")

    For Each instance In ExcelInstances
        Debug.WriteLine($"ExcelInstances: ProcessName: {instance.ProcessName} Id: {instance.Id}")
    Next

    If ExcelInstances.Count() = 0 Then
        Debug.WriteLine($"IsExcelRunning: False")
        Return False
    End If

    Debug.WriteLine($"IsExcelRunning: True")
    Return True
End Function

Public Function IsExcelVisible() As Boolean
    Dim excel As Excel.Application

    'get existing Excel processes
    Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")

    For Each instance In ExcelInstances
        Debug.WriteLine($"ExcelInstances: ProcessName: {instance.ProcessName} Id: {instance.Id}")
    Next

    If ExcelInstances.Count() = 0 Then
        Debug.WriteLine("No Excel instances found")
        Exit Function
    End If

    Debug.WriteLine("Getting Active Excel instance...")
    excel = TryCast(Marshal.GetActiveObject("Excel.Application"), Excel.Application)

    If excel Is Nothing Then Return False

    Debug.WriteLine($"IsExcelVisible: {excel.Visible}")

    Return excel.Visible
End Function

Public Sub QuitExcel()
    Dim excel As Excel.Application
    Dim wb As Excel.Workbook = Nothing
    Dim ws As Excel.Worksheet = Nothing

    'get existing Excel processes
    Dim ExcelInstances As Process() = Process.GetProcessesByName("EXCEL")

    For Each instance In ExcelInstances
        Debug.WriteLine($"ExcelInstances: ProcessName: {instance.ProcessName} Id: {instance.Id}")
    Next

    If ExcelInstances.Count() = 0 Then
        Debug.WriteLine("No Excel instances found")
        Exit Sub
    End If

    Debug.WriteLine("Getting Active Excel instance...")
    excel = TryCast(Marshal.GetActiveObject("Excel.Application"), Excel.Application)

    If excel Is Nothing Then Exit Sub

    wb = excel.ActiveWorkbook

    If wb IsNot Nothing Then
        Debug.WriteLine($"Closing Excel Workbook...")
        wb.Close(False)

        'release resources
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb)
    End If

    Debug.WriteLine($"Quiting Excel.")

    'quit Excel
    excel.Quit()

    'release resources
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel)

    GC.Collect() 'force garbage collection
End Sub

Resources