4 Replies Latest reply: Feb 18, 2015 12:41 PM by Pedro Pereira RSS

    Macro to export to excel

      Hi everyone,

       

      I want to export a chart table and a pivot chart from Qlikview to an excel file. I already search to see if I can adapt some code to export my data to the excel.

       

      I want to edit the name of the sheets. I have seen useful code on "VB Macro to Loop Field (Dynamic) and Export Chart (Dynamic) to Excel & Name Sheets by Field Value" the problem is that I dont understand the code and therefore it is hard for me to implement on my computer.

       

      The code I am using is on the .txt

       

      I dont even not if I am running correctly the macro. I define the button then I select the option "run macro" and then put the macro name "exportToExcel" on the edit module is where I have the code of the .txt file.

       

      Any help will be useful since I stuck, and every insight will be very much appreciated.

       

      Thanks in advance.

        • Re: Macro to export to excel
          Elena Prandoni

          Hi,

          you can see this interesting document:

           

          http://community.qlik.com/docs/DOC-3889

           

          There are some macro for export in excel where you can set name of sheet.

          I hope it can help you!

          Elena

            • Re: Macro to export to excel

              Can you put the file into a txt?

               

              I am afraid I can not open the file, since I still dont have a licensed account.

                • Re: Macro to export to excel
                  Elena Prandoni

                  Ok, in attachment the macro explained in the post I mentioned above.

                   

                  BR,

                  Elena

                    • Re: Macro to export to excel

                      Thanks for the information. I couldn't do it no your way, but I figured out how to do it in another way.

                       

                      My code:

                       

                      FUNCTION PasteObject(SolutionObject, ModelWorkbook, ModelSheetID, SheetPosition)

                         

                          set MyTable = ActiveDocument.GetSheetObject(SolutionObject) 'Change CH01 to the object you want to export to Excel (Object Properties > General tab > far right)

                          set XLSheet = ModelWorkbook.Worksheets(ModelSheetID) 'Select sheet where data should be pasted

                          Mytable.CopyTableToClipboard true 'Copy data to Clipboard

                          XLSheet.Paste XLSheet.Range(SheetPosition) 'Paste data starting at A1

                         

                      END FUNCTION

                       

                      SUB exportToExcel

                         

                          Dim docProp

                          set docProp = ActiveDocument.GetProperties

                          Dim tmpFile

                          tmpFile = docProp.MyWorkingDirectory

                         

                          set XLApp = CreateObject("Excel.Application")

                          XLApp.Visible = true 'you can make it false, if you want to make it in the background

                          set XLDoc = XLApp.Workbooks.Open(tmpFile & "\TestFileToSave.xlsx")

                         

                          PasteObject "CH02", XLDoc, 3, "A1"

                          PasteObject "CH05", XLDoc, 2, "A1"

                       

                         

                          ActiveDocument.ActivateSheetByID "SH01"

                           set XLSheet2 = XLDoc.Worksheets(1)

                          

                          

                          ActiveDocument.GetSheetObject("CH08").CopyBitmapToClipboard()

                          XLSheet2.Paste XLSheet2.Range("A1")

                          ActiveDocument.GetSheetObject("CH06").CopyBitmapToClipboard()

                          XLSheet2.Paste XLSheet2.Range("A28")

                         

                      END SUB

                       

                      However my images (objects CH06 and CH08) have poor quality. I tried to use PasteSpecial but it gives some kind of error.

                       

                      I try to out like that:

                       

                      XLSheet2.PasteSpecial(3) XLSheet2.Range("A1")


                      But it says "Expected end of statement". How can I do it?