Get the number of "sheets" in the Excel file

[aryan_hr]

Hi,

is it possible to get the number of "Sheets" in the Excel file, WITHOUT OPENING IT??

thanks in advance

 

[dk]

I'm not sure you can do this and get a precise sheet count without opening the workbook somewhere along the line. However, this code allows you to get a count of the number of worksheets (i.e. excluding charts, macro sheets, dialog sheets). In order for this code to run you must choose Tools, References from the VB editor and select:-

• 
  * Microsoft ActiveX Data Objects 2.n Library
  * Microsoft ADO Ext 2.n for DDL and Security

Once you've done that paste this code in a standard module:-

Sub GetSheetCount()
Dim oConn As New ADODB.Connection, adoxCat As ADOX.Catalog, strFileName As String

On Error GoTo ErrHandler:

'Put the file you want here
strFileName = "C:my documentsgeneral ledger pivot table.xls"

With oConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open strFileName
End With

Set adoxCat = New Catalog
Set adoxCat.ActiveConnection = oConn

MsgBox adoxCat.Tables.Count

Exit Sub

ErrHandler:
MsgBox Err.Description, , "An error occurred"
End Sub

HTH,
D

 

[aryan_hr]

thanks for your reply.
It worked and I could get worksheets information.

best wishes for you and your family.