Below is code i wrote as a wrapper for the microsoft office interops. It provides ways to establish the instance of the application and cleanup any remaining office processes after use.
Note the class I use is alot bigger but i have added the basics as a starting point.
This class is intended to be built upon and simply makes sure you do not leave several office instances open on a machine. which commonly can happen when doing this for the first time.
Option Strict On
Imports Microsoft.VisualBasic
''' <summary>Wrapper class for the microsoft.office.interop classes</summary>
''' <remarks>
'''
''' Requires the following registered COM [interop] objects
'''
''' Microsoft Office Object Library
''' Microsoft Word Object Library
''' Microsoft Excel Object Library
'''
''' </remarks>
Public NotInheritable Class MsOffice
#Region "Private Methods and Functions"
''' <summary>Function to calculate the total sum of process ID's of a process name.</summary>
''' <param name="instanceName">The instance to count</param>
''' <remarks></remarks>
Private Shared Function GetTotalProcessID(ByVal instanceName As String) As Integer
For Each diaProc As System.Diagnostics.Process In System.Diagnostics.Process.GetProcessesByName(instanceName)
GetTotalProcessID += diaProc.Id
Next
End Function
#End Region
#Region "Classes"
''' <summary>Wrapper class for the microsoft.office.interop.word class.</summary>
''' <remarks></remarks>
Public Class Word
#Region "Variables"
''' <summary>The current winword application instance.</summary>
''' <remarks></remarks>
Private _worApp As Microsoft.Office.Interop.Word.Application = Nothing
''' <summary>The current winword document instance.</summary>
''' <remarks></remarks>
Private _worDoc As Microsoft.Office.Interop.Word.Document = Nothing
''' <summary>The current winword instances Process ID</summary>
''' <remarks></remarks>
Private _intID As Integer = 0
''' <summary>The worksheet unprotection password</summary>
''' <remarks></remarks>
Private _strPassword As String = Nothing
#End Region
#Region "Properties"
''' <summary>The current winword application instance.</summary>
''' <remarks></remarks>
Public Property Application() As Microsoft.Office.Interop.Word.Application
Get
Return Me._worApp
End Get
Set(ByVal value As Microsoft.Office.Interop.Word.Application)
Me._worApp = value
End Set
End Property
''' <summary>The current winword document instance.</summary>
''' <remarks></remarks>
Public Property Document() As Microsoft.Office.Interop.Word.Document
Get
Return Me._worDoc
End Get
Set(ByVal value As Microsoft.Office.Interop.Word.Document)
Me._worDoc = value
End Set
End Property
''' <summary>The current winword instances Process ID</summary>
''' <remarks></remarks>
Public ReadOnly Property ProcessID() As Integer
Get
Return Me._intID
End Get
End Property
''' <summary>The document unprotection password</summary>
''' <remarks></remarks>
Public Property Password() As String
Get
Return Me._strPassword
End Get
Set(ByVal value As String)
Me._strPassword = value
End Set
End Property
#End Region
#Region "Public Methods and Functions"
''' <summary>Constructs a new Word Class instance and opens a winword Application instance</summary>
''' <remarks></remarks>
Public Sub New()
Try
Me._intID = GetTotalProcessID("winword") ' Get Total before app exists
Me._worApp = New Microsoft.Office.Interop.Word.Application()
Me._worApp.DisplayAlerts = Microsoft.Office.Interop.Word.WdAlertLevel.wdAlertsNone
Me._worApp.ScreenUpdating = False
Me._intID = GetTotalProcessID("winword") - Me._intID ' Get New Total and subtract to get out ID
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Sub
''' <summary>Function to open a file into the application</summary>
''' <param name="file">The path to the file</param>
''' <remarks></remarks>
Public Function Open(ByVal file As String, Optional ByVal password As String = Nothing) As Boolean
Try
Me._strPassword = password
Dim tmpSec As Microsoft.Office.Core.MsoAutomationSecurity = Me._worApp.AutomationSecurity
Me._worApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable ' Allow macros
Me._worDoc = Me._worApp.Documents.Open(DirectCast(file, Object))
Me._worApp.AutomationSecurity = tmpSec ' Reset security
If Not IsNothing(Me._strPassword) Then Me._worDoc.Unprotect(DirectCast(Me._strPassword, Object))
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Function
''' <summary>Function to save the current file in the winword application.</summary>
''' <param name="file">The path to the file</param>
''' <remarks></remarks>
Public Function SaveAs(ByVal file As String) As Boolean
Try
Me._worDoc.SaveAs(DirectCast(file, Object))
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Function
''' <summary>Method to close the winword application</summary>
''' <remarks></remarks>
Public Sub Close()
Try
If Not IsNothing(Me._worDoc) Then Me._worDoc.Close()
If Not IsNothing(Me._worApp) Then
Me._worApp.Quit()
If Not IsNothing(Me._worDoc) Then System.Runtime.InteropServices.Marshal.ReleaseComObject(Me._worDoc)
System.Runtime.InteropServices.Marshal.ReleaseComObject(Me._worApp)
System.Diagnostics.Process.GetProcessById(Me._intID).Kill() ' Surefire way to make sure word object has been destroyed
End If
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Sub
#End Region
End Class
''' <summary>Wrapper class for the microsoft.office.interop.excel class.</summary>
''' <remarks></remarks>
Public Class Excel
#Region "Variables"
''' <summary>The current excel application instance.</summary>
''' <remarks></remarks>
Private _excApp As Microsoft.Office.Interop.Excel.Application = Nothing
''' <summary>The current excel workbook instance.</summary>
''' <remarks></remarks>
Private _excWor As Microsoft.Office.Interop.Excel.Workbook = Nothing
''' <summary>The current excel instances Process ID</summary>
''' <remarks></remarks>
Private _intID As Integer = 0
''' <summary>The worksheet unprotection password</summary>
''' <remarks></remarks>
Private _strPassword As String = Nothing
#End Region
#Region "Properties"
''' <summary>The current excel application instance.</summary>
''' <remarks></remarks>
Public Property Application() As Microsoft.Office.Interop.Excel.Application
Get
Return Me._excApp
End Get
Set(ByVal value As Microsoft.Office.Interop.Excel.Application)
Me._excApp = value
End Set
End Property
''' <summary>The current excel workbook instance.</summary>
''' <remarks></remarks>
Public Property Document() As Microsoft.Office.Interop.Excel.Workbook
Get
Return Me._excWor
End Get
Set(ByVal value As Microsoft.Office.Interop.Excel.Workbook)
Me._excWor = value
End Set
End Property
''' <summary>The current excel instances Process ID</summary>
''' <remarks></remarks>
Public ReadOnly Property ProcessID() As Integer
Get
Return Me._intID
End Get
End Property
''' <summary>Provides access to individual cells in a worksheet.</summary>
''' <param name="column">The column name</param>
''' <param name="row">The row number</param>
''' <param name="sheet">The worksheet</param>
''' <remarks></remarks>
Public Property Cell(ByVal column As String, ByVal row As Integer, ByVal sheet As Object) As Object
Get
Try
Dim excSheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(Me._excWor.Sheets.Item(sheet), Microsoft.Office.Interop.Excel.Worksheet)
If Not IsNothing(Me._strPassword) Then excSheet.Unprotect(Me._strPassword)
Dim excRange As Microsoft.Office.Interop.Excel.Range = DirectCast(excSheet.Range(column & row).Cells(1, 1), Microsoft.Office.Interop.Excel.Range)
Return excRange.Value
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
Return Nothing
End Get
Set(ByVal value As Object)
Try
Dim excSheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(Me._excWor.Sheets.Item(sheet), Microsoft.Office.Interop.Excel.Worksheet)
If Not IsNothing(Me._strPassword) Then excSheet.Unprotect(Me._strPassword)
Dim excRange As Microsoft.Office.Interop.Excel.Range = DirectCast(excSheet.Range(column & row).Cells(1, 1), Microsoft.Office.Interop.Excel.Range)
excRange.Value = value
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Set
End Property
''' <summary>Provides access</summary>
''' <param name="item"></param>
''' <param name="sheet"></param>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
Public Property Shape(ByVal item As Object, ByVal sheet As Object) As Object
Get
Dim excSheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(Me._excWor.Sheets.Item(sheet), Microsoft.Office.Interop.Excel.Worksheet)
If Not IsNothing(Me._strPassword) Then excSheet.Unprotect(Me._strPassword)
Dim excShape As Microsoft.Office.Interop.Excel.Shape = excSheet.Shapes.Item(item)
Return excShape.TextFrame.Characters.Text
End Get
Set(ByVal value As Object)
Dim excSheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(Me._excWor.Sheets.Item(sheet), Microsoft.Office.Interop.Excel.Worksheet)
If Not IsNothing(Me._strPassword) Then excSheet.Unprotect(Me._strPassword)
Dim excShape As Microsoft.Office.Interop.Excel.Shape = excSheet.Shapes.Item(item)
excShape.TextFrame.Characters(0, excShape.TextFrame.Characters.Count).Delete()
excShape.TextFrame.Characters.Text = value.ToString
End Set
End Property
''' <summary>The worksheet unprotection password</summary>
''' <remarks></remarks>
Public Property Password() As String
Get
Return Me._strPassword
End Get
Set(ByVal value As String)
Me._strPassword = value
End Set
End Property
#End Region
#Region "Public Methods and Functions"
''' <summary>Constructs a new Word Class instance and opens a winword Application instance</summary>
''' <remarks></remarks>
Public Sub New()
Try
Me._intID = GetTotalProcessID("excel") ' Get Total before app exists
Me._excApp = New Microsoft.Office.Interop.Excel.Application()
Me._excApp.DisplayAlerts = False
Me._excApp.ScreenUpdating = False
Me._excApp.EnableEvents = False
Me._intID = GetTotalProcessID("excel") - Me._intID ' Get New Total and subtract to get out ID
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Sub
''' <summary>Function to open a file into the application</summary>
''' <param name="file">The path to the file</param>
''' <remarks></remarks>
Public Function Open(ByVal file As String, Optional ByVal password As String = Nothing) As Boolean
Try
Me._strPassword = password
Dim tmpSec As Microsoft.Office.Core.MsoAutomationSecurity = Me._excApp.AutomationSecurity
Me._excApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable ' Allow macros
Me._excWor = Me._excApp.Workbooks.Open(file)
Me._excApp.AutomationSecurity = tmpSec ' Reset security
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Function
''' <summary>Function to save the current file in the excel application.</summary>
''' <param name="file">The path to the file</param>
''' <remarks></remarks>
Public Function SaveAs(ByVal file As String) As Boolean
Try
Me._excWor.SaveAs(DirectCast(file, Object))
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Function
''' <summary>Method to close the winword application</summary>
''' <remarks></remarks>
Public Sub Close()
Try
If Not IsNothing(Me._excWor) Then Me._excWor.Close()
If Not IsNothing(Me._excApp) Then
Me._excApp.Quit()
If Not IsNothing(Me._excWor) Then System.Runtime.InteropServices.Marshal.ReleaseComObject(Me._excWor)
System.Runtime.InteropServices.Marshal.ReleaseComObject(Me._excApp)
System.Diagnostics.Process.GetProcessById(Me._intID).Kill() ' Surefire way to make sure excel object has been destroyed
End If
Catch ex As Exception
Exceptions.HandleException(ex)
End Try
End Sub
#End Region
End Class
#End Region
End Class
' ########
' ######## DEMONSTRATION [WORD]
' ########
Dim clsWord As New MsOffice.Word
Dim intNo As Integer = Now.Minute & Now.Second & Now.Millisecond
Try
clsWord.Open("c:\yourname.doc")
clsWord.Document.Bookmarks.Item("yourName").Range.Text = Server.MachineName ' Change a bookmark in the document
clsWord.SaveAs("C:\yourname.doc")
Console.WriteLine("Word File saved to '" & "C:\yourname2.doc" & "'.")
Catch ex As Exception
Console.WriteLine("There was an error manipulating the word document.")
Finally
clsWord.Close()
End Tryd Try
' ########
' ######## DEMONSTRATION [EXCEL]
' ########
Dim clsExcel As New MsOffice.Excel
Try
clsExcel.Open("c:\spreadsheet.xls")
clsExcel.Cell("G", 12, 1) = "123"
clsExcel.Shape("Text 221", 1) = "test"
clsExcel.SaveAs("c:\spreadsheet2.xls")
Console.WriteLine("Excel File saved to '" & "C:\spreadsheet2.xls" & "'.")
Catch ex As Exception
Console.WriteLine("There was an error manipulating the Excel document.")
Finally
clsExcel.Close()
End Try
Very clever!!
When I first saw the method GetTotalProcessID I thought huh?
Then I saw the logic, very simple :)
The client has say two instances of Excel running, so sum the ids
The code starts another instance, so sum and subtract to get its id
:)
Then you can use that id to shut it down if really needed using System.Diagnostics
I have seen other code that uses GUIDs and Windows API calls but this was for both client side and server side.
A few things:
On a client pc this is perfect! - common use of Excel interop
If excel instances are being created on a server then it will probably not work due to multiple users, but server side stuff is rare with excel interop
I release in 3 stages:
1. Old VBA habit, set everything to Nothing like
Public Sub CloseExcel(ByVal app As Excel.Application, _
ByVal interact As Boolean)
Dim wbk As Excel.Workbook
Dim wrk As Excel.Worksheet
app.Interactive = Interact
app.DisplayAlerts = Interact
If Not app.Workbooks Is Nothing Then
For Each wbk In app.Workbooks
For Each wrk In wbk.Worksheets
wrk = Nothing
Next
wbk.Close(False)
wbk = Nothing
Next
End If
app.Quit()
End Sub2. Com release
In your code you should loop ReleaseComObject or use FinalReleaseComObject for the RCW like
''Releases COM objects
Imports System.Runtime.InteropServices
''Releases COM objects
Private Sub ReleaseComponent(ByVal component As Object)
Dim count As Integer = 0
Do
count = Marshal.ReleaseComObject(component)
Loop While count <> 0
End Sub3. The third bit is your clever idea
Work out the Process ID and then just kill it if it is still hanging around.
Final thing
I have done quite a few years with VBA mostly in excel and access.
The funny thing is if you make the excel app visible then you hardly ever have problems. The process only hangs about if you are debugging and stop code execution. But I needed a clean way to use excel without it being visible on the client side pc.
Thanks for that :)
Hi,
using Interop is not best way to work with Excel in .NET. You can try using GemBox .NET Excel component which is free for commercial use (limit is 150 rows).
http://www.gemboxsoftware.com/GBSpreadsheet.htm
Here is a list of reasons why GemBox.Spreadsheet is better then Excel Interop and Excel Automation
http://www.gemboxsoftware.com/Excel-Automation.htm
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.