Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

export to excel

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

I can walk on water when it freezes
3 Replies
Not applicable

Re: export to excel

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

Re: export to excel

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

Partner
Partner

Re: export to excel

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

see this example

  1. Sub Test 
  2.    ExcelAppend "h:\test.xlsx", "CH01" 
  3. End Sub 
  4. Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID) 
  5.    ' Create an instance of Excel 
  6.    SET objExcelApp = CREATEOBJECT("Excel.Application"
  7.     
  8.    ' Open workbook 
  9.    WITH objExcelApp 
  10.       .DefaultSaveFormat = xlWorkbookNormal 
  11.       .DisplayAlerts = FALSE 
  12.       .Workbooks.Open strExcelAppenFile 
  13.       .DisplayFullScreen = FALSE 
  14.       .Visible = FALSE 
  15.    END WITH 
  16.     
  17.    ' Set worksheet 
  18.    SET objExcelSheet = objExcelApp.Worksheets(1) 
  19.        
  20.    ' Set Excel used range 
  21.    SET objExcelRange = objExcelSheet.Range("A65535").End(-4162) 
  22.    ' Last used row in column
  23.    intExcelLastRow = objExcelRange.Row 
  24.    ' Set object to append from 
  25.    SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID) 
  26.    ' Loop all rows of the object except first header row 
  27.    FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1 
  28.       ' Loop all columns of the object 
  29.       FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1 
  30.          ' Get object data 
  31.          SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 
  32.          ' Add that data to Excel cell 
  33.          objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 
  34.       NEXT 
  35.    NEXT 
  36.     
  37.    ' Save and quit 
  38.    objExcelSheet.SaveAs strExcelAppenFile 
  39.    objExcelApp.Application.Quit 
  40.    SET objExcelSheet = NOTHING       
  41.    SET objExcelApp = NOTHING 
  42. END SUB