Power Query does not work with excel workbooks

Hello,
I have a problem that, when I try to use power query on excel workbooks created by robot, thhe is answer is always blank. I have to open the workbook and save again and after this it works normally. Does anyone have damo issue and can help with this?
Thank you very much for replies and ideas.

Hi @qwerty456

Welcome to our UiPath Forum! :slight_smile:

Could you specify how is your Excel file being saved? Is it using the Excel Application Scope or the Workbook activity?

Also, what is the version of your Excel activity package and of your Studio?

Lastly, I suppose last resort would be to write a small process that will open each file and save it via surface automation (without the Excel Application Scope).

The issue seems quite weird though, so please report back! :slight_smile:

Hello,
Excel file is saved without Excel Aplication Scope. Excel package version is 2.4.6884.25683 and I have Studio version 2018.4.1. (Enterprise Edition).

We made some more investigation in our team and it seems, that it depends on our internal tagging. (public, internal, protected etc…). :slight_smile:

We solved it same way as you suggest- I have created a library, that opens Excel using Open application activity, double click first visible cell and save it. I have tried also other solutions for opening Excel(with Excel Aplication scope, or start process…), but with these, the problem with power query was not solved.

Anyway thank you for your interest! :wink:

2 Likes

@loginerror and @qwerty456

How we can invoke the power query in uipath which is already build ? Also to create power query through bot do we need to do UI automation’s on excel or any activities are available?

Much appreciate the help.

I’m also interested in best practices for invoking a Power Query refresh. Currently, I’m using the following code, but at times I get error messages. To fix this temporarily, I’ve bypassed them for now which is not ideal. Below is the Excel Macro Refresh code I use to ensure the process finishes prior to moving on to the next UI Path code.

NOTE: Remove On error resume next as it turns off your error handling in the current function***

Sub Refresh_All_Data_Connections()
'Disable error handling
On Error Resume Next

  • For Each objConnection In ThisWorkbook.Connections*

  •    'Get current background-refresh value*
    
  •    bBackground = objConnection.OLEDBConnection.BackgroundQuery*
    
  •    'Temporarily disable background-refresh*
    
  •    objConnection.OLEDBConnection.BackgroundQuery = False*
    
  •    'Refresh this connection*
    
  •    objConnection.Refresh*
    
  •    'Set background-refresh value back to original value*
    
  •    objConnection.OLEDBConnection.BackgroundQuery = bBackground*
    
  • Next*

  • ’ MsgBox “Finished refreshing all data connections”*

End Sub