11 Replies Latest reply: Jun 2, 2017 8:27 AM by Lorna Louw RSS

    Excel behaving oddly in report

    Lorna Louw

      hello guys

       

      I create an Excel report using Nprinting and email it as an attachment to some of our users.

       

      if I open this spreadsheet directly from the email, the figures are totally off.

       

      if I save it somewhere on my pc, manually open up Excel, and then File->Open AND enable editing, the figures come through right.

       

      Any idea what this could be?

       

      Thanks

        • Re: Excel behaving oddly in report
          Lech Miszkiewicz

          Do you have "vanila office installed" or with some plugins? Often companies are installing bunch of add-ons for version control of their XLS files etc.. then this may be a case.

           

          How about if you preview this document on your phone, or tablet?

           

          cheers

          Lech

           

          btw - which version of NPrinitng are you using? 16 or 17,

          Can you creenshot your template? are there any macros / formulas in your template? It is possible that file has to be processed before showing right numbers if it was designed in this way.

            • Re: Excel behaving oddly in report
              Lorna Louw

              Hello Lech,

               

              When I preview the doc on my phone, the figures are also skewed.

               

              We’re on Nprinting 16, I’m including a copy of the template.

               

              Regards

              Lorna

                • Re: Excel behaving oddly in report
                  Lech Miszkiewicz

                  Hi Lorna,

                   

                  Ok, thank you for this template.

                   

                  Now the questions:

                  • are numbers in ALL sheets skewed?
                    • SSCC - GROUPED DETAIL
                    • CH 57 Data
                    • SSCC - GROUPED DETAIL
                    • Overview Data
                  • In above mentioned sheets i would expect to have correct numbers
                  • I would only expect issues with following sheets as they are Pivot Tables and need to be refreshed prior to viewing.
                    • SSCC CONSOLIDATED
                    • Overview

                   

                   

                  cheers

                  Lech

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

                  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: Excel behaving oddly in report
                      Lorna Louw

                      You are 100% spot on – the skewed figures come from SSCC Consolidated and the Overview tab.

                       

                      What puzzles me – I have ticket the option for both of these pivots to refresh data..

                        • Re: Excel behaving oddly in report
                          Lech Miszkiewicz

                          Hi Lorna,

                           

                          Maybe you can implement solution with Excel spreadsheet with macro in it, which would force refresh pivot table prior saving and sending file?

                           

                          See instructions how to do this here:

                          Nprinting Pivot table not shown in pdf report

                           

                           

                          cheers

                          Lech

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

                          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: Excel behaving oddly in report
                              Lorna Louw

                              Thank you I will have a look at it

                              • Re: Excel behaving oddly in report
                                Lorna Louw

                                Hello,

                                 

                                I’ve checked out your suggested post. Understand it totally.

                                 

                                Just one question – I’ve got multiple objects on the one report that I’m creating. So would I still add this bit of code to my existing template ?

                                 

                                thanks

                                  • Re: Excel behaving oddly in report
                                    Lech Miszkiewicz

                                    Hi,

                                     

                                    Just one question – I’ve got multiple objects on the one report that I’m creating. So would I still add this bit of code to my existing template ?

                                    What do you mean? Multiple PivotTables? - because macro only needs to refresh them

                                     

                                    So:

                                    1. first of all you need to change your file from XLSX to XLSM (in order to get macros in Excel)
                                    2. once you create your template in XLSM file - you can use file i have attached (this will look exactly the same like the one you have sent me) you have to edit macro i have attached in order to refresh all PivotTables in your file
                                    3. I do not think you can edit macro using NPrinting designer so just use normal MSExcel to open your XLSM template, hit ALT+F11 (this should open VB script editor). There you can modify a code which may look like this:

                                    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

                                        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 'You may need to change the pivot table reference

                                        ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh 'You may need to change the pivot table reference

                                    End Sub


                                    you need to check  and list all Pivot tables which need to be refreshed (as an example I have put "PivotTable1" and "PivotTable2"

                                     

                                    does it make sense?

                                     

                                    cheers

                                    Lech

                        • Re: Excel behaving oddly in report
                          Andy Weir

                          I noticed something similar with one of our customers showing me a reort in this case a powerpoint on his IPhone.

                           

                          The alignment of text in some cases was scewed.

                           

                          The issue was resolved by him not looking at the file through his email client quick view which it tries to do to be helpful but rather opening it up in the native application to view it.  In this case powerpoint.

                           

                          Not an issue with NPrinting as such in this case.

                           

                          Regards

                           

                           

                          Andy