Getting the WorkSheet names in a XLAM file

[Rasm]

hmmm - I have installed a ALAM file as an add-in. I developed all the code in a XLSM file - in that file format the code below works. Next I save XLSM file as a XLAM file - however now the code does not work the same - in the sense that it does not the name 'NameXLAMworkbook' - so in XLSM mode - I have 2 workbooks - one containg code - the other containing data. In XLAM mode the WorkBooks.count is only one - when in truth it should be 2.

If I hardcode my 'NameXLAMworkbook' value - see after the 2 loops -then the code works fine. However - I like to be able to identify the version of the XLAM file - I do that adding a version number to the name. guess it brings up the entire question of how do you update XLAM file - do you simply overwrite the old XLAM file with a new version having the exact same file name.

Anyway - My main problem is how to get the Workbook and corresponding sheets included in the WorkBooks.Count and the resulting Sheet.count

  '**** Finds Name of XLAM file
    NameXLAMworkbook = vbNullString
    For i = 1 To Workbooks.Count
        For ii = 1 To Workbooks(i).Sheets.Count
            If Workbooks(i).Sheets(ii).Name = "Default_Settings_01" Then NameXLAMworkbook = Workbooks(i).Name
        Next ii
    Next i
    If NameXLAMworkbook = vbNullString Then NameXLAMworkbook = "PF_Data_Handler.xlam"

 

[Rasm]

Correction XLAM file

 

[RoryA]

Where is this code located? If you want to iterate the Addins, you want the Addins collection, not the Workbooks collection. Typically, you would simply save a new version over the top of the old one with the same name.

 

[Rasm]

RoRya

ok - I am writting all the code in a XLSM file - that way I can view my sheet containing the settings - if I save the file as a XLAM file - the I cannot view my settings sheet. Making it much trickier to work with. But I get you point of Addin collections. I will finish up my settings sheet in XLSM mode - next I will work with it in the XLAM mode - do you have an example of how to use the addin collection - I searched the web - get only useless hits.
Thanks

 

[RoryA]

What I was really getting at is that if the code is in the addin itself, then all you need is ThisWorkbook.Name - there is no need to loop at all.

 

[Rasm]

ahhhhh - I try that thanks -

but this loop does work - so I will try you method first - if not I use the loop

For i = 1 To AddIns.Count

 

[Rasm]

Rorya
ahh - your suggestion works perfect - no need for loop - thanks a bunch

 

[Rasm]

Rorya

OK - Sall good - I am up and running - However I do have a question for you. The only reason I do all the development with a file with extension XLSM is so that I can view my "Default_settings" sheet - this is where I record list.indexes and other settings - so next time the user loads the addin - the choices are same as last time. However if I do the development using a XLAM format - then I cannot view my settings sheet - any suggestions? - secondly I can not save the new version with a new name - but I suppose I can just make a copy of each version and then have one development version.

Anyway - this is no big deal - but just in case you have an answer.

 

[RoryA]

As a general rule I store user settings in the registry, not in the add-in.
It's better and easier to design as an xlsm anyway - makes no difference to the code really.

 

[Rasm]

Rorya
Good point about registry - but I have written code - so I leave it in the sheet - that should not be a concern?
Thanks