Workbooks.count

[John Bennito]

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

[dk]

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.

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

[John Bennito]

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

[dk]

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

[John Bennito]

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

[Juan Pablo González]

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.

[AGI VBA]

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:

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!

[AGI VBA]

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

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