VBA refresh query's

EvilC

Board Regular
Joined
Jul 26, 2014
Messages
74
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...

Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
instead of vba you can try Connections - Properties - [Refresh control] Refresh data when opening the file
 

EvilC

Board Regular
Joined
Jul 26, 2014
Messages
74
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..
 

EvilC

Board Regular
Joined
Jul 26, 2014
Messages
74
Thanks, but already been there. This will not update query's.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

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

have a nice day
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,891
That code should work. Does it get to the Refresh line? Set a breakpoint there.
 

EvilC

Board Regular
Joined
Jul 26, 2014
Messages
74
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.
 
Last edited:

Forum statistics

Threads
1,136,315
Messages
5,675,038
Members
419,546
Latest member
RobWayCot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top