I’m trying to count the number of open workbooks but the result shows me 0. I don’t know why.
Code :
Dim objExcel As Microsoft.Office.Interop.Excel.Application = CType(System.Runtime.InteropServices.Marshal.GetActiveObject(“Excel.Application”),Microsoft.Office.Interop.Excel.Application)
objExcel.Visible = True
Console.WriteLine(objExcel.Workbooks.Count.ToString)
Try
'For i As Integer = 0 To objExcel.Workbooks.Count
’ MsgBox(objExcel.Workbooks.Count)
’ For j As Integer = 1 To objExcel.Workbooks(i).Sheets.Count
'If Excelapp.Workbooks(i).Sheets(j).ToString = “Feuil1” Then
’ MsgBox(“Ok”)
'End If
’ Next
'Next
objExcel.Visible = False
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Are you sure that the result is 0? I ran the code to count the workbooks and it’s working fine for me. The only errors I could find is that the index for workbooks should start with 1 and that you should type cast sheet to worksheet to get the name.
Dim objExcel As Microsoft.Office.Interop.Excel.Application = CType(System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"),Microsoft.Office.Interop.Excel.Application)
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Console.WriteLine("Number of Workbooks: " + objExcel.Workbooks.Count.ToString)
Try
For i As Integer = 1 To objExcel.Workbooks.Count
Console.WriteLine("Sheets in Workbook " + i.ToString + " - " + objExcel.Workbooks(i).Name)
For j As Integer = 1 To objExcel.Workbooks(i).Sheets.Count
xlWorkSheet = CType(objExcel.Workbooks(i).Sheets(j), Microsoft.Office.Interop.Excel.Worksheet)
Console.WriteLine(xlWorkSheet.Name)
If xlWorkSheet.Name = "September 2020" Then
MsgBox("Found the sheet!")
End If
Next
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
I restart my computer and it’s working !
Thank you !
1 Like