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

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
38
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
 

Attachments

  • Add-In Name.PNG
    Add-In Name.PNG
    16.4 KB · Views: 2

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,550
What happens when you record a macro of you doing this process manually?
 

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
38
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,550
Have you tried

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

There's a handy write up here


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

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
38
Have you tried

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

There's a handy write up here


Using AddIns("MyAddInName").Installed=True and AddIns("MyAddInName").Installed=False to enable and disable
Thanks! That worked perfectly!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,224
Messages
5,576,830
Members
412,749
Latest member
BlakeVanderMeer
Top