Recently I had the pleasure to get involved with an MS Excel automation project. The project’s main goal was to read a number of Excel documents in a specific folder, validate their contents, and use the contents to generate a new Excel documents as an output.

Although I have been working with ASP.NET Excel automation before, I still feel there are a lot of things that I don’t know. So as I browsed through the list of URL given by Google’s Search Result, I found a number of interesting things that I would like to share with you.

Lesson Learned

Gary Read shares his experience on Automating Excel using .NET. I list a few Gary’s tips which are my favourites:

• Tip #1: Seriously Consider NOT Automating Excel from .NET
  In short, only automate Excel when no budget (apart from your salary :D) is allocated to the project.
• Tip #2: Use VB.NET instead of C#
  The best tip for me. If you are already starting to write the code in C#–like I did when I read this article, immediately switch to VB.NET. The switch to VB.NET makes it a hell lot easier to complete the project compared to C#.
• Tip #5: Wrap your Excel communication points
  Instead of cluttering your program with repeating lines of getting the cell and extracting the value, refactor the methods to make the program less hurting your eyes.
• Tip #7: Watch out interruptions and disconnects
  IMHO, to avoid this problem basically we just need to ensure that only one process/user is opening MS Excel. In other words, combining automation and manual excel is a recipe for disaster.

As my project is now completed, I would like share a number of simple codes that I found useful. Hopefully it will be useful for you too.

How to create Excel document

Imports Microsoft.Office.Interop.Excel
'.....
Dim appExcel As New ApplicationClass
Dim wbObject As Workbook = appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
Dim wsObject As Worksheet = wbObject.Worksheets.Item(1)
wsObject.Name = "Overall Summary" 'Set Worksheet Title
'....Modify the Worksheet content here
wbObject.SaveAs("C:\Output.xls") 'Save the file
wbObject.Close()
appExcel.Quit()
'.....

How to open existing Excel document

Imports Microsoft.Office.Interop.Excel
'.....
Dim appExcel As New ApplicationClass
Dim wbObject As Workbook = appExcel.Workbooks.Open("C:\Output.xls", False, False)
Dim wsObject As Worksheet = wbObject.Worksheets.Item(1)
'....Modify the Worksheet content here
wbObject.Save() 'Save the file
wbObject.Close()
appExcel.Quit()
'.....

How to manipulate Excel Worksheet

Imports Microsoft.Office.Interop.Excel
'.....
    Public Function AddWorksheet(ByVal strName As String, ByVal wbObject As Workbook) As Worksheet
        Dim wsResult As Worksheet
        wsResult = wbObject.Worksheets.Add() 'Add as the first Worksheet
        wsResult.Name = strName
        Return wsResult
    End Function
'....
    Public Sub RemoveWorksheet(ByVal strName As String, ByVal wbObject As Workbook)
        Dim wsResult As Worksheet
        wsResult = GetWorksheetByName(strName, wbObject)
        If Not wsResult Is Nothing Then
            wsResult.Delete()
        End If
    End Sub
'....
    Public Function GetWorksheetByName(ByVal strName As String, ByVal wbObject As Workbook) As Worksheet
        Dim wsResult As Worksheet
        If Not IsNothing(wbObject) And Not IsNothing(strName) Then
            For Each wsObject As Worksheet In wbObject.Worksheets
                If wsObject.Name.ToLower().Trim().Equals(strName.ToLower().Trim()) Then
                    wsResult = wsObject
                End If
            Next
        End If
        Return wsResult
    End Function
'....
    Public Sub MoveWorksheet(ByVal strWorksheetName As String, ByVal pos As SheetPosition, ByVal wbObject As Workbook)
        Dim wsResult As Worksheet = GetWorksheetByName(strWorksheetName, wbObject)
        If Not wsResult Is Nothing Then
            If pos = SheetPosition.First Then
                If wsResult.Index > 1 Then 'ensure the target is not the first worksheet
                    Dim tempws As Worksheet = wbObject.Worksheets(1) 'Object Index starts with 1
                    wsResult.Move(tempws)
                End If
            ElseIf pos = SheetPosition.Last Then
                If wsResult.Index < wbObject.Worksheets.Count Then
                    Dim tempws As Worksheet = wbObject.Worksheets(wbObject.Worksheets.Count)
                    wsResult.Move(, tempws)
                End If
            End If
        End If
    End Sub
'.....

That's all for now 🙂