Using VBA to enable/disable Microsoft add-in that requires a username and password

[mjohnston0209]

I am in the process of creating a code that will open every xlsm file within a folder, run a macro, save the file, and then close the file. This code should help reduce the time needed to update the files (approximately 85) significantly.

The problem I am encountering is that our company purchased a system called Spreadsheet Server that allows us to create queries that pull information directly from our accounting software. The add-in is originally disabled (I prefer this). I would like to enable it before running the macro in the file and then disable after the macro is complete. I have to enter a username and password every time I enable the add-in. What type of coding would I need to enable the add-in and enter my username and password? If I don't enable the add-in, any cells that pull information from our software show an error message, <Error>. Below is the code (which works except for the add-in) that allows me to perform the steps mentioned above in the first line.

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all .xlsm files in a user specified folder and run DataUpdate macro in each file

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

myPath = "W:\Accounting\Financial Reporting\WIPS\"

'Target File Extension (must include wildcard "*")
myExtension = "*.xlsm*"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)

****Enable add-in here

'Change First Worksheet's Background Fill Blue
Application.Run "'" & wb.Name & "'!DataUpdate"

****Disable add-in here

'Save and Close Workbook
wb.Close SaveChanges:=True

'Get next file name
myFile = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Complete!"

ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[mrshl9898]

What happens when you record a macro of you doing this process manually?

 

[mjohnston0209]

It does not record any of the steps. I have learned how to start the add-in. The add-in is for spreadsheet server. To enable the item, I use the coding below.

Sub CommandButton1_Click()

Application.Run "startserver(true)"
Application.Run "RedrawRibbon"

End Sub

However, I cannot disable it.

 

[mrshl9898]

Have you tried

Application.Quit "startserver(true)"
Application.Quit "RedrawRibbon"

There's a handy write up here

How to enable/add and disable/remove an Excel Add-In using VBA?

Something like if addin exists then do nothing else add_addin("AddinName"). I have a few add-ins, when enabled would first pops up a message. So if I enable them at the excel open automatically, it

stackoverflow.com

Using AddIns("MyAddInName").Installed=True and AddIns("MyAddInName").Installed=False to enable and disable

 

[mjohnston0209]

Have you tried

Application.Quit "startserver(true)"
Application.Quit "RedrawRibbon"

There's a handy write up here

How to enable/add and disable/remove an Excel Add-In using VBA?

Something like if addin exists then do nothing else add_addin("AddinName"). I have a few add-ins, when enabled would first pops up a message. So if I enable them at the excel open automatically, it

stackoverflow.com

Using AddIns("MyAddInName").Installed=True and AddIns("MyAddInName").Installed=False to enable and disable

Thanks! That worked perfectly!