Workbooks.count

John Bennito

New Member
Joined
Feb 15, 2002
Messages
11
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0
--------------------------------------------------------------------------------
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top