UI Path activity to delete Query Connection in Excel

Help Studio
, ,
1

I have an excel file that is connected to Analysis cubes and needs to be refreshed on a daily basis. This is not a problem. The requirement is also to make a copy of the Excel file after the data refresh and save it to an archive folder and remove the Query Connection that connects to the cube. I tried to use the Click activities to do it. I am able to go all the way to Delete query connection but the UI path activity fails when the modal popup comes up to confirm the deletion.

is there a way to get to the modal popup in an Excel with the click activity? Or any other Activity that can be used to delete?

image

Thanks in advance.

2

Hi @Ambi

Please use a hover activity to place your cursor on the Connection to get the pop up then use click activity to the following selector. This is selector for delete. Have the cursor move instant checked or try send windows messages


<html app='excel.exe' /> 
<webctrl aria-role='button' tag='DIV' innertext='DELETE' />

cheers

3

Hi Anil,
Thank you for the reply. I finally was able to delete query connections by using the InvokeVBA activity and using the following VBA script. Hopefully its useful for others.

Sub Remove()
Dim connection As WorkbookConnection
Dim query As WorkbookQuery
On Error Resume Next
For Each connection In ThisWorkbook.Connections
connection.Delete
Next
For Each query In ThisWorkbook.Queries
query.Delete
Next
End Sub

4

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