14 Replies Latest reply: Apr 26, 2016 6:25 AM by Miguel Guillot RSS

    ExportEx not working

    Miguel Guillot

      Hello,

       

      I am having an issue with Export Ex when I try to export a Graph to .xls This is the code I am using:

       

      set chart_kpi5_1 = .doc.GetSheetObject("CH368")

      chart_kpi5_1.ExportEx"C:\Folder1\test.xls",5



      The problem is that when I go to that folder it has not created anything!

       

      Does anyone had the same problem?

       

      Thank you!

        • Re: ExportEx not working
          Tamil Nagaraj

          Miguel,

           

          Try,

           

          set chart_kpi5_1 = ActiveDocument.GetSheetObject("CH368")

          chart_kpi5_1.ExportEx "C:\Folder1\test.xls" , 5

            • Re: ExportEx not working
              Miguel Guillot

              Hi Tamil,

               

              First of all thank you. But it still does not work as I am using a .vbs script... Any other idea?

               

              Thank you very much again!

                • Re: ExportEx not working
                  Tamil Nagaraj

                  Miguel, The above one is working fine. Could you post your full script and explain us what you are trying to do.? So that we can check and provide a solution.

                    • Re: ExportEx not working
                      Miguel Guillot

                      Hi Tamil,

                       

                      This is my code:

                       

                      Sub includeFile(ByVal fSpec)

                          executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile(fSpec).readAll()

                      End Sub

                       

                       

                      includeFile "QvUtils.vbs"

                       

                       

                      with New QlikView

                       

                        .open("..\..\QVW\Sales\Field Performance.qvw")

                        set chart_kpi5_2 = ActiveDocument.GetSheetObject("CH356")

                       

                       

                      When I do this I get: Object required: ActiveDocument

                       

                      Thank you again!

                        • Re: ExportEx not working
                          Tamil Nagaraj

                          Miguel,

                           

                          Try,

                           

                          Sub includeFile(ByVal fSpec)

                              executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile(fSpec).readAll()

                          End Sub

                           

                          includeFile "QvUtils.vbs"

                           

                          with New QlikView

                            .open("..\..\QVW\Sales\Field Performance.qvw")

                          set chart = .doc.GetSheetObject("TB03")


                          chart.ExportBiff("C:\Folder1\test.xls")

                            .doc.CloseDoc

                            .Quit

                          end with

                            • Re: ExportEx not working
                              Miguel Guillot

                              Hi Tamil,

                               

                              Thank you very much again. The problem is that the object I am trying to export is a Graph and Graphs do not suppor ExportBiff... That is why I am trying with ExportEx...

                               

                              Thank you again!

                                • Re: ExportEx not working
                                  Tamil Nagaraj

                                  Hello Miguel,

                                   

                                  Sorry for the late reply. I had left for the day and I saw your message before an hour. After gathering some knowledge from google, I found a solution for you.

                                   

                                  Below code exports the graph into .XLS format.

                                   

                                  '==========================================================
                                  Sub includeFile(ByVal fSpec)
                                      executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile(fSpec).readAll()
                                  End Sub
                                  
                                  includeFile "QvUtils.vbs"
                                  
                                  with New QlikView
                                  '==========================================================
                                  'File Path & Date Format => YYYYMMDD_HHMMSS
                                  
                                      strPath = GetAbsolutePath("..\Output\")
                                      strDate = Year(now) & Right ("0" & Month(Now),2) & Right ("0" & Day(Now),2)
                                      strTimeStamp = Right("0" & Hour(Now),2) & Right ("0" & Minute(Now),2) & Right ("0" & Second(Now),2)
                                      strFile = StrPath & "Test " & strDate & "_" & strTimeStamp & ".xls"
                                  '==========================================================
                                  
                                     .open("..\App\1.qvw")
                                      set chart = .doc.GetSheetObject("CH01")
                                      chart.CopyBitmapToClipboard
                                  '==========================================================  
                                  
                                      Set obj = CreateObject("Excel.Application")
                                      obj.visible = False
                                      Set objWb = obj.Workbooks.Add
                                      obj.Activeworkbook.Activesheet.Paste()
                                      obj.Activeworkbook.SaveAs strFile, -4143   '51 for xlsx
                                      obj.Quit
                                      obj.visible = True
                                      Set chart = Nothing
                                      Set obj  = Nothing
                                      Set objWb = Nothing
                                  '=========================================================
                                  
                                    .doc.CloseDoc
                                    .Quit
                                    Msgbox "Graph Exported Successfully!!", VbOKOnly + VbInformation ,"VBScipt Status"
                                  end with
                                  
                                  

                                   

                                  Output:

                                  Untitled.png


                                    If you want to save the graph as JPG format, you can use the below code.


                                  Sub includeFile(ByVal fSpec)
                                      executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile(fSpec).readAll()
                                  End Sub
                                  
                                  
                                  includeFile "QvUtils.vbs"
                                  
                                  with New QlikView
                                    .open("..\App\1.qvw")
                                    set chart = .doc.GetSheetObject("CH01")
                                    chart.ExportBitmapToFile  (GetAbsolutePath("..\Output\Test.jpg"))
                                    .doc.CloseDoc
                                    .Quit
                                    Msgbox "Graph Exported (JPG) Successfully!!", VbOKOnly + VbInformation ,"VBScipt Status"
                                    Set chart = Nothing
                                  
                                  end with
                                  end w
                                  
                                  

                                   

                                  I have attached all the tested files for your reference. Have a look and let me know if you still have any issues.

                              • Re: ExportEx not working
                                Marcus Sommer

                                with New QlikView

                                 

                                  .open("..\..\QVW\Sales\Field Performance.qvw")

                                 

                                isn't a valid statement to open another qvw - see here an example from the APIGuide.qvw (is in your install-folder by automation):

                                 

                                rem ** VBScript **

                                set App = ActiveDocument.GetApplication

                                set newdoc = App.OpenDoc ("C:\MyDocuments\QV4Automation.qvw","","")

                                 

                                set App = ActiveDocument.GetApplication

                                App.OpenDoc "C:\MyDocuments\Budget.qvw","JNN","123456"

                                 

                                rem ** Visual Basic **

                                dim App as New QlikView.Application

                                set newdoc = App.OpenDoc ("C:\MyDocuments\Budget.qvw","JN","123456")

                                 

                                and I'm not sure if relative paths are working here.

                                 

                                - Marcus