8 Replies Latest reply: Jan 13, 2014 1:59 PM by Tiago Ferneda RSS

    Export to XLSX

      Hi,

       

      I have the following routine:

       

      Sub export

      set obj = ActiveDocument.GetSheetobject("TB01")

      obj.exportbiff ("C:\Test.xlsx")

      end sub

       

      I then call this in a button.

       

      this exports the file correctly however I get an error when opening the file. excel says it is corrupted. Any ideas why?

       

      I need a simple routine to export a table to XLSX

        • Re: Export to XLSX
          Gysbert Wassenaar

          See this post: Re: QV to excel 2010?

          • Re: Export to XLSX

            I think that use the create object is the better option. Use the command CreateObject("Excel.Application"). Take a look at this related post below pls.

             

            http://community.qlik.com/thread/80672

             

            Best Regards.

             

            Tiago

              • Re: Export to XLSX

                Thanks for this.

                 

                Is there a simpler version of this code?

                Thanks,


                  • Re: Re: Export to XLSX

                    The code below can be used to export more than one object to a excel file, take a look.

                     

                    sub launchXL

                    set oXL=CreateObject("Excel.Application")

                    oXL.visible=True 

                    oXL.Workbooks.Add

                    aSheetObj=Array("TB01","CH01")

                    for i=0 to UBound(aSheetObj)

                      oXL.Sheets.Add 

                      Set oSH = oXL.ActiveSheet

                    oSH.Range("A1").Select   

                    Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))

                      obj.CopyTableToClipboard True

                      oSH.Paste

                      sCaption=obj.GetCaption.Name.v

                      set obj=Nothing 

                      oSH.Rows("1:1").Select

                      oXL.Selection.Font.Bold = True 

                      oSH.Cells.Select

                      oXL.Selection.Columns.AutoFit 

                      oSH.Range("A1").Select   

                      oSH.Name=left(sCaption,30)   

                      set oSH=Nothing 

                    next

                    set oXL=Nothing

                    end sub

                     

                    You just have to adjust it by your necessity.

                     

                    For more advanced options look at the link I've posted before.

                     

                    Best regards.

                     

                    Tiago

                      • Re: Re: Export to XLSX

                        Thanks for this.

                         

                        I have amended it however it doesnt seem to work. where is the location to send the file to?

                         

                        Thanks


                          • Re: Export to XLSX

                            Try this one, it worked for me. Now it's saving the Excel file.

                             

                            sub launchXL

                            set oXL=CreateObject("Excel.Application")

                            oXL.visible=true

                            oXL.Workbooks.Add

                            aSheetObj=Array("CH1", "CH2")

                            for i=0 to UBound(aSheetObj)

                              oXL.Sheets.Add

                              Set oSH = oXL.ActiveSheet

                            oSH.Range("A1").Select 

                            Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))

                              obj.CopyBitmapToClipboard

                              oSH.Paste

                              sCaption=obj.GetCaption.Name.v

                              set obj=Nothing

                              oSH.Rows("1:1").Select

                              oXL.Selection.Font.Bold = True

                              oSH.Cells.Select

                              oXL.Selection.Columns.AutoFit

                              oSH.Range("A1").Select 

                              'oSH.Name=left(sCaption,30) 

                              'set oSH=Nothing

                            next

                            oSH.SaveAs "D:\temp.xlsx", 51

                            set oSH=Nothing

                            set oXL=Nothing

                            end sub

                             

                            I'm passing just charts, I didn't do the logic for tables.

                             

                            Best regards.

                             

                            Tiago