Write to Excel Object

Question

I am trying to write a code that exports data to excel after user prompted actions. Basically, I have been able to export to Excel successfully, but the 2nd instance I want to write to a new tab instead of a new Excel application.

oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(3)
oSheet.Delete()
oSheet = oBook.Worksheets(2)
oSheet.Delete()
oSheet = oBook.Worksheets(1)
oSheet.Name = "Run " & Counter

At this point, the user will press a button, making Excel no longer active. So when I want to write more data to a new sheet, the Object commands do not 'work unless I repeat code exactly.

I tried:

Counter +=1

'For the first instance
If Counter = 1 Then

        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(3)
        oSheet.Delete()
        oSheet = oBook.Worksheets(2)
        oSheet.Delete()
        oSheet = oBook.Worksheets(1)
        oSheet.Name = "Run " & Counter

Else
'For every instance after that the user wants to do another run

        oExcel.ActivateObject(Excel.Application)
        oBook = oExcel.Workbooks(1)
        oSheet = oBook.Worksheets.Add
        oSheet.Name = "Run " & Counter

End If

I have been looking for days and am getting very frustrated. I do not know how to reference back to the open excel in order to continue to writing data ... after the user has pressed a button on the VB form confirming they want to do another run.

Answer 1

To get a reference to an already-running instance of excel you can use GetObject.

Eg:

' Test to see if a copy of Excel is already running. 
Private Sub testExcelRunning()
    On Error Resume Next 
    ' GetObject called without the first argument returns a 
    ' reference to an instance of the application. If the 
    ' application is not already running, an error occurs. 
    Dim excelObj As Object = GetObject(, "Excel.Application")
    If Err.Number = 0 Then
        MsgBox("Excel is running")
    Else
        MsgBox("Excel is not running")
    End If
    Err.Clear()
    excelObj = Nothing 
End Sub

http://msdn.microsoft.com/en-us/library/e9waz863(v=vs.90).aspx

If Excel is not already running you can start a new instance using CreateObject.