Refreshing the data in an Excel file

OpenRPA
1

I have a workflow that grabs data from an Excel file which has data sources in it that need refreshing before i get the data. I have tried various different approaches but none seem to allow me to open the Excel file and refresh the data. They all create a new read only instance of the file or pop up a save as file prompt. Is there a way to reliably refresh the data and save the file before i then use Read Cell or Read Range

2

I was talking with someone about this at a meeting a few weeks ago, but never got around to looking at it. You are referring to an Excel sheet that uses PowerPivot/PowerQuery to get data from external sources, right? If that is the case, there is no activity to do it, and Office is incredibly hard to automate using UI automation, since it keeps adding and removing panes when you move it or change stuff.
In that meeting i mentioned if he could not simply use auto refresh ( its mention here and here )
is that a solution ?
If not, then there seem to be a c# snippet here that shows how it could be done using Invoke Code … if that works, then it would be easy for me to also add an activity to OpenRPA that does the same.

4

Hi @Alex_Langley,

I had exactly the same taks a few weeks back.
What I did for my worklow was this:

  1. Get Element activity (for Windows UI)
  2. Within that Get Element activity use the “Open selector” function to select the top level pane of that excel file you need to be refreshed, then in the same window of the selector you need to click on the “Pane” which highlightes that top-level pane of the file, right-click on it and click “Select Element” and hit Ok
  3. Now use the Record function and click the Data ribbon and Refresh All button, hit Esc to stop Recording.
  4. Depending on the complexity and time it takes for your file to get refreshed I would definately also add a “Delay” activity" after that just to make sure the file gets refreshed before we move on to the next step of the workflow.
  5. After that what I would probably do is again add another Get Element activity and Record the Save file thing.
    I think this is it. Please let me know if this works out for you.

A more clean way to do this thing would be to use the keyboard shortcut keys to do the Refresh All and Save file. And by the way, below Allan mentioned that panes in Excel change, well shortcut keys definetely do not change. I haven’t tried it yet, but if you would like to give that a go the way you make the bot run the keys is you do the Record function and you just push the keys yourself and hit Esc when you are done. Or you can have an Excel Macro which refreshes the file and saves it and just have openRPA run that macro via :Run Excel Macro" activity (haven’t tried it yet myself though).

1 Like
5

@Alex_Langley,
Also I’m really curious to know if you got the Read Range activity to work fine for you. Because I am having some problems in that area.

6

@Anatoly What is the problem with read range ? What happens ( or does not happen ) ? is the problem only related to excel files that uses power query ?

7

Hi @Allan_Zimmermann, thank you for your question.
I will get back to you asap once I have reviewed the video material to my post you sent me on the Read Range subject.

8

Hi there. Thanks for all the help. I was actually just getting data into my file from other Excel/CSV files. I find it simpler to maintain the bot configuration if i keep some of the format processing etc in an excel file. In the end i got it working by using Start Process on Excel, Delaying for a while then Typing Text Ctrl S. Then waiting again, then using Close Application rather than using Close Workbook, and then waiting again. Anything other than that very strict controlled process using Read Cell or Read Range always opened a new instance of the file in read only which then prompts to Save As.

1 Like
9

As mentioned above im not using Power Queries but I imagine they have a similar issue. If you open the file then save and close it, all with time delays. Then perform your read cell or read range it worked ok, the data will have updated in your main document.

10

Hi @Allan_Zimmermann ,
i was able to resolve the issues I faced using the video material you shared. Thank you for that.
The only question I have left is about the Read range what is te example syntax for “lastUsedRow”. If you have an example please kindly share with me.

11

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.