Блог

В макросе я пытаюсь подсчитать различные значения на основе 2 критериев. Раньше я использовал формулу во внешнем отчете для добавления counts =IFERROR(СТРОКИ(УНИКАЛЬНЫЕ(ФИЛЬТР(. Это работает для большинства поставщиков, но когда исходные данные, из которых он рассчитывает, становятся слишком большими для других поставщиков, он выдает ошибки и показывает только 1 или 0.

Я переключился на попытку добавить таблицу необработанных данных в сводную таблицу и добавить ее в модель данных в той же книге, что и таблица необработанных данных, чтобы я мог легко получить отдельный столбец count, а затем использовать GETPIVOTDATA для добавления счетчиков к строкам во внешнем отчете. Все это является частью более крупного макроса. Это отлично работает у некоторых поставщиков, у других ошибка при создании подключения к модели данных. «Ошибка 1004 во время выполнения: проблема с моделью данных не позволяет Microsoft Excel открыть эту книгу. Попробуйте перезапустить Microsoft Excel. «

Я пытался очистить соединения перед созданием нового соединения и новой сводной таблицы, но я все еще получаю ошибку. Когда я останавливаю макрос, чтобы протестировать добавление сводной таблицы power и добавление в модель вручную, все мои параметры PowerPivot отображаются серым цветом, а также возможность добавления его в модель данных при попытке добавить его с помощью обычной опции сводной таблицы.

Я впервые использую PowerPivot в макросе, но, похоже, я не могу найти более простое решение для получения различных значений при использовании большого набора данных без зависания или указания только «1» или «0».

Текущий код:

     Dim wsPivotModel As Worksheet
    Dim SDCPivotCache As PivotCache
    Dim PvtSDCmodel As PivotTable
    Dim SDCconnection As WorkbookConnection
    Set wsPivotModel = MainWB.Sheets.Add
    
    ' delete existing internal connections if necessary
    For Each SDCconnection In MainWB.Connections
        If SDCconnection.Type = xlConnectionTypeWORKSHEET Then SDCconnection.Delete
    Next SDCconnection
    
    'Create new connection
    Set SDCconnection = MainWB.Connections.Add2("WorksheetConnection_" amp; MainWB.Name amp; "!Table_SDCdata", "", _
        "WORKSHEET;" amp; DataPath amp; "" amp; MainWB.Name, MainWB.Name amp; "!Table_SDCdata", 7, True, False)
    
    'create and configure new pivotcache
    Set SDCPivotCache = MainWB.PivotCaches.Create(SourceType:=xlExternal, SourceData:=SDCconnection, Version:=6)
    With SDCPivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsNone
    End With
    
    'Create and configure new pivottable
    Set PvtSDCmodel = SDCPivotCache.CreatePivotTable(TableDestination:=wsPivotModel.Name amp; "!R1C1", TableName:="PvtSDCmodel", DefaultVersion:=6)


    With wsPivotModel.PivotTables("PvtSDCmodel")
        .RepeatAllLabels xlRepeatLabels

        With .CubeFields("[Table_SDCdata].[Region]")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .CubeFields("[Table_SDCdata].[Format]")
            .Orientation = xlRowField
            .Position = 2
        End With
        
        .CubeFields.GetMeasure "[Table_SDCdata].[Site]", xlCount, "Count of Site"
        .AddDataField .CubeFields("[Measures].[Count of Site]"), "Count of Site"
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        
        With .PivotFields("[Measures].[Count of Site]")
            .Caption = "Distinct Count of Site"
            .Function = xlDistinctCount
        End With
        
    End With
       
'Get store format count in province from SDC
    Dim ref As Variant
    Dim sFormula As String
    
    ref = "[" amp; MainWB.Name amp; "]" amp; wsPivotModel.Name
    sFormula = "=GETPIVOTDATA(""[Measures].[Distinct Count of Site]"",'" amp; ref amp; "'!$A$1,""[Table_SDCdata].[Region]"",""[Table_SDCdata].[Region].amp;[""amp;[@Region]amp;""]"",""[Table_SDCdata].[Format]"",""[Table_SDCdata].[Format].amp;[""amp;[@Format]amp;""]"")"
        
    With wb.Worksheets("Number of Stores Ranged").ListObjects("Table_Number_of_Stores_Ranged")
        With .ListColumns("Total Number of Stores : In Province").DataBodyRange
            .NumberFormat = "General"
            .Formula = sFormula
            .Value = .Value
        End With
               
  

Любые предложения о том, почему возникает эта ошибка, и после этого все мои параметры становятся серыми?
Или, если есть какие-либо другие предложения о том, как легко получить отчетное количество из большого объема данных без сбоев, если Power Pivot не будет работать (что должно)?