vb.net - vb.net获取事件excel工作簿的完整路径和文件名

我的代码几天前就可以使用了，但是却忘了保存工作副本。我花了4个星期才找到这个答案，并且不想再花那么多时间，所以...

除了 objWorkBook 这行会返回错误，这里的所有内容都可以正常工作:
“在给变量赋值之前，先使用变量'objWorkBook'。在运行时可能会导致空引用异常。”

有什么建议？

Dim objExcel As Excel.Application = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
Dim objWorkBook As Excel.Workbook

Dim totalWorkBooks As Integer = objExcel.Workbooks.Count
MsgBox(totalWorkBooks & " is Number of Open Workbooks")

Dim ActiveBookIndex As Integer = objExcel.ActiveWindow.Index
MsgBox(ActiveBookIndex & " is Active Window Index")

Dim FullName As String = objWorkBook.FullName
MsgBox(FullName & " is FullName")

Dim OnlyName As String = objWorkBook.Name
MsgBox(OnlyName & " is Name without the Path")

我忘记了我赋予的价值。

我的目标是将打开的Excel工作簿名称与已知位置的名称进行比较，以便它们匹配时我的程序可以继续进行。我需要上面的代码，因此可以将它与If-Then中的以下代码进行比较，以便我的程序可以继续进行。

Dim dir As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim FullFileName As String = dir & "\My_File_Name.xlsx"

从积极的角度来看，我拼凑了一个解决方案，即使这不是我一直在寻找的答案...。

Dim p() As Process = System.Diagnostics.Process.GetProcessesByName("Excel")
Dim Title As String = p(0).MainWindowTitle
Dim dir As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        Dim FullFileName As String = dir & "\" & Replace(Title, "Microsoft Excel - ", "") & ".xlsx"

MsgBox(dir)
MsgBox(Title)
MsgBox(FullFileName)

暂时可以使用，但我想换一种方式解决。

最佳答案

换线
Dim objWorkBook As Excel.Workbook
至
Dim objWorkBook As Excel.Workbook = Nothing
另外，在尝试在objWorkBook行中使用它之前，您的Dim FullName As String = objWorkBook.FullName对象未分配任何内容

这是您要尝试的吗？

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim objExcel As Excel.Application = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
        Dim objWorkBook As Excel.Workbook

        Dim totalWorkBooks As Integer = objExcel.Workbooks.Count
        MsgBox (totalWorkBooks & " is Number of Open Workbooks")

        Dim ActiveBookIndex As Integer = objExcel.ActiveWindow.Index
        MsgBox (ActiveBookIndex & " is Active Window Index")

        '~~> Set the workbook to say first workbook.
        '~~> You can use a loop here as well to loop through
        '~~> the workbooks count
        objWorkBook = objExcel.Workbooks(1)

        Dim FullName As String = objWorkBook.FullName
        MsgBox (FullName & " is FullName")

        Dim OnlyName As String = objWorkBook.Name
        MsgBox (OnlyName & " is Name without the Path")

        '
        '~~> Rest of the code
        '
    End Sub
End Class

编辑:评论的后续行动

但是，假设我已经打开了9个工作簿，如何让我的应用在不知道完整路径和文件名的情况下进行索引，操作，在它们之间切换...

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim objExcel As Excel.Application = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
        Dim objWorkBook As Excel.Workbook = Nothing
        Dim FullName As String = ""
        Dim OnlyName As String = ""

        Dim totalWorkBooks As Integer = objExcel.Workbooks.Count
        MsgBox (totalWorkBooks & " is Number of Open Workbooks")

        For i As Integer = 1 To totalWorkBooks
            objWorkBook = objExcel.Workbooks(i)

            With objWorkBook
                FullName = .FullName
                OnlyName = .Name

                MessageBox.Show (FullName & " is FullName and " & OnlyName & " is Name without the Path")

                '
                '~~> Rest of the code here to manipulate the workbook. For example
                ' objWorkBook.Sheets(1).Range("A1").Value = "Blah Blah"
                '

            End With
        Next i

        releaseObject (objExcel)
        releaseObject (objWorkBook)
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

关于vb.net - vb.net获取事件excel工作簿的完整路径和文件名，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19903203/