Hi All,
Can anyone guide how can we click refresh button in smart view enabled excel.
I have a Excel in sharedrive. Daily they will add a new file there. My task is i need to click on refresh button under smart view option on perticular sheets.(It’s connected to database in backend) Then i need to move that file to other folder.
I tried to record macro but it didn’t worked. Any suggestions to slove this issue.
While directly clicking the Smart View refresh button in UiPath might not be straightforward, here are two effective methods you can use to achieve your goal:
Method 1: Using Keyboard Shortcuts (Reliable and Efficient):
Identify Keyboard Shortcut: Open the Excel file with Smart View enabled. In the Smart View ribbon, locate the “Refresh” option or icon. Often, there’s a keyboard shortcut associated with it (e.g., Ctrl+Alt+R).
Use Type Into Activity: In your UiPath workflow, after opening the Excel file using the “Excel Application Scope” activity, use the “Type Into” activity.
Set Properties: In the “Type Into” activity properties:
Set the “Target” to the application window (typically “wnd app”).
Set the “SimulateType” property to True to mimic user keystrokes.
Send Keyboard Shortcut: Enter the identified keyboard shortcut (e.g., ^{Alt+R}). This will send the keystrokes to refresh the data in the specific sheets.
Method 2: Using VBA Macro (Flexible but Requires Maintenance):
Create VBA Macro: Open the Excel file and access the VBA editor (Alt+F11). Create a new macro (Insert > Module).
Write VBA Code: In the module, paste the following code, replacing "Sheet1" and "Sheet2" with the actual sheet names you want to refresh:
VBASub RefreshSmartViewSheets() Sheets("Sheet1").Select ActiveSheet.PivotTables(1).RefreshTable Sheets("Sheet2").Select ActiveSheet.PivotTables(1).RefreshTable End Sub
content_copy
Call VBA Macro: In your UiPath workflow, use the “Invoke VBA” activity.
Set Properties: In the “Invoke VBA” activity properties:
Set the “WorkbookPath” to the shared drive location of the Excel file.
Set the “MethodName” to the name of your VBA macro (e.g., RefreshSmartViewSheets).
Additional Considerations:
Error Handling: Implement error handling in your UiPath workflow to gracefully handle scenarios where the file might not be found, the macro fails, or the keyboard shortcut doesn’t work as expected.
File Moving: After refreshing, use the “Move File” activity to move the Excel file to the desired location on the shared drive.
Choosing the Right Method:
If consistency and reliability are crucial, using keyboard shortcuts is generally preferred.
If you need more flexibility in refreshing specific sheets or handling different Excel layouts, consider the VBA macro approach.