Weird Excel problem

[Sean]

Hi

I'm using VBScript to open an excel workbook and get some values from
the different worksheets. The problem is that one of the worksheets
creates an "Unknown Runtime Error".

The line on which the error arises is:
strTotalDays = xlWB.Range("C22")

After further investigation, it appears ANYTHING on this sheet seems
to create an error. There is also a hidden spreadsheet just before
that sheet - not sure if that has a problem. If I do the following:
For each objTemp in xlWB.Worksheets
msgbox objTemp.Name
Next

it will msgbox the names of all the sheets including the hidden sheet
and excluding the sheet I want!

Any ideas?

 

[jaf]

Hi Sean,
strTotalDays = xlWB.Range("C22")
Your implying a WORKBOOK object. Workbooks don't have ranges.
Worksheets or sheets do.

Try strTotalDays = xlWB.sheets(sheetname).Range("C22")
Or strTotalDays = xlWB.sheets("sheet1").Range("C22")

 

[Sean]

Hi

Sorry, my mistake (typo).

My code is actually:
Set xlWS = xlWB.Sheets("SPLS PI Data")
strTotalContractedDays = xlWS.Range("C22")

The second line (Range) gives me the error.

Sure as hell, I've tried everything. On Excel Version 10 it actually
works fine, but we've got Version 11 installed and need to use it.

I really don't know how to get around it. It is literally confusing
the Worksheet name with a hidden worksheet, but if I try using the
hidden worksheet name, or the index or either sheet, or anything else,
I can't get a reference to the (visible) worksheet that I need.

Any ideas?
Thanks
Sean