VBA procedure to pen an Excel workbook and refresh all datas in the QueryTables and PivotTable objects

bouffon69 (Sylvain Le Courtois) — Fri, 07 Sep 2007 07:44:29 GMT

// description of your code here
// Can also use the Workbook Open event ( Private Sub Workbook_Open() )



Sub Auto_Open()

Application.DisplayAlerts = False

    ChDir "T:\EXPLOIT\TSMENV\EXCEL\politiques"

    Workbooks.Open Filename:="t:\EXPLOIT\TSMENV\EXCEL\politiques\politiques.xls", _

        UpdateLinks:=3

    For i = 1 To ActiveWorkbook.PivotCaches.Count

        ActiveWorkbook.PivotCaches(i).RefreshOnFileOpen = False

    Next

    For i = 1 To ActiveWorkbook.Sheets.Count

        For j = 1 To ActiveWorkbook.Sheets(i).QueryTables.Count

            ActiveWorkbook.Sheets(i).QueryTables(j).RefreshOnFileOpen = False

        Next

    Next

    ActiveWorkbook.RefreshAll

    ActiveWorkbook.RefreshAll

    ActiveWorkbook.RefreshAll

    ActiveWorkbook.Save

    ActiveWindow.Close

    Application.Quit

End Sub

DZone Snippets: refresh code

VBA procedure to pen an Excel workbook and refresh all datas in the QueryTables and PivotTable objects