Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Workbooks.count

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can I count the hidden workbooks and xla files that are open.

    The workbooks.count only shows me the count for visible xls files

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

    Default

    Hi,

    Are you sure that hidden workbooks aren't being counted by Workbooks.Count? I just tried it on my machine and it included all of them.

    Anyway, here's some code for getting the other bits you want. Hope it helps.

    Code:
    Sub GetCount()
    Dim lngWorkbookCount As Long
    Dim lngAvailableAddins As Long
    Dim lngInstalledAddins As Long
    Dim xlAddin As AddIn
    
    
    lngWorkbookCount = Workbooks.Count
    lngAvailableAddins = Excel.AddIns.Count
    
    For Each xlAddin In Application.AddIns
        If xlAddin.Installed = True Then
            lngInstalledAddins = lngInstalledAddins + 1
        End If
    Next
    
    
    MsgBox "Workbooks open = " & lngWorkbookCount & vbCrLf & _
            "Available addins = " & lngAvailableAddins & vbCrLf & _
            "Installed addins = " & lngInstalledAddins
    
    End Sub

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks DK

    You may be right about the hidden files. However what I am really trying to count is open xla files. These files are not installed add-ins and when I ran your code this did not show up.

    Any thoughts ?

    Thanks

    John

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

    Default

    --------------------------------------------------------------------------------
    Thanks DK

    You may be right about the hidden files. However what I am really trying to count is open xla files. These files are not installed add-ins and when I ran your code this did not show up.

    Any thoughts ?

    Thanks

    John
    Hi John,

    The installed add-ins as per my code gives a count of the add-ins installed i.e. those which are ticked in the Tools, Addins dialog box. If you run the code and it says for example Installed add-ins=4 going into the VB editor and then looking in the project explorer you'll see 4 xla files.

    Unless I've misunderstood your request I think this is what you were after. If not, please repost.

    Regards,
    Dan

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks DK
    The xla file I am using is not an installed add-in. It does not show up in the Tools - add-in list in Excel.

    It is just an xls file with VBA code that has been saved as an xla file.

    I do not intend to make this an add-in as I need to distribute this file and also change it often.

    Thanks

    John



  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Excel only considers as an AddIn those files that are installed using the Tools, AddIns menu command. Your file is just a hidden workbook that has .xla as its extension.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    New Member
    Join Date
    Oct 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Workbooks.count

    I know this is an old post, but I've run into the same problem.

    I'm working with a .xlam file I intend to make an add-in, but for now I've simply got the file open (and not added to Application.AddIns). I'm trying to write an installer macro that 1) closes the workbook, 2) copies the .xlam into the AddIns directory, 3) re-opens it, and 4) adds it to the AddIns collection. Running into problems on the second 'For...Next' loop below:


    Code:
    Dim fso As New FileSystemObject
    Dim oFile
    Dim strAddInLoc As String
    Dim ThisAddin As String
    Dim i As Integer
    
    
        'remove add-in from AddIns, if present
        For i = 1 To AddIns.Count
            If AddIns(i).Name = ThisAddin Then
                AddIns.Remove (i)
                Exit For
            End If
        Next i
    
    
        ' close add-in, if open
        For i = 1 To Workbooks.Count
            If Workbooks(i).Name = ThisAddin Then
                Workbooks(i).Close
                Exit For
            End If
        Next i
    
    
           'copy & re-open
            oFile.Copy strAddInLoc & oFile.Name, True
            Set oFile = fso.GetFile(strAddInLoc & oFile.Name)
            Workbooks.Open (oFile.Path)
            Workbooks(oFile.Name).IsAddin = True
       
           
        'install add-in
        With Application.AddIns
            .Add (oFile.Path)
            .Item(ThisAddin).Installed = True
        End With

    But Workbooks.Count only returns a count of .xlsb/.xls/.xlsx/.xlsm, as far as I can tell. How can I get a count of ALL open workbooks, including AddIns (both Installed, and otherwise)?

    Much appreciated!

  8. #8
    New Member
    Join Date
    Oct 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbooks.count

    OK I've got an easy workaround, but it feels like there should be a more legitimate solution to this one....

    Code:
    On Error Resume Next
    Workbooks(ThisAddIn).Close
    On Error GoTo 0

Some videos you may like

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
  •