7 Replies Latest reply: Aug 29, 2016 8:10 AM by Matheus Colares RSS

    Macro export to excel in Qlikview Server

    Matheus Colares

      Hi folks.

      I will ask a question which the answer i know, but just to make sure, let's go.

      I created a vision to my prevention loss department and bacause complexity i did mutiples chart diferent and now they want export all chart to excel in once time in the same sheet....OK...I did this using Macro...Like this:

      Sub Exportar_Objetos_Excel
      
        Set XLApp = CreateObject("Excel.Application")
        Set XLDoc = XLApp.Workbooks.Add
        Set XLSheet = XLDoc.Worksheets(1)
        XLApp.Visible = True
      
        XLApp.Cells.Select
           XLApp.ActiveWindow.DisplayGridlines = False
        XLApp.Range("A5").Select
      
        ActiveDocument.GetSheetObject("CH01").CopyBitMapToClipboard
        XLSheet.Paste
      
        XLSheet.Range("B25").Select
        ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard True
        XLSheet.Paste
      
      End Sub
      
      

      Its work...buuut only in Qlikiview Desktop, with Office installed...But the user use qlikview in browser, you know, There were not  excel installed in my server, so my Macro not worked...=/
      Buuuuuut, in Qlikview server there is a option "SEND TO EXCEL", and this f**** work in browser, why?

      Summing up, i need create a button in qlikview, with macro which does the same "send to excel", but send mutiples chart once time, but not usign "CreateObject("Excel.Application")" , because this not work on server...

       

      If not exist solution, I understand...but if someone has passed to a situation similar, and resolved...coment!!!

      I'll be very thankfull.

      Thanks for your time.

      "sorry by my english, i'm training XD"

        • Re: Macro export to excel in Qlikview Server
          Avinash R

          have you enabled the Allow system access in the server

           

          Try like this

          In access point

          Ctrl+Shift+M> Click on Enable System access

          • Re: Macro export to excel in Qlikview Server
            Stan Bennett

            The 'Set XLApp = CreateObject("Excel.Application")' is actually opening up an instance of Excel. In fact, you should have seen Excel open up on your desktop when you ran it. Especially since you have the 'XLApp.Visible = True' in your code above. Since Excel is not installed on your server this is why it is not working, as the macro is trying to find and run the Excel application.

             

            The only other option to export to Excel in a macro is to do similar to the following:

             

            Sub ExportExcel

                    set obj = ActiveDocument.GetSheetObject("CH01")

                    obj.ExportBiff "C:\CH01.xls"

            End sub

             

            However, unfortunately this will not allow you to export multiple charts to the same Excel workbook.

             

            Since your requirement is to have multiple charts in the same Excel, the only option I see for you is to have Excel installed on your server. Then take a look at the following link for a great example of exporting multiple objects to the same Excel workbook: http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

             

            Hope this is helpful in confirming your suspicions.

             

            - Stan

            • Re: Macro export to excel in Qlikview Server
              Santiago Respane

              Hi,

              besides Avinash advice that is a must do in order for you macro to work, here's my version of the macro for exporting.

              The idea is to have one macro for all charts in the report.

               

              Macro code:

              sub ExportChart

                    set MyTable = ActiveDocument.GetSheetObject(ActiveDocument.Variables("v.ChartToExport").GetContent().String)

                    Const xlCenter = -4108

                    set XLApp = CreateObject("Excel.Application")

                    XLApp.Visible = True

                    set XLDoc = XLApp.Workbooks.Add

                  

                   'Some title setting

                    XLDoc.Sheets(1).name = MyTable.GetCaption.Name.v

                    set XLSheet = XLDoc.Worksheets(1)

                  

                   'Some sheet formatting

                    set XLSheet = XLDoc.Worksheets(1)

                    XLSheet.Columns.Interior.color = RGB(255,255,255)

                  

                   'Copy table

                    Mytable.CopyTableToClipboard true

                    XLSheet.Paste XLSheet.Range("A1")

                  

                    XLSheet.Columns("A:Z").EntireColumn.AutoFit

                    XLSheet.Columns("A:Z").VerticalAlignment = xlCenter

              End Sub

               

              In your case for exporting multiple charts, you can add a button, you add two actions in pairs for every object to export, as follows:

              1. Object One
                1. External -> Set Variable -> v.ChartToExport = MyTableID1
                2. External -> Run MAcro -> ExportChart
              2. Object Two
                1. External -> Set Variable -> v.ChartToExport = MyTableID2
                2. External -> Run MAcro -> ExportChart
              3. Object 3

               

              This will generate one xls file for each object.

              Please let me know if this helps.

              Kind regards,

              • Re: Macro export to excel in Qlikview Server
                Matheus Colares

                Thank you guys, but as Stan said, don't have any way to do that, if the Excel not installed in the server, unfortunately.

                Only way to do that is export do .txt or .csv.

                Thank you Avinash, Santiago and Stan...I tested all code, and all worked, but to qlikview desktop,like i said.

                But thanks anyway...Until next time.