Macro to loop through various filter combinations and export results of a pivot to excel workbook

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a working VB script to loop a pivot (CH139) through selected values on ONE field (DLR_NBR_LRD) and export it to an excel workbook.

Below is my code:

SUB AdHocExport
confirmation = MSGBOX ("Ad hoc Excel export has been initiated." & vbCrLf & "Do you wish to continue?"& vbCrLf &"", 36, "Export     Confirmation")
            IF confirmation = 7 THEN
                  EXIT SUB
            END IF
DIM xlApp
DIM xlBook
DIM xlSheet
DIM strSheetName
DIM var
DIM fname
SET f = ActiveDocument.Variables("vfname")
     fname = f.GetContent.STRING
SET v = ActiveDocument.Variables("vMacroChartId")
         var = v.GetContent.STRING
SET xlApp = CREATEOBJECT("Excel.Application")
         xlApp.Visible = TRUE
SET xlBook = xlApp.Workbooks.Add
SET xlSheet = xlBook.Worksheets("Sheet1")
         ActiveDocument.Fields(fname).Clear
SET Doc = ActiveDocument
SET Field = Doc.Fields(fname).GetPossibleValues
FOR i=0 to Field.Count-1
         Doc.Fields(fname).Clear
         Doc.FIelds(fname).SELECT Field.Item(i).Text
         Doc.GetApplication.WaitForIdle
         Doc.GetSheetObject(var).CopyTableToClipBoard TRUE
         xlApp.ActiveSheet.Paste
         xlSheet.Cells.EntireColumn.AutoFit
         xlSheet.Cells.EntireRow.AutoFit
         strSheetName = Field.Item(i).Text
         xlApp.ActiveSheet.Name = strSheetName
         IF(i<Field.Count-1)THEN
                IF(i>=2)THEN
                     xlApp.ActiveWorkbook.Worksheets.Add
               END IF
                IF(i<2) THEN
                     xlApp.Worksheets(xlApp.ActiveSheet.Index +1).SELECT
               END IF
       END IF
NEXT
Doc.Fields(fname).Clear
MSGBOX "Ad hoc Excel export is complete!",64,"Task Completion Notification"
END SUB

And I for selecting specific values in the field (DLR_NBR_LRD), I am creating a new variable in script and linking it to original variable by using

LOAD * INLINE [
DLR_NBR_LRD_KK, DEALER_NBR_LRD
29136, 29136
6723, 6723
19731, 19731
];

The vfname variable I am using in the VB script is assigned with this DLR_NBR_LRD_KK variable and the vMacroChartId is assigned with the pivot table ID.

New Requirement: need to use the above code to scale the variable selection to not just one variable but combination of several variables. I need to be able to loop the same object CH139 through the selections of:

DLR_NBR_LRD -> 29136, GRADE-> A1

DLR_NBR_LRD -> 29136, GRADE-> D1

DLR_NBR_LRD -> 29136, GRADE-> A1, COUNT-> 0

DLR_NBR_LRD -> 29136, GRADE-> A1, COUNT-> <=1 (0,1)

topic Macro to loop through various filter combinations and export results of a pivot to excel workbook in QlikView

Macro to loop through various filter combinations and export results of a pivot to excel workbook