Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

djbloiss
New Contributor III

VBA to Create PivotTable in Nprinting

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?

Tags (2)
6 Replies
Lech_Miszkiewicz
Honored Contributor III

Re: VBA to Create PivotTable in Nprinting

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

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
Employee
Employee

Re: VBA to Create PivotTable in Nprinting

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.



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 with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
djbloiss
New Contributor III

Re: VBA to Create PivotTable in Nprinting

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.

djbloiss
New Contributor III

Re: VBA to Create PivotTable in Nprinting

Lech,

I figured out the code.

djbloiss
New Contributor III

Re: VBA to Create PivotTable in Nprinting

I accidentally replied to myself when I meant to reply to you; so my comment to you is below.

Employee
Employee

Re: VBA to Create PivotTable in Nprinting

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.



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 with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
Community Browser