How to export in excel many records

Tue, 06 Dec 2011 11:11:38 GMT

Hi guys,

I've a big problem whenever i try to export in excel data from my qv document, by the way, the document is 800 mb and i've a lot of tabs with a lot of objects inside, and a lot of datas(over then 18.000.000 milions of records).

I created a lot of push buttons wich contains calculated numbers by expression, for each one there is a macro wich export entire recorset in excel to give to the customer detailed datas.

Untill the file was small, no problmes, but, as soon as the file become greater then 200 mb every export decreased perfomance and often crashed(by closing the document in browser)

I used, first, export biff function, but not work well and the performance are too bad, so i used macro looks like this one :

Function SendNiceAndEasyStaffToExcel()

XLSExport = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\Exporttest2.xls"

Set AppExcel = CreateObject ("Excel.Application") 'AppExcel definition

AppExcel.Visible = True 'AppExcel (visible) will be executed

set obj = ActiveDocument.GetSheetObject("CH01") 'Obj is defined & accessed

obj.SendToExcel 'SendToExcel function on Obj

AppExcel.ActiveSheet.Name = "Raport CH01" 'Renames the active sheet.

XLSExport = "C:\Documents and Settings\z679574\Desktop\ExportingTesting\Exports\Exporttest2.xls"

AppExcel.ActiveSheet.SaveAs (XLSExportTMP1) 'First export is saved as the value of XLSExportTMP1

set OWB_XLSExportTMP1 = AppExcel.Workbooks.Open(XLSExportTMP1) 'Object OWB_XLSExportTMP1 (with .Open ) is defined.

OWB_XLSExportTMP1.Save 'Object OWB_XLSExportTMP1 is reopened/accessed via .Save

OWB_XLSExportTMP1.Close 'Object OWB_XLSExportTMP1 is closed via .Close

set OWB_XLSExport = AppExcel.Workbooks.Open(XLSExport) 'Object OWB_XLSExport (with .Open ) is defined.

OWB_XLSExport.Save 'Object OWB_XLSExport is reopened/accessed via .Save

OWB_XLSExport.Close 'Object OWB_XLSExport is closed via .Close

'Now closing App.Excel also from running processes (needs all workbooks to be closed).

AppExcel.Visible = False ' Excel.exe is closed

set AppExcel = nothing ' definition cleared to avoid conflicts with other functions.

set OWB_XLSExport = nothing ' definition cleared to avoid conflicts with other functions.

set OWB_XLSExportTMP1 = nothing ' definition cleared to avoid conflicts with other functions.

End Function

But......SendToExcel function seem to be better then exportbif, but also in this case, when datas grow up after loading new datas in the document, doesn't work fine(too long time)

Finaly i used copydatatoclipboard function, but no good results.....

Is there a right way to export so much data to excel from a qv document with a big size ad mine ?

Thx a lot.

Bye.

Re: How to export in excel many records

hectorgarcia — Fri, 25 Oct 2013 17:28:33 GMT

yes , export to txt in your macro

obj.Export "C:\QlikView Publisher Data\Documents\"&v.GetContent.String&"TRANSACCIONES.txt" , "|"

I hope it will help

topic How to export in excel many records in QlikView

How to export in excel many records

Re: How to export in excel many records