Macro to create an Excel file - Problem if more than 65536 rows

[#1]

Sub ExportAnCTable()

       Dim strvPCT

       ActiveDocument.Fields("Organisation").Select "Cumbria"

       Set vPCT = ActiveDocument.Fields("Responsible Organisation").GetPossibleValues

       strvPCT = vPCT.item(i).Text

       Set objExcel = CreateObject("Excel.Application")

       objExcel.Visible = True

       objExcel.DisplayAlerts = False

       set chart = ActiveDocument.GetSheetObject ("CH109")

       chart.SendToExcel

       Set WB = objExcel.ActiveWorkbook

       WB.SaveAs "c:\temp\"&strvPCT&" - A&C Data.xls"

       WB.Close

       objExcel.Quit  

       chart.Minimize

       ActiveDocument.GetApplication.WaitForIdle

       ActiveDocument.ClearCache       

End Sub

We are using this routine to generate a spreadsheet from a chart on the Qlikview form. It works fine so long as the number of rows in the spreadsheet generated is not greater than 65536. Can anyone advise me how to cater for the situation where there are more than this number. We are using Excel 2007 in our organisation.

Thanks

[Gysbert_Wassenaar · Accepted solution]

The biff format .xls can only have 65536 rows per sheet. So if you save to .xls you're stuck with that limit. You can try specifying the ooxml format:

WB.SaveAs "c:\temp\"&strvPCT&" - A&C Data.xlsx", 51

It's possible the .SendToExcel method also is limited to 65536 rows. In that case you can try increasing the easter egg setting RowLimitForCsvInsteadOfXls. See here for how to do that. Maybe that helps. If not, you're probably limited to exporting to a .csv file instead of to .xlsx.

[#3]

Hi Gysbert

Thanks very much for your reply. I changed it to :

WB.SaveAs "c:\temp\"&strvPCT&" - A&C Data.xlsx", 51

and it correctly produced > 90000 rows.

Question : With the old version of the code (using an xls) it automatically set the sheetname to the filename whereas now that I am using an xlsx it generates the sheetname as <chart object id> and some numbers. Do you know how I can set the sheetname to be the same as the filename ?

Shirley

[#4]

no need macro..just put this configuration on settings.ini in qlikview server :

RowLimitForCsvInsteadOfXls=10000000

change the value as u want..but as far as i know ms excel max sheets is only 255 sheets..so make sure you don't export more than 255 sheets..

thx..