Refreshing a data source in Excel

Help
1

Hi All,

First post, so please be gentle :slight_smile:

I’m having a bash at creating a PoC for our business, and I want to automate the refresh of several spreadsheets that are connected to a variety of data sources (ODBC and SQL mostly) and are currently manually refreshed several times per day.

I can’t seem to get this working reliably via the Desktop Recording method (once I’ve called the workbook to open, the process can’t find the Excel window that was selected as part of the recording process), and I can’t seem to find any functionality to refresh all data sources in the Excel package (or any others, for that matter).

Anyone got any ideas, please? Bear in mind that I’m very much a novice, and am working my way through the first modules of the Academy as we speak! :slight_smile:

Many thanks in advance

Dave

2

Hi Dave,

I think I have a similar issue. I have an excel in which some queries are imbedded (SQL). The query in excel reads some cells which will select the correct scope for the query. When I’m trying to refresh this query manually, excel will ask me to input my Username and Password. However when I’m triggering this via UiPath, excel never seems to ask me these inputs and therefore also never executes the query.

Did you find a solution for your problem?

Thanks in advance!

3

Have you guys thought bought creating a Macro that will refresh the data? You will just call macro command in uipath and assign the macro. For my Use Case, I created refresh macro and call the macro to refresh the data in the excel

4

Hi Birdi,

I tried executing the macro below. When I do it manually it works, but when I let the robot do it, the macro crashes on this line: ‘Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False’
Any idea why?

Full macro script:
Sub Execute_Queries()
’
’ Execute_Queries Macro
’
’ Keyboard Shortcut: Ctrl+q
’
Sheets(“data dump repricing”).Select
Range(“A1”).Select
Application.CommandBars(“Selection”).Visible = False
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets(“data dump liquidity”).Select
Range(“A1”).Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets(“data dump key rates”).Select
Range(“A1”).Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Calculate

End Sub

5

Hi All,

I solved it!

There’s a bug in Uipath, where the pop up screen requesting for Username and Password does not show up within a ‘Excel Application Scope’. Doing the same thing outside of an Excel Application Scope, does trigger the pop up.

6

Were you able to have Uipath robotically enter the username and password?
Bill