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"

    margaux devay

      Hello,

       

      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

          Hi,

          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"
              margaux devay

              Hi,

              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

              pathcorp="F:\TEST\"

              set corp = ActiveDocument.GetSheetObject("CH02")

              corp.ExportBiff  pathcorp & "TEST.xls"

              ActiveDocument.Fields("REGION").Clear

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

              path="F:\TEST\"

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

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

                

              next

              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

              Hi,

              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,