vb.net 如何将我的活动 Excel 工作簿和活动 Excel 工作表声明为变量？

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

提问by user5480156

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:

将评论中的信息添加到 Karen Payne 的回答中：

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?

即：如何附加到打开的 Excel 实例并检索对 ActiveWorkbook 的引用？

回答by Karen Payne

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

也许以下内容会有所帮助。这是一个在 xlWorkSheet 中设置活动表的演示，可以根据需要使用。

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

回答by TnTinMn

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.

以下是附加到正在运行的 Excel 实例的两种技术。我认为“Button1_Click”中显示的方法是您正在寻找的方法，但我还展示了第二种方法，该方法在运行对象表 (ROT) 中查找匹配的工作簿名称。

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

回答by KFayal

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

对不起，我的最后一个答案.. 我没有看到你试图在 .NET 中做到这一点。无论如何，就像在 Excel 上下文中一样，您必须首先打开或创建所有要使用的工作簿。如果您已经打开它，那么您可以使用要分配给工作簿变量的工作簿的文件名。

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

回答by Karen Payne

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

回答by KFayal

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

对于这些 Excel 对象，您必须使用“设置”关键字为它们分配值。例如：

Set oWB = ActiveWorkbook