Open new Excel sheet when number of rows is too big

[Isa_pm]

Hi again,

I'm using ActiveX to write some data to Excel. It is common that the amount of data that I want to write to Excel takes more than the 65536 rows of a sheet, so every time this number of rows is reached I wanted to open a new sheet and write data to it and so one. The writting part is alrigth (I adapted the Write Table to XL.vi), I can see the row current row number I am writting data to and I now how to open the new sheet and write to it. The problem is that I don't seem to find a way to open the new sheet only once after the row nº is greater than 65536...  I don't know where to test the row number...I join my code if you have some time to look at it, any suggestion would be of great help.

I hope you understand my problem...

Best regards,

Isabel

[Khalid]

Hi Isabel,

You wrote: "I can see the row current row number I am writting data to.. "

So, this is where I would compare if the row number is greater than 65536.  Maybe you can post your VI...?

Regards,

-Khalid

[Michael_Munroe]

To write to continuous rows you need to keep the row number and sheet reference as pointers to where the data is stored.  Before you write the row, check the row count to see if it is = 65535.  If it is, then add a new sheet, replace the sheet reference with the new one and reset the row number to 1.  If you do not have the Workbook reference handy, then use the old sheet referece with the Parent property to add the new sheet.

NewSheet = OldSheetRef.Parent.Sheets.Add(after:=SheetRef)

[Isa_pm]

Hi,

I forgot to join my VI. Here it is.

I'll try to do what you told me...

Thanks

Isabel

[Michael_Munroe]

Here is the change that I suggested.  I use the modulus for simplicity to wrap the 65535 back to 0.  A new sheet is added only when the row and column indexes are both at 0.  The shift registers are used to hold the current value of the Sheet reference.

I also correct several bugs in your code with comments on why.

The nested for loops used is the Example code is only suitable for a small amount of data.  The proper way to transfer a large array to Excel is with a Block Write.  Your data array would need to be broken into worksheet-sized chunks and written with a single Range->Value write using the double indexed data.

[Isa_pm]

Hi,

Thank you very much for your help, that's exactly what I needed. Only now I see what I was doing wrong...

Isabel