VB.Net为运行EXCEL实例添加工作簿

我正在开发AutoCAD插件。试图在它首先查找已经运行的EXCEL实例的地方实现函数，所以我只是将新工作簿添加到现有实例中，而不是总是创建新流程。

我的代码在试图查找正在运行的进程时失败。由于某种原因，它总是检测到正在运行的EXCEL进程，我在任务管理器中检查了它，它不在那里，所以这就是为什么我的插件在Marchal.GetActiveObject方法中崩溃的原因，因为它试图获取正在运行的进程。。。

到目前为止我的函数代码：

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

'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

这是以BASIC方式进行的。

要编译上述文件，请将以下行粘贴到批处理文件中，并将上述文件命名为excel.vb。将bat文件放在与excel.vb相同的文件夹中，然后双击批处理文件。

REM Excel.bat
REM This file compiles Excel.vb to Excel.exe
REM To use 
REM    Excel
Rem Example 
Rem 
Rem     Excel 
"C:WindowsMicrosoft.NETFrameworkv4.0.30319vbc.exe" /target:winexe /out:"%~dp0Excel.exe" "%~dp0Excel.vb"
pause

不需要Visual Studio

只有在工作簿尚未打开的情况下，才会添加工作簿。尝试以下操作(注释遍布整个代码(：

创建新项目Windows Forms App (.NET Framework)

添加参考：

VS 2022：

在VS菜单中，单击项目
选择"添加引用…">
单击COM
检查Microsoft Excel xx.x对象库(例如：Microsoft Excel 16.0对象库(
单击"确定">

添加以下导入

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

以下是一些可能有用的其他方法：

注意：如果您的应用程序有打开的Excel资源，则即使单击Excel窗口的X(右上角(，Excel也可能仍在运行。如果打开任务管理器，则可以在"；背景处理"；。要退出Excel，请参阅下面的代码。

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

资源

Microsoft.Office.Interop.Excel
如何在Windows的命令行中列出所有进程
C#关闭其他应用程序打开的Excel实例

相关内容

最新更新

热门标签：