How do I declare my active Excel workbook and active Excel worksheet as a variable?

Question

I am trying to figure how to set the active workbook and active sheet as a variable I can reference later. I have my script set up, and I placed ????'s in the areas where I assume I would put the reference.

Any suggetsions?

  Dim oXL As Application
        Dim oWB As Microsoft.Office.Interop.Excel.Workbook
        Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim oRng As Microsoft.Office.Interop.Excel.Range

        oWB = ????
        oSheet = ?????

Added info from comment to Karen Payne's answer:

I am trying to reference an existing one I already have open, that is the active window at the time of using my application.

i.e.: How can I attach to an open Excel instance and retrieve a reference to the ActiveWorkbook?

Answer 1

Here is the same code, partly commented out as to the activate part and I added in code to show how to get the current active sheet.

    Option Strict On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices

Module SheetCode
        Public Sub SetDefaultSheet(ByVal FileName As String, ByVal SheetName As String)
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
            '
            ' Here I added how to get the current active worksheet
            '
            Dim ActiveSheetInWorkBook As Excel.Worksheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
            Console.WriteLine(ActiveSheetInWorkBook.Name)

            Runtime.InteropServices.Marshal.FinalReleaseComObject(ActiveSheetInWorkBook)
            ActiveSheetInWorkBook = Nothing

            'For x As Integer = 1 To xlWorkSheets.Count
            '    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
            '    If xlWorkSheet.Name = SheetName Then

            '        xlWorkSheet.Activate()

            '        xlWorkSheet.SaveAs(FileName)
            '        Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            '        xlWorkSheet = Nothing

            '        Exit For

            '    End If

            '    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            '    xlWorkSheet = Nothing

            'Next

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)

        End Sub
        Private Sub ReleaseComObject(ByVal obj As Object)
            Try
                If obj IsNot Nothing Then
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                    obj = Nothing
                End If
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub

    End Module

Answer 2

Sorry about my last answer.. I didn't see that you were trying to do it in .NET. Anyway, just like in the context of Excel, you have to first open or create all of the workbooks that you want to work with. If you already have it open, then you can use the file name of the workbook you want to assign it to a workbook variable..

xlWorkbook = xlApp.Workbooks.Item("filename of the workbook already open")

Answer 3

Here are two techniques for attaching to a running Excel instance. I think the method shown in 'Button1_Click' is one you are seeking, but I also showed second method that looks in the Running Object Table (ROT) for a matching Workbook name.

Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop

Public Class Form1
    Private WithEvents ExcelCleanUpTimer As New Timer With {.Interval = 100}

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim app As Excel.Application
        Try ' to attach to any Excel instance
            app = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
        Catch ex As Exception
            MessageBox.Show("No Excel instances found")
            Exit Sub
        End Try

        ' take over responsibility for closing Excel
        app.UserControl = False
        app.Visible = False

        Dim activeWB As Excel.Workbook = app.ActiveWorkbook

        Dim activeSheet As Object = app.ActiveSheet

        ' determine if ActiveSheet is a Worksheet or Chart
        ' if it is a Worksheet, activeChart is Nothing
        ' if it is a Chart, activeWorksheet is Nothing
        Dim activeWorksheet As Excel.Worksheet = TryCast(activeSheet, Excel.Worksheet)
        Dim activeChart As Excel.Chart = TryCast(activeSheet, Excel.Chart)

        If activeWorksheet IsNot Nothing Then
            For Each cell As Excel.Range In activeWorksheet.Range("A1:A20")
                cell.Value2 = "hello"
            Next
        End If

        'shut Excel down, don't save anything
        app.DisplayAlerts = False
        app.Workbooks.Close()
        app.Quit()

        ' using a timer lets any ComObj variables in this method go out of context and
        ' become eligible for finalization.  GC's call to RCW wrapper finalizer will 
        ' release its reference counts on Excel
        ExcelCleanUpTimer.Start()
    End Sub

    Private Sub ExcelCleanUpTimer_Tick(sender As Object, e As EventArgs) Handles ExcelCleanUpTimer.Tick
        ExcelCleanUpTimer.Stop()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub


    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' if this is a saved Workbook, the path will be the full file path
        Dim path As String = "Book1" ' or the full file path to a workbook including the extension

        Dim activeWorkbook As Excel.Workbook = Nothing
        Try
            ' this technique differs for Marshal.GetActiveObject in that if the path 
            ' is a valid file path to an Excel file and Excel is not running, it will
            ' launch Excel to open the file.
            activeWorkbook = CType(Marshal.BindToMoniker(path), Excel.Workbook)
        Catch ex As Exception
            ' an exception will be throw if 'path' is not found in the Runinng Object Table (ROT)
            MessageBox.Show(path & " not found")
            Exit Sub
        End Try

        Dim app As Excel.Application = activeWorkbook.Application
        app.Visible = True
        app.UserControl = True

        ' the wb window will be hidden if opening a file
        activeWorkbook.Windows(1).Visible = True


        ExcelCleanUpTimer.Start()
    End Sub
End Class

Answer 4

Perhaps the following will help. It is a demo that sets the active sheet in xlWorkSheet that can be used as you see fit.

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices

Module SetDefaultWorkSheetCode
    Public Sub SetDefaultSheet(ByVal FileName As String, ByVal SheetName As String)
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False
        xlWorkSheets = xlWorkBook.Sheets

        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetName Then

                xlWorkSheet.Activate()

                xlWorkSheet.SaveAs(FileName)
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

                Exit For

            End If

            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing

        Next

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)

    End Sub
    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Module

Answer 5

With those Excel objects, you have to assign values to them using the "Set" keyword. For example:

Set oWB = ActiveWorkbook