I have included here a bit of commented code. As an example I use this code inside a Button_CLick event. It is supposed to check if the user already has an automatable Excel Instance running. If they have then the code adds a new sheet and put soem data into it.
If no such Automatable Excel exists teh code creates a new instance and adds a sheet with the data.
For this example each sheet has a unique name made from Now.TickCount.
Pressing the button several times works great... it adds successive sheets to the document. However, if the user then closes Excel down and we press the button again instead on opening a fresh Excel instance it seems to automate the old one left over (who's process is visible in the Task Manager until the demo app is shut down). The outcome of this is that Excel just hangs.
The rest of this post is my code. Create a windows form application with one form containing one button. In the click event of the button add:
'create new automation object
Dim objExcel As Object
Dim booNew As Boolean
Try
'if there is already an automatable version of excel open then use it
objExcel = GetObject(, "Excel.Application")
booNew = False
Catch ex As Exception
'if theres an error then there is no automatable version available so create one
objExcel = CreateObject("Excel.Application")
booNew = True
End Try
'stop excel displaying alerts to the user during automation
objExcel.DisplayAlerts = False
'if there are no workbooks then create one
Dim objBooks = objExcel.Workbooks
If objBooks.Count < 1 Then
objBooks.Add()
End If
'set up to use the last book available
Dim objBook = objBooks(objBooks.Count)
'remove any sheets we dont want if we are using a new instance of excel
If booNew Then
objBook.WorkSheets("Sheet2").Select()
objExcel.ActiveWindow.SelectedSheets.Delete()
objBook.WorkSheets("Sheet3").Select()
objExcel.ActiveWindow.SelectedSheets.Delete()
End If
Dim objRange As Object
Dim objSheets = objBook.WorkSheets
Dim objSheet As Object
'create a new sheet
objSheet = objSheets.Add()
'name the new sheet
Dim strName As String = Now.Ticks.ToString
Try
'create a new sheet with the name that identifies the test and source table
objSheet.Name = "Test - " + strName
'if it already exists an error will be raised, catch this
Catch
'should create new name but for now will just give an error message
MessageBox.Show("You are trying to add a sheet with the same name as an existing sheet (" + strName + " ) delete or rename the old one first", "Sheet Name Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
objRange = Nothing
objSheet = Nothing
objSheets = Nothing
objBook = Nothing
objBooks = Nothing
objExcel = Nothing
Exit Sub
End Try
'set the window up
objExcel.WindowState = 2
objExcel.Visible = True
objExcel.UserControl = True
'create a header string array for testing
Dim arrHeader As String() = {"COL 1", "COL 2"}
'set up a range that represents the header
objRange = objSheet.Range("A1", Reflection.Missing.Value)
objRange = objRange.Resize(1, arrHeader.GetLength(0))
'add the header in
objRange.Value = arrHeader
'create some data for a test
Dim arrData(2, 1) As Single
arrData(0, 0) = 0.0
arrData(1, 0) = 0.1
arrData(2, 0) = 0.2
arrData(0, 1) = 0.3
arrData(1, 1) = 0.4
arrData(2, 1) = 0.5
'set up a range that represents teh data area
objRange = objSheet.Range("A2", Reflection.Missing.Value)
objRange = objRange.Resize(arrData.GetUpperBound(0) + 1, arrData.GetUpperBound(1) + 1)
'add the data in
objRange.Value = arrData
'set the window state to normal
objExcel.windowstate = -4143
'cleanup
objRange = Nothing
objSheet = Nothing
objSheets = Nothing
objBook = Nothing
objBooks = Nothing
objExcel = Nothing
