VB6 cannot create more than 133 worksheets using Office11 excel object

[ProfReynolds]

I'm trying to create a workbnook using VB6. Basicall, I copy a worksheet, set the new worksheet name, and set a parameter on the worksheet. But VB6 (using office 11 excel object) will crash when I try to create the 134th sheet.

Do I need to flush or commit? Is this a VB limit?

Alternatevely, I could call a VBA script within the wrokbook - but I do not know how to do that.

This code crashes when SheetNo=123:

    Set oExcelWs = oExcelWb.Sheets("SourceSheet")
    oExcelWs.Activate
    
    For SheetNo = 1 To 300
        oExcelWs.Copy Before:=oExcelWb.Sheets(SheetNo+4)
    Next

 

[EBGreen]

The number of worksheets allowed in a workbook is limited by physical memory. I suspect this is the problem. Either way, if you have a situation that needs you to make 100+ worksheets, then I'd say it was screaming for a database solution.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[ProfReynolds]

The database idea sounds good on the surface - but we are using Excel as a formatting tool - the database is the source data being used to create the worksheets.

Odd thing is that the Excel documentation says that the worksheet count is indeed limited by physical memory. But this limit seems to be independent of machine, and independent of auxiillary tasks on that machine.

 

[PHV]

So, if the data are already in a DB, why so many sheets ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, hit submit to fast !
So, if the data are already in a DB, why so many duplicated sheets ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EBGreen]

If you are just looking for formatting of data that is already in a DB, why not front-end it with Access?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[ProfReynolds]

Good questions - and I'm beginning to question my approach.

The reason for the quantity of worksheets is only a formatting issue - the sheet template is formated by my customer. We replicate the format for each new data - and print. Printing is the reason for the sheets, the formatting, etc.

But I'm beginning to ask myself whether I can push the newly created and populated sheets into a separate workbook. For some bizzare reason, moving a worksheet to a new workbook does not seem to result in the same worksheet count limit.

So does anyone have any suggestions for calling a VBA within the workbook from within my VB6 application?

Thanks to both of you responders

 

[xlbo]

It is all to do with the sheet CODE name

when you COPY a woksheet, the code name is changed from say Sheet1 to Sheet11. The next time the same sheet is copied, it will create sheet111 and so on and so forth.

The error is actually created by attempting to create a code name for a sheet that is greater than the internal limit for the length of a sheet code name - nothing to do with physical memory.

Can't remember exactly what the limit is but it only happens when you copy sheets - consider simply inserting th required no. of sheets instead

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question