VBA refresh query's

[EvilC]

Hey all,

I have a bunch of excel workbooks that import via power query other exel workbooks and present it as clean data.
Now i need to open all these books and refresh the query as i keep dropping new data into it.

What i have now does not seem to do the trick....

I also tried RefreshAll when opening the files...

Public Sub refreshININ()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim con As WorkbookConnection
    Dim Cnam As String
    
    'the folder that contains the workbooks refresh
    Path = "\\MyPath\"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(Path)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With


    For Each file In folder.Files
        If Right(file.Name, 4) = "xlsx" Or Right(file.Name, 3) = "xls" Then
            Workbooks.Open Path & file.Name
                For Each con In ActiveWorkbook.Connections
                    If Left(con.Name, 8) = "Query - " Then
                    Cname = con.Name
                        With ActiveWorkbook.Connections(Cname).OLEDBConnection
                        .BackgroundQuery = False
                        .Refresh
                        End With
            ActiveWorkbook.Save
            ActiveWorkbook.Close True
                      End If
                Next con
                  
        End If
        Next file


        With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

 

[EvilC]

Any VBA Guru's around

 

[sandy666]

instead of vba you can try Connections - Properties - [Refresh control] Refresh data when opening the file

 

[EvilC]

instead of vba you can try Connections - Properties - [Refresh control] Refresh data when opening the file

Hey Sandy666, thanks for your reply,

That will work, but as i have a bunch of files this is clearly not the most efficient way. These files hold a table that are then imported into the needed reports, so they should not be used or opened. Even if i code all the files to open, wait and then close, as the data grows the query refresh time grows with it..

 

[sandy666]

sure, maybe it will help Refresh closed workbooks

 

[EvilC]

Thanks, but already been there. This will not update query's.

 

[sandy666]

So you need to wait for someone with a good vba skill

have a nice day

 

[John_w]

That code should work. Does it get to the Refresh line? Set a breakpoint there.

 

[EvilC]

Hey John,

Thanks for your reply,

Code runs without any error, just there is no refresh of data in the files...

I´m running some tests now, will post the outcome later.