Export QlikView Object in Excel

Wed, 25 Nov 2020 16:16:04 GMT

I have to export an Object with ID LB04 in excel when one presses a button. I tried this VBscript:

sub excelExport

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = FALSE

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).name = "Supervisor Report"

set XLSheet = XLDoc.Worksheets(1)

set MyTable = ActiveDocument.GetSheetObject("LB04")

set XLSheet = XLDoc.Worksheets(1)

MyTable.CopyTableToClipboard true

XLSheet.Paste XLSheet.Range("A1")

XLDoc.SaveAs "C:\path\to\my\folder\BI\data.xlsx"

XLApp.Visible = False

XLApp.Quit

Set XLApp = Nothing

Set Table = Nothing

end sub

It does not work. How can I solve?

Re: Export QlikView Object in Excel

vinafidalgo — Thu, 07 Apr 2016 18:35:32 GMT

Try to use this function:

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