Enhancing Power Automate Desktop Proficiency with Access and PowerShell Integration

1. Install ImportExcel Module
2. Creating Desktop Flow
   1. Create Input Variable
   2. Retrieving Excel File From Folder
   3. Create “For each” loop
   4. Launch Excel
   5. Read from Excel worksheet
   6. Running PowerShell Script to insert data into our Access database

I recently had a conversation with a friend about the relevance of Access databases in today’s tech landscape. To my surprise, he emphatically affirmed that Access databases are still widely used. The last time I encountered Access was during my college days when it was used as a teaching tool for database modeling. Honestly, I had no idea that organizations continued to rely on it.

In my personal journey, Access has played a pivotal role in enhancing my skills with Power Automate Desktop. Initially, my goal was to create a Model Driven App and utilize it in conjunction with Power Apps desktop to execute desktop flows. This approach worked reasonably well, but over time, I no longer had the Model Driven App and didn’t want to recreate it. That’s when I turned to Access as my training ground.

However, even my practice with desktop flows on Access forms had its limitations. During my experimentation with PowerShell, I became determined to find a way to insert data into an Access database file without ever having to open Access itself. So, for those who still rely on Access in their workflows, This one is for you…

Here’s what you’ll need: a designated location to store your Excel file. Personally, I store mine in a folder on my desktop.

Install ImportExcel Module

To install the ImportExcel module, please open PowerShell and execute the provided script below:

Install-Module -Name ImportExcel -Scope CurrentUser

Creating Desktop Flow

Create Input Variable

To create an input variable for storing the path to the Access database we are working with, follow these steps to configure it:

1. Open the Power Automate Desktop workflow where you intend to use the variable.
2. Locate the area where you can define variables, typically in the workflow settings or variable panel.
3. Create a new variable and give it a meaningful name, such as “varAccessDb.”
4. Set the data type of this variable to “String” since it will store the file path, which is typically a text value.
5. Save your workflow to ensure the variable is retained.

Now, you have an input variable named “varAccessDb” that can be used within your PowerShell script to hold the path to the Access database.

Retrieving Excel File From Folder

To initiate the process, the first action we need to perform is to retrieve the Excel file(s) that will be used in our flow. Follow these steps:

1. In the Actions pane on the left-hand side of your Power Automate Desktop interface, type “Get files in folder” in the search bar.
2. Locate the “Get files in folder” action in the search results.
3. Drag and drop the “Get files in folder” action onto your workflow canvas.

This action will allow you to specify the folder from which you want to fetch the Excel files for further processing in your flow.

To direct the “Get files in folder” action to the folder containing your Excel workbooks and set it up to collect all files with a .xlsx extension, follow these steps:

1. Select the “Get files in folder” action block on your workflow canvas to access its configuration.
2. In the “Folder path” input field, specify the path to the folder where your Excel workbooks are located. You can either type the path or use the browse button (…) to navigate to and select the folder.
3. In the “File filter” input box, input “*.xlsx”. This filter will ensure that the action captures all files with the .xlsx extension.
4. Save your configuration.

Now, the “Get files in folder” action is configured to retrieve all Excel files with the .xlsx extension from the designated folder for further processing in your flow.

File filter: *.xlsx

Save.

Create “For each” loop

To make your solution dynamic and capable of handling one or multiple files, you can set up a “For Each” loop. Follow these steps:

1. In the Actions pane, search for “For Each” to find the For Each loop action.
2. Drag and drop the “For Each” loop action onto your workflow canvas.
3. In the “Values to iterate” field of the “For Each” loop, use the %Files% variable that was generated from your “Get files in folder” action.
4. To enhance readability, you can rename the “Store into” section from “CurrentItem” to “CurrentFile.”

By doing this, you’ve set up a loop that will iterate through each file retrieved by the “Get files in folder” action, allowing your workflow to handle both single and multiple files seamlessly.

Launch Excel

To add a “Launch Excel” action within your “For Each” loop and configure it, follow these steps:

1. In the Actions pane, search for “Launch Excel” to locate the “Launch Excel” action.
2. Drag and drop the “Launch Excel” action into your workflow editor, making sure it is nested within the “For Each” loop.

Now, let’s configure the “Launch Excel” action with the following parameters:

• File path: This should point to the current Excel file being processed in the loop. Use the “CurrentFile” variable to specify the file path dynamically.
• Visible: You can choose whether you want Excel to be visible during the execution of your workflow. Set it to “No” if you want Excel to run in the background without displaying the user interface, or “Yes” if you want Excel to be visible.
• Create new instance: You can choose to create a new instance of Excel for each file, ensuring that each file is processed independently. Set it to “Yes” if you want a new instance for each file, or “No” if you want to reuse the same instance for all files.
• Run macro (optional): If you have a specific Excel macro to run, you can specify it here. Otherwise, leave it blank if you don’t need to run a macro.

By configuring the “Launch Excel” action in this manner, you ensure that your workflow opens and processes each Excel file one by one within the “For Each” loop.

Launch Excel: and open the following document
Document Path: %CurrentFile.FullName%

Certainly, if you prefer to make the Excel instance visible, you can set the “Visible” option to “Yes” in the “Launch Excel” action configuration. This choice will allow you to see the Excel interface while the workflow is running, which can be helpful for monitoring and debugging your automation process.

Read from Excel worksheet

To add a “Read from Excel worksheet” action and configure it as described, follow these steps:

1. In the Actions pane, search for “Read from Excel worksheet” and drag it into your workflow editor. Ensure that you place it within the same “For Each” loop as the “Launch Excel” action.
2. Configure the “Read from Excel worksheet” action with the following parameters:

Excel instance: %ExcelInstance%
Retrieve: All available values from worksheet

Running PowerShell Script to insert data into our Access database

This PowerShell script will loop through the Excel data and insert records into the specified Access table without the need to have Access open. It should significantly improve the efficiency of your workflow compared to looping through each record in the spreadsheet using multiple actions in Power Automate Desktop.

1. Add a “Run PowerShell script” action to your workflow.
2. Copy and paste the following PowerShell script into the action, making any necessary updates to match your specific file paths, table names, and data variables:

# Define the path to the Access database
# varAccessDb is the our input variable
$accessDBPath = "%varAccessDb%"

# Define the connection string
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$accessDBPath;Persist Security Info=False;"

# Define the path to the Excel file
$excelFilePath = "%CurrentFile%"

# Import data from the Excel file
# Ensure that your Excel file has columns named PartName, SKU, Description, and Price
$dataCollection = Import-Excel -Path $excelFilePath

try {
    # Create a connection object
    $connection = New-Object System.Data.OleDb.OleDbConnection($connectionString)

    # Open the connection
    $connection.Open()

    foreach ($item in $dataCollection) {
        # Define the SQL query for inserting data
        $query = "INSERT INTO Part ([Part Name], [SKU], [Part Description], [Price]) VALUES ('$($item.PartName)', '$($item.SKU)', '$($item.Description)', $($item.Price))"

        # Create a command object
        $command = New-Object System.Data.OleDb.OleDbCommand($query, $connection)

        # Execute the command (perform the insert operation)
        $command.ExecuteNonQuery()
    }

    # Close the connection
    $connection.Close()
}
catch {
    # Handle the error
    Write-Host "An error occurred: $_"
}
finally {
    # Ensure that the connection is closed even if an error occurs
    if ($connection.State -eq [System.Data.ConnectionState]::Open) {
        $connection.Close()
    }
}

Lastly add an action to close Excel.