VB application stops generating spreadsheet at row 65536

Hello
I have a VB application that's been running for the last couple of years on a machine that has Office 2010 installed. Now that the number of rows generated in the spreadsheet is > 65536, the application stops at this point.

I can't work out why this is happening, the reference used is Microsoft Excel 14.0 Object library.

Here's a snippet of the code, if any more is required, please let me know:

Please Login or Register  to view this content.

Does anyone know why this is happening? Any help would be greatly appreciated.

Thank you.

Hi TerryDav,

You need to wrap your code with the appropriate tags as per forum rule 3 (refer here). Thanks.

The number of rows was increased from 65,536 to 1,048,576 with the release of Excel 2007 so the larger number of rows have been available for a while. I see no reason why the above is failing due to that - are there any row variables defines as integer?

Robert

Tested your code and for me it's creating workbook with 10,48,576 rows.

Not sure what's wrong happening at your end

Originally Posted by TerryDav

The spreadsheet opens with 1,048,576 rows but the application stops at the 65,536th row.

Then you have to share that code with us which may help us in fixing your issue

Here's the code

Please Login or Register  to view this content.

What is the value of k?

Where/how are you running this code?

Also what are you trying to write into the various columns with the WriteRow macro i.e. what is trying to be returned to cell A2 of the active sheet with this line??:

Please Login or Register  to view this content.

You also said you're using early binding for Microsoft Excel 14.0 Object library which is for Microsoft Excel 2010 running on Windows 7. Are you sure the specs of the server are the same (it would seem Excel 2003 is on it as this was the last version of Excel with the 65,536 row limit).

How many rows are physically on any sheet (you can check this via clicking on any blank column and then pressing the End key followed by the Down Arrow key) from the new workbook the macro creates?

How many rows are physically on any sheet (you can check this via clicking on any blank column and then pressing the End key followed by the Down Arrow key) from the new workbook the macro creates?

So how many rows are there?

Area is a string variable, so are all the other variables in the various columns except DocNumber, it's a long variable.

What is the code (or manual procedure) to assign the range to these string variables?

Are you saving the file as a .xlsx first? if you're creating a workbook that is .xls, it doesn't matter what version of Excel you're running, it will limit the rows to 65536. I'd make sure your default workbook type is .xlsx.

Not really sure then - maybe it's a string limitation though how can Area (which is for Area_Code) be returning so many records?? Maybe these variables need to be defined as a recordset?

Good luck with it.

Robert

What is the default file for your Excel though? Try saving it first, run it, then resave. When Excel creates new workbook, it will create it in its default workbook file type (XLS, XLSX, XLSB, etc), and if it happens to be XLS, it will only allow 65k rows UNTIL the workbook is saved as an XLSX, in which it will allow more. Try saving it as an XLSX immediately after creating it, and then let the code finish and resave at the end.

Sorry...worth a shot.

Hello TerryDav,

I had s similar problem awhile ago. I discovered that the original workbook was saved as an xls and the newer files it created were saved as xlsm. While the newer version should have referenced beyond 65536 rows, it did not. Although 2007 and later is backward compatible with earlier versions of Excel, the earlier Excel versions still retain their original file limits despite the new extension. I suspect the same is true for files created on a Excel 2003 server.

I wonder if it's got to do with this line...

Please Login or Register  to view this content.

...as you can't specify the format when using SaveCopyAs and it's still in Excel 2003 mode.

Comment out that line (so it's there in case the following doesn't work) and try this instead:

Please Login or Register  to view this content.

Regards,

Robert

OK - what about if you open a blank workbook and put the following code onto it:

Please Login or Register  to view this content.

If that works save it as a macro enabled workbook and use that (you could save it read-only to preserve it) as I can only guess it's go to do with fetching a new workbook from the new instance of Excel being created via the code.

If that doesn't work I'm out of ideas I'm afraid

Robert