VBA Code Sample - Refresh Pivots

This function utilizes another function <GetAllfilesInDir> to return an array of the Excel files to refresh. You will need to set a reference to Micrososoft Excel in the VBA editor. You need to pass the directory path to this function. This function returns nothing.

    Function MyRefresh(byval myFilePath as string))
    Dim AppXL As Excel.Application, wbXL As Excel.Workbook, ShtXL As Excel.Worksheet
    Dim pc As Excel.PivotCache
    Dim myFile, mySQLString, myYear, myErrorCode As String
    Dim blnExcelRunning As Boolean
    Dim td As DAO.TableDef
    Dim tds As DAO.TableDefs
    Dim fld As DAO.Field
    Dim strTable, strDirPath, myMonth1, myMonth2 As String
    Dim Cnt, i, lngI As Integer
    Dim varFileArray As Variant
    i = 0

    varFileArray = GetAllFilesInDir(myFilePath)


     Set AppXL = CreateObject("Excel.Application")
                On Error Resume Next
                blnExcelRunning = False
                Set AppXL = GetObject(, "Excel.Application") 'look for a running copy of Excel
                If Err.Number <> 0 Then 'If Excel is not running then
                    Set AppXL = CreateObject("Excel.Application") 'run it
                    blnExcelRunning = True
                End If
                Err.Clear   ' Clear Err object in case error occurred.
                Debug.Print "i - " & i
                For i = 0 To UBound(varFileArray)
                    On Error Resume Next
                    myFile = myFilePath & varFileArray(i)
                    Set wbXL = AppXL.Workbooks.Open(myFile)
                    Set wbXL = AppXL.ActiveWorkbook

                    For Each pc In wbXL.PivotCaches
                    AppXL.Workbooks(varFileArray(i)).Close False
                    wbXL = Nothing
                On Error GoTo 0 'Resume normal error processing
End Function