An SQL Connection to a password-protected Excel file is not feasible.
However, we can automate a procedure that creates a new Excel file that is identical to the first, but it is not password-protected. So, we will send SQL queries to the new Excel file.
Hence, the below method could be followed in order for the task to be automated.
- The password-protected file could be opened via the "Launch Excel" Action.
In the "Advanced" tab, both the "Excel file needs authentication" and "Open as ReadOnly" boxes would need to be checked.
- Via the "UI / Windows" Actions, go to File > Info > Protect Workbook > Encrypt with Password and specify an empty password.
The %""% expression could be used in the "Populate Text Field in Window" Action.
- Via the "Close Excel" Action, the current file could be closed and saved as a new one at the desired directory.
- The needed SQL Statements could now be executed in the new file.
It is crucial that the relevant driver has been installed on the machine. The following Connection String could be utilized, as well.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myExcelFile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";