I am using Microsoft Task Scheduler to run Excel models everyday. The models retrieve data from the database and do stuff. The connection strings contain all information(username, password etc.) needed to connect to the database. However, I get this prompt sometimes:
SQL Login Prompt
When I click “OK”, everything goes fine, so I need the Excel to just click OK because the username and password are already there. I want to suppress this window by clicking OK. Otherwise the models stop running and wait for me to click OK manually.
I checked for the solutions on the web but did not encounter a working one. I tried to change the connection string in various ways but it did not work.
Does anyone have any idea on how to solve this issue?
Edit: Here is the code I am running on VBScript for Task Scheduler.
'Input Excel File's Full Path ExcelFilePath = "MyExcelFile" 'Input Module/Macro name within the Excel File MacroPath = "Macro1" 'Create an instance of Excel Set ExcelApp = CreateObject("Excel.Application") 'Do you want this Excel instance to be visible? ExcelApp.Visible = True 'or "False" 'Prevent any App Launch Alerts (ie Update External Links) ExcelApp.DisplayAlerts = False 'Open Excel File Set wb = ExcelApp.Workbooks.Open(ExcelFilePath) 'Execute Macro Code ExcelApp.Run MacroPath WScript.Sleep 10000 'Input Module/Macro name within the Excel File MacroPath = "Macro2" 'Execute Macro Code ExcelApp.Run MacroPath WScript.Sleep 10000 'Save Excel File (if applicable) wb.Save 'Reset Display Alerts Before Closing ExcelApp.DisplayAlerts = True 'Close Excel File wb.Close 'End instance of Excel ExcelApp.Quit
The excel VBA code for Macro1 is just
Sub UpdateModel() ActiveWorkbook.RefreshAll End Sub
command for pivot tables which are getting data from the database.