export to excel

ali_hijazi — Sat, 10 May 2014 14:50:14 GMT

Dears

I'm working on QlikView 11.2 SR5

when Users send values to Excel of any chart then the default extension that comes in the dialogue box is .xls

how can we force it to become .xlsx

knowing that the extension can be changed manually but I want it to be the default as .xlsx

Please advise

Re: export to excel

Sun, 11 May 2014 10:19:38 GMT

Hi, I dont see any problem to keep the .xls format...

its_anandrjs — Sun, 11 May 2014 10:57:41 GMT

After export the files as *.xls you can save it as *.xlsx but there is no problem to keep it as *.xls.

fkeuroglian — Sun, 11 May 2014 16:31:52 GMT

Hi, you could do it with a macro and set the default xlsx format

see this example

Sub Test
ExcelAppend "h:\test.xlsx", "CH01"
End Sub
Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID)
' Create an instance of Excel
SET objExcelApp = CREATEOBJECT("Excel.Application")
' Open workbook
WITH objExcelApp
.DefaultSaveFormat = xlWorkbookNormal
.DisplayAlerts = FALSE
.Workbooks.Open strExcelAppenFile
.DisplayFullScreen = FALSE
.Visible = FALSE
END WITH
' Set worksheet
SET objExcelSheet = objExcelApp.Worksheets(1)
' Set Excel used range
SET objExcelRange = objExcelSheet.Range("A65535").End(-4162)
' Last used row in column A
intExcelLastRow = objExcelRange.Row
' Set object to append from
SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID)
' Loop all rows of the object except first header row
FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1
' Loop all columns of the object
FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1
' Get object data
SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn)
' Add that data to Excel cell
objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text
NEXT
NEXT
' Save and quit
objExcelSheet.SaveAs strExcelAppenFile
objExcelApp.Application.Quit
SET objExcelSheet = NOTHING
SET objExcelApp = NOTHING
END SUB