PDA

View Full Version : [SOLVED] Deactivate Add-in and save .xlsm as Add-in then activate the ADD-IN again



loveguy1977
12-03-2013, 11:57 PM
Dear, Sir,

I'm seeking your help to Deactivate Add-in and save .xlsm as Add-in then activate the ADD-IN again with the following explaination:

I'm having an WP_ADDINS.xlsm file that always adding data to it. After I added the data, I save it as Excel Add-in (Excel 2007) which will be saved in this folder (C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns). but can't be saved untill the existing add-in has been deactivated. Then after saving the file as ADD-IN, I need to activate the ADD-IN again. This way is westing my time. Therefore, I would like to do all these in one go
I mean:
1. open my WP_ADDINS.xlsm file
2. add my data and save the file only
3. deactivate existing add-in (Macro needed from this step)
4. save WP_ADDINS.xlsm file as Excel Add-in (Excel 2007) in folder mentioned above
5. activate the ADD-IN again
6. close WP_ADDINS.xlsm file

Thank you very much for your help

Jan Karel Pieterse
12-04-2013, 08:44 AM
One way is to make sure the xlam file is marked read-only (in Windows Explorer, right-click, properties, check box). That way you can simply replace the xlam file even when it is loaded in Excel. You will have to restart Excel to make sure it loads the updated add-in however. Oh and you cannot save-as a file to the add-in because the add-in is alrady open in Excel, so you'll have to close the file:

Workbooks("AddinName.xlam").Close false

loveguy1977
12-06-2013, 04:15 AM
Dear,

I got code to disable & enable ADD-IN, now I just need code to save as which mintioned above. I mean save WP_ADDINS.xlsm file as Excel Add-in (Excel 2007) in this folder (C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns).


Sub DisableAddIn()
On Error Resume Next
myAddInName = "WP_ADDINS"
AddIns(myAddInName).Installed = False
Debug.Print Err.Number & " - " & Err.Description
If Err.Number <> 0 Then
Set myAddIn = AddIns.Add(Filename:="C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\" & myAddInName _
& ".XLA", CopyFile:=False)
AddIns(myAddInName).Installed = False
End If
On Error GoTo 0
End Sub


Sub EnableAddIn()
On Error Resume Next
myAddInName = "WP_ADDINS"
AddIns(myAddInName).Installed = True
Debug.Print Err.Number & " - " & Err.Description
If Err.Number <> 0 Then
Set myAddIn = AddIns.Add(Filename:="C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\" & myAddInName _
& ".XLA", CopyFile:=True)
AddIns(myAddInName).Installed = True
End If
On Error GoTo 0
End Sub

Please help, I am realy in need for it
Thank you very much

loveguy1977
12-06-2013, 06:51 AM
I got it


Sub Update_ADDIN()

ActiveWorkbook.Save
On Error Resume Next
myAddInName = ""WP_ADDINS"

AddIns(myAddInName).Installed = False
Debug.Print Err.Number & " - " & Err.Description
If Err.Number <> 0 Then
Set myAddIn = AddIns.Add(Filename:="C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\" & myAddInName & ".xlam", CopyFile:=False)
AddIns(myAddInName).Installed = False
End If

ActiveWorkbook.SaveAs Filename:= _
"CC:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\ & myAddInName & ".xlam", _
FileFormat:=xlOpenXMLAddIn, CreateBackup:=False

AddIns(myAddInName).Installed = True
Debug.Print Err.Number & " - " & Err.Description
If Err.Number <> 0 Then
Set myAddIn = AddIns.Add(Filename:="CC:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\ & myAddInName & ".xlam", CopyFile:=True)
AddIns(myAddInName).Installed = True
End If

'ActiveWorkbook.Close False
Workbooks("WP_ADDINS.xlam").Close False
Application.Quit
On Error GoTo 0
End Sub

Jan Karel Pieterse
12-06-2013, 09:00 AM
There seems to be a " missing in this line:


ActiveWorkbook.SaveAs Filename:= _
"CC:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\ & myAddInName & ".xlam", _
FileFormat:=xlOpenXMLAddIn, CreateBackup:=False


which should read:


ActiveWorkbook.SaveAs Filename:= _
"CC:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\" & myAddInName & ".xlam", _
FileFormat:=xlOpenXMLAddIn, CreateBackup:=False

loveguy1977
12-09-2013, 03:11 AM
Sub Update_ADDIN()

ActiveWorkbook.Save
On Error Resume Next
myAddInName = ""WP_ADDINS"

AddIns(myAddInName).Installed = False
Debug.Print Err.Number & " - " & Err.Description
If Err.Number <> 0 Then
Set myAddIn = AddIns.Add(Filename:="C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\" & myAddInName & ".xlam", CopyFile:=False)
AddIns(myAddInName).Installed = False
End If

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\ & myAddInName & ".xlam", _
FileFormat:=xlOpenXMLAddIn, CreateBackup:=False

AddIns(myAddInName).Installed = True
Debug.Print Err.Number & " - " & Err.Description
If Err.Number <> 0 Then
Set myAddIn = AddIns.Add(Filename:="C:\Users\rXXX\AppData\Roaming\Microsoft\AddIns\ & myAddInName & ".xlam", CopyFile:=True)
AddIns(myAddInName).Installed = True
End If

'ActiveWorkbook.Close False
Workbooks("WP_ADDINS.xlam").Close False
Application.Quit
On Error GoTo 0
End Sub