Refreshing Excel Workbook with UIPath

Help
,
1

Hi Guys,
I am trying to refresh an excel workbook but I am not able to do it without opening and passing refresh Hotkeys to it. I can’t open the workbook since this process fails if I have the Remote Desktop disconnected.
Is there a way I can refresh the workbook without opening or doing any UI Interactions?

Note: The excel is connected to SQL Server Database and I refresh it by clicking “Refresh All” in the data tab.

2

You can go through This thread :

if you know macro above link can help you.

and

3

@abchakraborty

Have an excel with the following macros code,

sub RefreshWorkbook(filePath As String)
       Workbooks(filePath).RefreshAll
End Sub

Then in UiPath use Execute Macros activity and pass the string below

"RefreshWorkbook(""filePath"")"

Instead of filePath here pass the path of excel that needs to be refrehsed.

This will work good even in case of citrix.

1 Like
4

Thanks @Ragu9060!
I made the changes you suggested and now I am getting this error:

image
image1430×521 47.3 KB

I am not sure what is causing it. Here is what I did with the flow:

image
image1063×714 17.6 KB

5

Hi @abchakraborty

In the execute macro you must mention the function name not the Excel file path

Thanks
Ashwin.S

6

Yes, refresh_wb is the the sub name I used.
In place of the filepath, I used the path of the file I want to refresh.

image
image1150×190 5.02 KB

This is the macro I had in the refresh_wb1.xlsm file.

7

Hi @abchakraborty

Just give refresh_wb without file path

Thanks
Ashwin S

8

Hi @abchakraborty

Try giving the full path

Thanks
Ashwin.S

9

Try saving the excel file containing macro as macro enabled file. Then the file extension will be .xlsm.

In this case it should work. If not forward me your file and correct and send you back.

10

@AshwinS2

Be noted, we are passing file name as argument to the Macro function.

11

Refresh_wb1.zip (9.5 KB)

Here is the macro file. For the filepath, I am providing the filepath for the file I want to refresh.

12

Mock and send me file that you need to refresh, with out sample file i cannot test refresh.

You can remove any sensitive data in excel and replace it with dummy data.

13

Sure. I am uploading the zip folder with two files. The workbook_to_refresh.xlsx is the one to be refreshed and the MOCK_DATA.csv is the mock data connected to the workbook with Powerquery.

The aim is to refresh the powerquery connections without opening the workbook using VBA.
Please let me know if you need anything else

@Ragu9060

uipath mock.zip (17.5 KB)

14

@abchakraborty

Sorry if I am suggesting something out of discussion.

Why can’t you read the csv and write data to excel. You can make the excel as template and use write range to enter data into the required columns.

When you settle of macro you have to always make sure the macros are enabled in file and always make sure the macros file in present. The above solution will be much simpler.

15

@Ragu9060
The csv file I provided is actually sample file for powerquery. I sent it so that we can check if the macro is working fine.
I have about 10-11 SQL Server connections built in the original file to be refreshed and I transform the data with powerquery to get the relevant texts. Therefore, unfortunately, it would not be possible for me to use csv since csv does not support powerquery.

16

Guys, I have found the solution.
I created the vb file as usual with “Thisworkbook.refreshall” as the code in the sub.

After I ran the VBA script using the bot, I was getting the error that “this will cancel a pending data refresh”, which was causing a manual intervention from me every time.

Therefore, I went to query properties and disabled “Refresh Data in the Background” and the code works like a charm now.

Thanks a lot for your help, everyone!

6 Likes
17

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