Attribute VB_Name = "WeeklyReport"
Option Explicit

' One button: refresh the queries, then the pivots, stamp the time, save a PDF.
' From botlery.com/blog/automate-weekly-excel-report.html
' Excel for Windows. Save the workbook as .xlsm.

Private Const REPORT_SHEET As String = "Report"   ' the sheet with your pivots and charts
Private Const STAMP_CELL As String = "B2"         ' where "Refreshed ..." is written

Public Sub RefreshWeeklyReport()
    Dim cn As WorkbookConnection
    Dim pc As PivotCache
    Dim pdfPath As String

    On Error GoTo Failed
    Application.ScreenUpdating = False
    Application.StatusBar = "Refreshing the weekly report..."

    ' Make each query finish before the next step. With background refresh on,
    ' RefreshAll returns at once and the pivots would refresh on last week's data.
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeOLEDB Then cn.OLEDBConnection.BackgroundQuery = False
    Next cn
    ThisWorkbook.RefreshAll

    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc

    With ThisWorkbook.Worksheets(REPORT_SHEET)
        .Range(STAMP_CELL).Value = "Refreshed " & Format(Now, "yyyy-mm-dd hh:nn")
        pdfPath = OutputFolder() & "Weekly report " & Format(Date, "yyyy-mm-dd") & ".pdf"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, _
            Quality:=xlQualityStandard, OpenAfterPublish:=False
    End With

    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "Report refreshed and saved as:" & vbNewLine & pdfPath, vbInformation, "Weekly report"
    Exit Sub

Failed:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "The refresh stopped: " & Err.Description, vbExclamation, "Weekly report"
End Sub

' The workbook's own folder, or Documents when the workbook is opened from
' OneDrive or SharePoint (its Path is then a web address, not a folder).
Private Function OutputFolder() As String
    Dim p As String
    p = ThisWorkbook.Path
    If p = "" Or LCase$(Left$(p, 4)) = "http" Then p = Environ$("USERPROFILE") & "\Documents"
    OutputFolder = p & Application.PathSeparator
End Function