topic Re: Macro to create an Excel file - Problem if more than 65536 rows in QlikView

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

Tue, 13 Aug 2013 21:24:47 GMT

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

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

Gysbert_Wassenaar — Wed, 14 Aug 2013 07:05:17 GMT

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.

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

Wed, 14 Aug 2013 15:12:24 GMT

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

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

Mon, 21 Apr 2014 07:33:47 GMT

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..