4 Replies Latest reply: May 31, 2016 5:27 AM by margaux devay RSS

    Macro send to excel with "date format" "Numeric" and "Region format"



      I am trying to do a macro in order to send Qlik chart in excel but with combine criteria :


      1)Date format : DD:MM:YYYY

      2)regional setting of Excel : if my Qlik separator is "," but the excel separator is "."

      3)Numeric format and not text in excel.

      4)not saving the excel just open it directly.


      I found a  lot of macro with one of the criteria but with my level i am not able to mix all the macro


      help please

        • Re: Macro send to excel with "date format" "Numeric" and "Region format"
          Santiago Respane


          Please be kind and share an app with dummy data and your macro in order to solve your issue.

          Kind regards,

            • Re: Macro send to excel with "date format" "Numeric" and "Region format"


              the export code that i use to open in excel is :



              sub Export
              set obj = ActiveDocument.GetSheetObject("CH02")
              obj.ExportEx "C:\ExcelTest.xls", 5
              set app=ActiveDocument.GetApplication
              app.Launch "C:\ExcelTest.xls",""
              end sub




              For the  "use regionnal setting for send in Excel" i find this macro for it but it is not working with XLSX and with open in excel :


              Sub Export1

              ActiveDocument.ClearAll false


              set corp = ActiveDocument.GetSheetObject("CH02")

              corp.ExportBiff  pathcorp & "TEST.xls"


              set val=ActiveDocument.Fields("REGION").GetPossibleValues

              set XLApp = CreateObject("EXCEL.application")

              XLApp.Visible = FALSE

              set XLDoc = XLApp.Workbooks.Add


              for i=0 to val.Count-1

              set MyTable = ActiveDocument.GetSheetObject("CH02")


              ActiveDocument.Fields("REGION").Select val.Item(i).Text

                      MyTable.ExportBiff  path & val.Item(i).Text &".xls"



              Set XLApp = Nothing

              Set Table = Nothing


              'Msgbox "Exported Sucessfully"

              end sub


              finally i need to format the date

              as DD/MM/YYYY


              in attchment send you the excel file with data

            • Re: Macro send to excel with "date format" "Numeric" and "Region format"
              Santiago Respane


              my suggestion is to have a hidden copy of your table with the formats you need and then export it with the standard macro function. This will be easier for you to reduce impact changes. If tomorrow you need new formats it will be easier to do it in QV than changing your macro.


              If this is not an option we can start working on your macro.


              Please let me know if this helps.

              Kind regards,