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.
