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


          you can see this interesting document:




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

          I hope it can help you!


            • 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.




                    • 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)




                          XLSheet2.Paste XLSheet2.Range("A1")


                          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?