code modules included in sheet count?

[Guest]

Hi.

I'm working with an Excel file that was the output of Oracle's older version
of ADI (Application Desktop Integrator)... the version of ADI that was a
deskop app itself, and may be running some old Excel things.

There's a code module called "CODE" in it, which shows as a module in the
VBE. But, when asked how many sheets the file has,

Msgbox ActiveWorkbook.Sheets.Count , the module with the CODE is being
included as a sheet. I've proved this by removing the module CODE, and it
then reports 29 sheets.

Originally, I had thought they probably had a sheet with property visible =
xlVeryHidden , but that was not the case.

Is there a property somehwere where I can tell when a module of code is
being included in the sheet count?

Thanks,
Mark

 

[Guest]

I would look at the project window in VBA. The window shows the sheet number
and the sheet name.

 

[Guest]

I would look at the project window in VBA. The window shows the sheet
number

and the sheet name.

In a normal Excel file, I'd agree with you. However, this time, "No it
doesn't."

There are 29 sheets listed in the 'Microsoft Excel Objects' folder (and no,
I didn't miscount).

Yet, the code msgbox activeworkbook.sheets.count reports 30.

There is one object in the 'Modules' folder, whose name is CODE .

If I delete that module, the MsgBox ActiveWorkbook.Sheets.Count reports 29.

So the code module is being reported as a sheet, which was my question.

Is there a way I can tell in the code when that is the case?

 

[Guest]

Try this.

Worksheet.Type can be the following
xlChart
xlDialogSheet
xlExcel4IntlMacroSheet
xlExcel4MacroSheet
xlWorksheet

Use following code

For Each Ws In Workbooks

Ws.Activate

If ActiveCell.Worksheet.Type = xlWorksheet Then

End If
Next Ws

 

[Guest]

If ActiveCell.Worksheet.Type = xlWorksheet Then

Thanks for the suggestion.

In this case, you can't actually get to a cell on the worksheet to use
ActiveCell, though.

I have a plan now that will work, though. Thanks for your suggestion. I'm
still not quite sure what Oracle did, but it doesn't matter any more.

I tried the Type idea, and the module named CODE reports constant -4167, the
same as any other sheet, which must be the constant for xlWorsksheet

My need is to run up through the worksheets performing some code to transfer
some things out to another workbook, but the code needs not to work on this
one oddball sheet.

I've discovered that the workbooks is making a distinction between Sheets
and Worksheets

AtiveWorbook.Sheets(1) is the CODE which I do not want to use, but

ActiveWorkbook.Worksheets(1) is the first visible regular sheet, which I want.

So that will work.

Thanks.

 

[Guest]

Does this mean you get an error? You can use the error condition to locate
the Oracle sheet. do you get the same number of sheets using sheets and
worksheets?

 

[Guest]

Does this mean you get an error?

depends upon the code.

if I were to write:

dim ws as worksheet
set ws = sheets(1)

Yes, it would error.

You can use the error condition to locate the Oracle sheet.

I could.

do you get the same number of sheets using sheets and
worksheets?

No. worksheets.count = 29 ; sheets.count = 30

I saw something a while back about the distinction between a "Worksheet" and
a "Sheet"

 

[Dave Peterson]

xl97 added the VBE. xl95 and before used macro sheets.

It sounds like the old program is creating a file using xl95.

I think I did the same thing (but it was a long time ago). I exported the
module, deleted that module and reimported it. (I needed that code.)