Excel Sheet Counting

[vdennis]

Good afternoon all. I am trying to figure out how to read the sheet tab name in a workbook where the number of sheets may vary. What I want to do is to 'trim' the names so in a case if I am looking for "JULY16", and the sheet name it "JULY16 " (notice the space,) it wont crash the program. Yes, that difference did crash the program and it took us hours to figure it out, as some time it would work, and other not. Reason? Different months!
As always, many thanks!
-Dennis-

 

[Rob Fitzpatrick]

Would the TRIM function work for you?

 

[vdennis]

Thanks for the quick response, but in the case no. Not knowing how many sheets need to be examined is the issue. What I did find was a VB bit of code;
?activeworkbook.Sheets.Count

So now I just have to plug in the com-handle stuff and this should solve that part. Once I know how many sheets are in the workbook, I can check the name of each sheet and be sure there are no spaces or what ever. The one we where working on had 15 sheets, and only the July sheet had the trailing space. So once I have the progress code working, I'll post it here, as I am sure this could come in handy.
-Dennis-

 

[Rob Fitzpatrick]

If I recall there's a Sheets collection in the Excel object model that you can iterate over.

 

[vdennis]

And the answer is :
... Normal excel def in place, handle and all....

def iSheets as integer.
assign iSheets = hWorkBook:count.

Worked great, but..... we only see 13 sheets and there are 27!
Still could work just fine but was surprised by that .

 

[Rob Fitzpatrick]

Sheets can be visible, hidden, or very hidden. That could possibly account for the seeming discrepancy.

 

[mrobles]

DEF VAR i AS INT.
DEF VAR actual AS INT.
DEF VAR nombre as CHAR.
I = chExcelApplication:Sheets:COUNT.
DO actual = 1 to i.
chWorkSheet = chExcelApplication:Sheets:ITEM(cual) NO-ERROR.
nombre = TRIM(chExcelApplication:Sheets:Item(actual):NAME).
END.