Optimizing Workflow in Power Automate: Challenges and Solutions for PDF to Excel Mapping

[Ronyn77]

Hello,

I am new to Power Automate and am using the desktop version, which is free. The ultimate goal of my flow is to take a PDF invoice and create an Excel file that maps all the invoice data. As of now, I've created the flow that takes a PDF as input, parses it into a text variable, and uses regex and some for each loops to map all the necessary data to the Excel file.

This flow has some downsides for me, which I'd like to discuss to see if there's an alternative approach.

1. To change the PDF file—because I've completed the previous mapping and need to map a new PDF—I have to edit the flow and specify the new file I want to map in Excel. Ideally, after starting the flow, a window would pop up where I can drag and drop the PDF I want to map, or at least upload it by browsing within Windows. I searched online and it seems such an option might not be possible without using third-party software, which is also paid. Or am I mistaken and there is a way?

2) Another issue I have is that when I start the flow, it opens the Excel file and shows me the entire mapping process until it finishes. Is there a way to run it in the background without visually seeing the entire process? If not, I am forced to continue in this manner. Please share your thoughts;

2.1) A problem I encountered following the mentioned procedure is: if the file is already open, it returns an error. The solution I found online is to terminate the Excel process in advance, before opening it. However, this isn't ideal either, because if other Excel files are already open on the PC, it closes all the files. Is there a way to close only the file that will be used for mapping? If there isn't any solution for this, the only thing that comes to my mind is to start the flow in a virtual machine designed solely for this purpose and not interact with the daily work of the user who is using the PC for other things. Here too, I am open to any suggestions.

3) If the first point is not achievable, another solution I found online is to use the email client (Outlook). In my case, I am receiving the PDFs that I need to map in Excel by email. So, what I could do is read the emails from the specified sender and download the attachment, which will then be processed in the flow I've already created. Here immediately arises a question: I do not want to start the flow for each new email that arrives. I want the flow to remain listening, and when a new email arrives and matches the specified rules, to generate the Excel file I need.

[Deenuji]

@Ronyn77 _

1. Create a cloud flow and set the trigger point as either receiving an email in Outlook or uploading a file in SharePoint.

2. Extract the PDF file from Outlook and pass or pass the SharePoint file to Power Automate AI Builder.

3. AI Builder will extract all the relevant details from your PDF and convert them into an Excel format. However, you may need to implement some workarounds to make it work seamlessly.

Please refer this - https://youtu.be/PD2eKTzkZ70?feature=shared
Hope this helps.

Thanks,

Deenu

[Ronyn77]

I was asking how to do it using the desktop version, not the cloud one. Is it possible, or not? Just in case I would follow your suggestion to use the cloud version, could I use it for free, or am I required to subscribe?

[WillSG · Accepted solution]

Hi @Ronyn77  I hope you are doing well.

Here are my answers to your questions:

1. Use the Get Files in Folder Action, this will generate a variable to be used into the For Each Action, into the For Each will Go your Code already built.
2. The Launch Excel Action has the option to make the Excel Instance Invisible, my recommendation here is to open the Excel File only if you have a new email with a file.
3. The issue with Excel already opened is because PAD opened the file, but if PAD doesn’t close the Excel you will end up with multiple Excel Files opened in the background, so use the Close Excel Action and then you can open the file manually to review it.
4. To move the files from Outlook to Folder you can use PAD, no need for additional programs.

Here is the snipped code of some of the actions that you need to enhance your robot.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''Your Excel File Path''' Visible: False ReadOnly: False Instance=> ExcelInstance

Folder.GetFiles Folder: $'''Your Folder Path''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files

LOOP FOREACH CurrentItem IN Files

    # Here goes the actions that fill out the Excel File

END

Please let me know if it works, happy automation!,

If I have addressed your inquiry successfully, kindly consider marking my response as the preferred solution. If you found my assistance helpful, a 'Thumbs Up' would be greatly appreciated.

Additionally, I offer specialized consultancy and development services leveraging PAD. If you're interested in exploring these services further, feel free to DM me, and we can initiate a discussion.

Kind regards,

Will SG

Managing Director & Automation Lead

RAMS CR (Recruitment & Automation)

LinkedIn Profile

[MichaelAnnis]

The listening part is the premium license. You have to use a cloud flow to listen to your email, and you can trigger a desktop flow from there. If you don’t want to use PAD on your local workstation (hijacking your mouse and keyboard), then you will need to designate either another computer (that’s not being used) or a VM, which would be an additional charge. 

