6 Replies Latest reply: Jan 23, 2018 3:33 AM by Ruggero Piccoli RSS

    VBA to Create PivotTable in Nprinting

    Daniel Blois

      I am creating an Add-in that will speed up creating Excel reports in Nprinting.  The first function I am creating it creating a PivotTable.  This is the Code I have created so far:

       

      Sub AddPivot()

       

          Dim ReportName As String

          Dim ReportSheet As Worksheet, DataSheet As Worksheet

          Dim WorkingBook As Workbook

       

          Set WorkingBook = ActiveWorkbook

          Set DataSheet = ActiveSheet

          frmReportName.Show

          ReportName = Replace(frmReportName.tbReportName.Value, " ", "")

       

          DataSheet.ListObjects.Add(xlSrcRange, DataSheet.Range(DataSheet.Cells(1, 1), DataSheet.Cells(2, fnFinalColumn(DataSheet))), , xlYes).Name = ReportName

          DataSheet.Range("A" & fnFinalRow(DataSheet) + 1).Value = "<deleterow>"

          Set ReportSheet = WorkingBook.Sheets.Add

          WorkingBook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ReportName, Version:=xlPivotTableVersion10).CreatePivotTable TableDestination:=ReportSheet.Range("A1"), _

              TableName:="Pivot" & ReportName, DefaultVersion:=xlPivotTableVersion10

          ReportSheet.Select

          ReportSheet.Name = ReportName

          With ReportSheet.PivotTables("Pivot" & ReportSheet).PivotCache

              .RefreshOnFileOpen = True

              .MissingItemsLimit = xlMissingItemsNone

          End With

       

      End Sub

       

      Function fnFinalRow(ByVal wsToTest As Worksheet) As Long

       

          With wsToTest

              fnLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

          End With

       

      End Function

       

      Function fnFinalColumn(ByVal wsToTest As Worksheet) As Long

       

          With wsToTest

              fnLastColumn = .Cells(1, .Columns.Count).End(xlUp).Row

          End With

       

      End Function

       

      Now, the code looks correct to me but I am receiving an error on this line:

       

      DataSheet.ListObjects.Add(xlSrcRange, DataSheet.Range(DataSheet.Cells(1, 1), DataSheet.Cells(2, fnFinalColumn(DataSheet))), , xlYes).Name = ReportName

       

      Here is the error I am receiving.  I have stepped through the code and even set watches for Datasheet and checked that ReportName has a value (although ReportName being empty couldn't create that error).

       

      What am I missing? is this something particular to Nprinting?

        • Re: VBA to Create PivotTable in Nprinting
          Lech Miszkiewicz

          What NPrinting version are you using? Can you attach your template?

          • Re: VBA to Create PivotTable in Nprinting
            Ruggero Piccoli

            Hi,

             

            Please note that:

            - Qlik NPrinting 16 supports the Office VBA macros in the meaning that you can save an .xlsm template with the VBA code. When Qlik NPrinting generates the report, it copies the VBA code from the template to the reports without executing the macros. The recipents must execute the macros by themselves or you can associate them with the opening event.

            - Qlik NPrinting 17 at the moment (November 2017) doesn't support Office macros. The VBA code will not be copied into the generated reports.

             

             

            Best Regards,

            Ruggero

            ---------------------------------------------

            When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

            • Re: VBA to Create PivotTable in Nprinting
              Daniel Blois

              Ruggero, thank you for that information.  Is there plans for NPrinting 17 to eventually support .xlsm files?

               

              However, in this case it doesn't effect me because I am not doing a .xlsm file.  The user will never get the code.  This is an add-in that I am creating that speeds up the creation of the templates and the end-user will never receive it.  Is this, still supported in NPrinting 17?

               

              by the way I figured out my code.

                • Re: VBA to Create PivotTable in Nprinting
                  Ruggero Piccoli

                  Hi,

                   

                  1 - at the moment I don0t know if and when it will be added

                  2 - I never tested, sorry. Please let us know the results of your tests. Thanks

                   

                   

                  Best Regards,

                  Ruggero

                  ---------------------------------------------

                  When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.