Microsoft.Office.Interop Word/Excel Manipulation

16 Years Ago Fungus1487 0 970 Views

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.

vb.net

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

S.P.G 0 Newbie Poster

16 Years Ago

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 Sub

2. 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 Sub

3. 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 :)

BarbaMariolino 0 Newbie Poster

15 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.