to my understanding, the listening (connector) license is only $15/month. The VMs are more. 

as for the flow itself, it sounds like we could “get window data” on any open excel files that matches a specific name, and then close just that excel window, not terminate all excel processes. 

would it be viable to run this once a day?  This may open up options to you where we simply build Outlook rules to house these emails in a folder, and run the flow once per day to loop through each email and do everything, and then you can just run it as often as you need to (ex. Once a day).  This would be free with your current license and not require cloud or premium connector license. 

good luck!

[Ronyn77]

Hello @WillSG,

Thank you for the suggestions.

Regarding point 1), I followed your advice. The loop iterates through all the PDFs found in the folder after initiating the flow, which works fine, but I'm looking to improve it if possible. Is there a way to set the automation to listen mode, so it starts mapping from PDF to Excel as soon as a new PDF is added to the folder?

2) I set the Excel instance to be invisible as you suggested. However, it briefly pops up on the screen before disappearing again, then it operates in the background. It's better than before but still not ideal.

Lastly, I looked at the "code" you mentioned at the end, only after completing the PAD flow, because initially, I thought I had to incorporate your code somewhere in PAD. Upon closer inspection, it appears to be the code for the automation. Did you write it manually, or is it exported from somewhere?

[Ronyn77]

Hello @MichaelAnnis ,

I would prefer to avoid the premium license. As of now, I only need to map the invoices for a single supplier. Obtaining a license would make sense if I had to process a large volume of invoices from multiple suppliers, saving time on individual mappings since using regex to do it properly is time-consuming.

Having a Virtual Machine is not an issue; there are many free VM options available, and I already have an infrastructure with a server that utilizes Windows virtual machines on Windows Server, so there won't be any additional costs.

I've never used the get window data function and I'm not sure if I'll need to use it more frequently. I've modified the flow, and now, when it opens a new Excel file to write the data, I save it with a unique name based on the invoice number, which will likely solve the problem.

Regarding starting the flow once a day, it's a great idea. I've checked the emails containing these PDF invoices; in most cases, they are sent at the same time, but unfortunately, there are exceptions.

However, how can I automatically start the flow once per day?

I found solutions on the net using Windows Scheduler, but I was wondering if there's an easier way to do it.

[MichaelAnnis]

Sounds like you got most of it worked out, so that’s good. 

windows scheduler is what I’ve seen most talked about here when they aren’t initiating from cloud flows, but I think even that requires the premium license. 

if you have a VM doing no other desktop flows, you could set it up on a loop to constantly run with a one hour wait between each loop, but that’s just a work around.  At the point, I’d personally just run it once a day. 

[Ronyn77]

1. You wrote : "if you have a VM doing no other desktop flows, you could set it up on a loop to constantly run with a one hour wait between each loop, but that’s just a work around.  At the point, I’d personally just run it once a day. How could you achieve to run it once per hour? Using windows scheduler or what? What do you mean exactly?

[MichaelAnnis · Accepted solution]

Some people just have flows running in the background all the time.  If it's not using your mouse and keyboard, it doesn't affect you, and you don't affect it.

Loop while 1=1 (a loop that will run continuously until you manually turn it off)

    'your flow

    'wait 1 hour (with a wait, instead of this loop running repeatedly (every second), we reduce the number of times it runs in a day)

End (loop)

But we can only have one of these running at a time.  It's a work around when you don't have connectors to trigger a flow.

Good luck!

[Ronyn77]

Thank you very much. Last question : if I want to run the flow one time per day at 6.00 a.m., what should I do?

[Agnius]

If you want it to run once a day at a specific time, you have the following options:

1. Run it manually (does not require a Premium license)
2. Proper schedule via cloud flows (requires a Premium license)
3. Proper schedule via Windows Task Scheduler (requires a Premium license)
4. Funky workaround schedule via Windows Task Scheduler, where you set up a shortcut key combination for your flow via PAD and then make Windows Task Scheduler call a PowerShell script to send the keystrokes (does not require a Premium license)

[Ronyn77]

Hello @Agnius,

If you've read my post, you'll understand that I'm not seeking a "premium" solution. Instead, I'm exploring workarounds that would allow me to automatically trigger the flow based on an event, after the flow has already been started manually. Following the previous suggestions, probably the best approach is to create an infinite loop and insert a condition within it that will trigger the subflow. I'm uncertain if I could utilize the "wait for text on screen" (OCR) feature and capture the time windows.