![]() To retrieve all the contents of a spreadsheet, you can use the following SQL query in the Execute SQL statement action. This approach is faster and increases the performance of the flow. When the manipulation of the Excel file is complete, use the Delete file(s) action to delete the nonprotected copy of the Excel file.Īlthough the Read from Excel worksheet action can read the contents of an Excel worksheet, loops can take a significant time to iterate through the retrieved data.Ī more efficient way to retrieve specific values from spreadsheets is to treat Excel files as databases and execute SQL queries on them. Lastly, deploy the Close Excel action to save the nonprotected workbook as a new Excel file.Īfter saving the file, follow the instructions in Open a SQL connection to an Excel file to open a connection to it. To press the OK button in the dialog and apply the changes, deploy the Press button in window action. To populate an empty string, use the following expression: %""%. You can find more information about UI automation and how to use the respective actions in Automate desktop applications.Īfter selecting Encrypt with Password, populate an empty string in the pop-up dialog using the Populate text field in window action. Next, deploy the appropriate UI automation actions and navigate to File > Info > Protect Workbook > Encrypt with Password. The file is password-protected, so enter the appropriate password in the Password field. To achieve that, launch the Excel file using the Launch Excel action. The Open SQL connection action can't connect to password-protected Excel files, so you have to remove the protection. Open a SQL connection to a password-protected Excel fileĪ different approach is required in scenarios where you run SQL queries on password-protected Excel files. To use the presented connection string successfully, you have to download and install Microsoft Access Database Engine 2010 Redistributable. ![]() Provider=.12.0 Data Source=%Excel_File_Path% Extended Properties="Excel 12.0 Xml HDR=YES" Now, deploy the Open SQL connection action and populate the following connection string in its properties. Optionally, you can skip this step and use the hard-coded path of the file later in the flow. To establish the connection, create a new variable named %Excel_File_Path% and initialize it with the Excel file path. Open a SQL connection to an Excel fileīefore running a SQL query, you have to open a connection with the Excel file you want to access. To achieve this functionality without SQL queries, you need loops, conditionals, and multiple Excel actions.Īlternatively, you can implement this functionality with SQL queries using only two actions, Open SQL connection and Execute SQL statements. Suppose a flow has to modify only the Excel registries that contain a particular value. Although Excel actions can handle most Excel automation scenarios, SQL queries can retrieve and manipulate significant amounts of Excel data more efficiently.
0 Comments
Leave a Reply. |