VBA refresh all not working

Help
, ,
1

Hi! I create 3 modules (Refresh_data, Clear_filter, Filter_data) in VBA. Then I use 3 ‘Execute Macro’ to run the 3 Macros in sequence. Clear and filter running good. But the ‘Refresh_data’ seems never run. The ‘Refresh_data’ is linked with a database query. The query takes around 40 seconds. This Macro can run in the Excel. I just don’t know why it cannot be executed in UiPath. Thank you for your help!

Sub Refresh_data
Application.ActiveWorkbook.RefreshAll
End Sub

Sub Clear_filter()
On Error Resume Next
Sheet1.ShowAllData
On Error GoTo 0
End Sub

Sub Filter_data()
Dim Raw_data As Worksheet
Dim Filter_criteria As Worksheet
Set Raw_data = ThisWorkbook.Sheets(“Sheet1”)
Set Filter_criteria = ThisWorkbook.Sheets(“Sheet2”)
Raw_data.AutoFilterMode = False
Dim PartType As String
Dim WetStep As String
NameA = Filter_criteria.Range(“A” & 1)
NameB = Filter_criteria.Range(“A” & 2)
With Raw_data.Range(“A1”)
.AutoFilter Field:=6, Criteria1:=25
.AutoFilter Field:=20, Criteria1:=“=”
.AutoFilter Field:=2, Criteria1:=“=" & NameA & "”
.AutoFilter Field:=15, Criteria1:=NameB
.AutoFilter Field:=7, Criteria1:=“Waiting”
End With
End Sub

2

Hi @RML

Welcome to UiPath community buddy

i think you are doing for sheet1 and sheet2
may be you can try to mention them in specific like this
—This would refresh the specific workbook
Sub Workbook_RefreshAll()
Workbooks(“Sample.xls”).RefreshAll
End Sub
—This would refresh the second workbook.
Sub Workbook_RefreshAll2()
Workbooks(2).RefreshAll
End Sub

Hope this would help you
Kindly revert for any clarifications
Cheers @RML

3

I tried ‘Workbooks(2).RefreshAll’ but VBA report run-time error ‘9’. I have 2 worksheets. The 1st one has Query. The second one only has the input panel and Macro control button. May I know how I can resolve it? Thank you.

4

Try with this buddy @RML

5

ActiveWorkBook is always to be avoided. :-1:

Try something similar to this.

Option Explicit

Sub RefreshAllOpenWorkBooksWithConnections()
    
    Dim xlWorkBook As Excel.Workbook

    For Each xlWorkBook In Excel.Workbooks
        Debug.Print xlWorkBook.Name & ",Connections Count = " & xlWorkBook.Connections.Count
        If xlWorkBook.Connections.Count >= 1 Then
            xlWorkBook.RefreshAll
        End If
    Next

End Sub
6

Option Explicit

Sub RefreshAllOpenWorkBooksWithConnections()

Dim xlWorkBook As Excel.Workbook
Set xlWorkBook = ThisWorkbook
For Each xlWorkBook In Excel.Workbooks
    Debug.Print xlWorkBook.Name & ",Connections Count = " & xlWorkBook.Connections.Count
    If xlWorkBook.Connections.Count >= 1 Then
        xlWorkBook.RefreshAll
    End If
Next

End Sub

I replace with the above code. But got ‘Compile error: Variable not defined’.
May I ask how should I fix it? Thank you!

7

It’s pretty standard code. What version of Excel is it running against.

How many Excel Instances are open at same time ?

8

My Excel version is 365 MSO (16.0.10730.20264) 32-bit. And I only have 1 instance

9

The bug has been fixed. But UiPath still cannot invoke the refresh data query. May I know what else I could try to fix it? Thank you