PivotChart over multiple Documents returns 0 as sum when document is empty

i have an weird problem with multiple excel documents and pivot charts: i used this report document to get information over multiple excel sheets: http://blog.contextures.com/archives/2010/08/30/macro-creates-excel-pivot-table-from-multiple-files/ The problem is, if one of my files has actually no data, the result of my PivotChart is completly 0! I tried if with adapting the query with an

 `table" 开发者_StackOverflow& i & "` WHERE `table" & i & "`.`Stunden` != 0

or

 `table" & i & "` WHERE `table" & i & "`.`Stunden` IS NOT NULL

at the end, but same result. As workarround it worked to 0 all rows in the data sheets, or add a pseudofield at the beginning which has 0 as value.

is there any posibility to check with the query if the whole table is emtpy and return a value which excel can understand?

thanks!

reox,

This is pretty ugly, but I think it works. It creates a test pivotcache and pivottable for each workbook and only adds the workbook to the SQL if there's at least on record in the test pivot cache. You could do something shorter with DAO.

Sub MergeFiles()
    Dim PT As Excel.PivotTable
    Dim PC As Excel.PivotCache
    Dim strConTest As String
    Dim pcTest As Excel.PivotCache
    Dim ptTest As Excel.PivotTable
    Dim boolSheetHasRecords As Boolean
    Dim strSQLTest As String
    Dim arrFiles As Variant
    Dim strSheet As String
    Dim strPath As String
    Dim strSQL As String
    Dim strCon As String
    Dim rng As Range
    Dim i As Long

    strPath = CurDir
    ChDirNet ThisWorkbook.Path

    arrFiles = Application.GetOpenFilename("Excel Workbooks (*.xls), *.xls", , , , True)
    strSheet = "Sheet1"

    If Not IsArray(arrFiles) Then Exit Sub

    Application.ScreenUpdating = False

    If Val(Application.Version) > 11 Then DeleteConnections_12

    Set rng = ThisWorkbook.Sheets(1).Cells
    rng.Clear
    For i = 1 To UBound(arrFiles)
        strConTest = _
        "ODBC;" & _
        "DSN=Excel Files;" & _
                     "DBQ=" & arrFiles(i) & ";" & _
                     "DefaultDir=" & "" & ";" & _
                     "DriverId=790;" & _
                     "MaxBufferSize=2048;" & _
                     "PageTimeout=5"
        Set pcTest = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        strSQLTest = "SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
        With pcTest
            .Connection = strConTest
            .CommandType = xlCmdSql
            .CommandText = strSQLTest
            Set rng = ThisWorkbook.Sheets(1).Cells
            rng.Clear
            Set ptTest = .CreatePivotTable(TableDestination:=rng(6, 1))
            If pcTest.RecordCount > 0 Then
                boolSheetHasRecords = True
                Else
                boolSheetHasRecords = False
            End If
        End With
        Set ptTest = Nothing
        Set pcTest = Nothing
        If boolSheetHasRecords Then
            If strSQL = "" Then
                strSQL = "SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
            Else
                strSQL = strSQL & " UNION ALL SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
            End If
        End If
    Next i
    If strSQL <> "" Then
        strCon = _
        "ODBC;" & _
                 "DSN=Excel Files;" & _
                 "DBQ=" & arrFiles(1) & ";" & _
                 "DefaultDir=" & "" & ";" & _
                 "DriverId=790;" & _
                 "MaxBufferSize=2048;" & _
                 "PageTimeout=5"
        Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        With PC
            .Connection = strCon
            .CommandType = xlCmdSql
            .CommandText = strSQL
            Set rng = ThisWorkbook.Sheets(1).Cells
            rng.Clear
            Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))
        End With
        With PT
            With .PivotFields(1)                            'Rep
                .Orientation = xlRowField
                .Position = 1
            End With
            .AddDataField .PivotFields(8), "Sales", xlSum   'Total
            With .PivotFields(3)                            'Region
                .Orientation = xlPageField
                .Position = 1
            End With
            With .PivotFields(2)                            'Date
                .Orientation = xlColumnField
                .Position = 1
                .DataRange.Cells(1).Group _
                        Start:=True, _
                        End:=True, _
                        Periods:=Array(False, False, False, False, True, False, True)
            End With
        End With
    End If
    'Clean up
    Set PT = Nothing
    Set PC = Nothing

    ChDirNet strPath
    Application.ScreenUpdating = True
    End Sub