topic Re: VBA to Create PivotTable in Nprinting in Qlik NPrinting

VBA to Create PivotTable in Nprinting

djbloiss — Fri, 19 Jan 2018 15:49:29 GMT

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 — Sun, 21 Jan 2018 04:27:39 GMT

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

Re: VBA to Create PivotTable in Nprinting

Ruggero_Piccoli — Mon, 22 Jan 2018 09:55:33 GMT

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

djbloiss — Mon, 22 Jan 2018 16:17:51 GMT

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

djbloiss — Mon, 22 Jan 2018 16:18:12 GMT

Lech,

I figured out the code.

Re: VBA to Create PivotTable in Nprinting

djbloiss — Mon, 22 Jan 2018 16:18:52 GMT

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

Re: VBA to Create PivotTable in Nprinting

Ruggero_Piccoli — Tue, 23 Jan 2018 08:33:55 GMT

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

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