I had this code working a few days ago, but forgot to save the working copy. It took me 4 weeks just to find this answer and would not like to take that much time again, so...
Everything here works, except the objWorkBook lines, which return the error: "Variable 'objWorkBook' is used before it has been assigned a value. A null reference exception could result at runtime."
Any suggestions?
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")
I forgot what Value I had assigned.
My objective is to compare an open Excel Workbook name with one in a known location so that if they match, my program can proceed. I need the code above so I can compare it to the following code in an If-Then so that my program can proceed.
Dim dir As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim FullFileName As String = dir & "\My_File_Name.xlsx"
On a positive note, I pieced together A solution, even though it's not the answer I was looking for....
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)
This will work for now, but I would like to solve it the other way.
Change the line
Dim objWorkBook As Excel.Workbook
to
Dim objWorkBook As Excel.Workbook = Nothing
Also your objWorkBook object is not assigned to anything before you are trying to use it in the line Dim FullName As String = objWorkBook.FullName
Is this what you are trying?
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
EDIT: Followup from comments
But let's say I have 9 Workbooks already open, how do I get me app to index, manipulate, switch between them... without knowing the Full Path and File Names ahead of time?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments