VB application stops generating spreadsheet at row 65536

[TerryDav]

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.

[Trebor76]

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

[:) Sixthsense :)]

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

Not sure what's wrong happening at your end

[TerryDav]

Thanks for the replies.

There are no integer variables, all are long. The spreadsheet opens with 1,048,576 rows but the application stops at the 65,536th row.

I just had a thought. The application is running on a Windows Server 2003 (32bit) machine with Office 2010 (32 bit) installed. I don't suppose it's anything to do with the 32bit operating system.

Thank you.

[:) Sixthsense :)]

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

[TerryDav]

Here's the code

Please Login or Register  to view this content.

[:) Sixthsense :)]

What is the value of k?

[TerryDav]

Thanks again for the help
When the application stops, k is 65536, k is dimmed as Long.
Cheers.

[Norie]

Where/how are you running this code?

[TerryDav]

An exe deployed to a Windows 2003 server. The server has Office 2010 32 bit installed on it. A scheduled task runs this every night.

[Trebor76]

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?

[TerryDav]

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

[Trebor76]

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?

[TerryDav]

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

[Trebor76]

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?

[TerryDav]

Is this the code you're after, pSearch is a document management (eDocs) object. When I stepped through the code, pSearch.GetRowsFound has > 65536 records so it doesn't look like this object that's causing the problem.

Please Login or Register  to view this content.

[Obsessed]

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.

[Trebor76]

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

[TerryDav]

File not saved until the end of the application, hasn't got to that point at the stage where it stops. When it is saved, after all the rows have been written, it is saved as an xlsx file.

[Obsessed]

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.

[TerryDav]

Thanks for all your help, it's really appreciated. The default save format is xlsx, I checked in the settings. Also when I opened Excel and then clicked on save, the save as type shows as xlsx.

[Obsessed]

Sorry...worth a shot.

[TerryDav]

Do you think it's anything to do with the OS being Windows Server 2003?

[Leith Ross]

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.

[Trebor76]

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

[TerryDav]

Thanks for the continued responses.
I can see how it can be this line of code because it doesn't get to that point in the code? The application doesn't save the spreadsheet it stops at row 65536 and just hangs there, doesn't save the spreadsheet.

[Trebor76]

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