Get the number of "sheets" in the Excel file
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Get the number of "sheets" in the Excel file

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary Canada
    Posts
    222
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi,

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

    thanks in advance

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:-

    Code:
    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



  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary Canada
    Posts
    222
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    best wishes for you and your family.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com