Count the number of open workbooks

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

image

I restart my computer and it’s working !
Thank you !

1 Like