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

[#1]

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

[santiago_respane]

Hi,

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

Kind regards,

[#3]

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

[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,

[#5]

Hi,

i am not sure to understand your solution, i have already an hidden table and i format it with Qlik parameter (exemple number format integer ...)and it is not working when i import thank to the native XL button.

I think that my problem of numeric and date format is induced by the size of the files (more than 65K lines).

Also i have more than 65 different charts that the reason i was thinking of a macro.

Thank you