question for RobBroekhuis 1

[Luis939]

well since i noticed rob is back, welcome back!, hopefully you can see back to this thread, you had generously offered to show me some code of yours but you left, perhaps if you had the chance, you could post it here

thread707-518717

 

[RobBroekhuis]

Wow, you have a long memory. And my code is hardly revolutionary, but I'm happy to share it. Below is an example from one of my addins. The AddIn workbook has several hidden sheets, but only one visible one, which has a button the user can click to install as an AddIn (user has the option of just working with the xls). The button calls the InstallAsAddin sub:

Sub InstallAsAddIn()
Dim s As String, oldname As String
If ThisWorkbook.IsAddin Then Exit Sub
ThisWorkbook.IsAddin = True
s = Application.StartupPath
'it's possible that the following line needs changing,
'based on your Office setup. It works consistently
'throughout my organization's environment
s = Left(s, Len(s) - 13) + "addins\RC1 toolbox.xla"
'if necessary, remove older version of toolbox
On Error Resume Next
AddIns("RC1 toolbox&quot.Installed = False
Kill s
On Error GoTo 0
oldname = ThisWorkbook.FullName
ThisWorkbook.SaveAs s, xlAddIn
'the following spaghetti-construction is necessary to avoid
'Excel from getting confused between the .xls and .xla files
ThisWorkbook.IsAddin = False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs oldname, xlWorkbookNormal
Application.DisplayAlerts = True
Application.AddIns.Add s
AddIns("RC1 toolbox&quot.Installed = True
'a flag telling the workbook_close event not to
'remove the custom menus set up by the app
SkipDeleteMenus = True
ThisWorkbook.Close 'closes the .xls, but the .xla is still loaded
End Sub

Since I keep the app on my own PC as an add-in, I need an easy way to save my add-in as a .xls for distribution. I have a button on my "About" form called "Save as XLS" (only visible when I am the user), which calls the following sub:

Public Sub SaveAsXLS()
Dim sh As Worksheet, XLSName As String, XLAName As String
XLAName = ThisWorkbook.FullName
'substitute your own name below
XLSName = Application.DefaultFilePath & "\RC1 toolbox.xls"
XLSName = Application.GetSaveAsFilename(XLSName, _
"Excel workbooks (*.xls),*.xls", 1, _
"Location for toolbox&quot
If XLSName = "False" Then Exit Sub
Application.ScreenUpdating = False
ThisWorkbook.IsAddin = False
'keep only the button worksheet visible
For Each sh In ThisWorkbook.Sheets
If sh.Name <> &quot;button&quot; Then sh.Visible = xlSheetHidden
Next sh
ThisWorkbook.SaveAs Filename:=XLSName, FileFormat:=xlWorkbookNormal
ThisWorkbook.IsAddin = True
Application.DisplayAlerts = False
ThisWorkbook.SaveAs XLAName, xlAddIn
Application.DisplayAlerts = True
End Sub

I hope this helps. It's worked well for me in several applications.


Rob

 

[Luis939]

Cool, thanks for sharing Rob!