Run-time error 9 - Subscript out of range

[cassidybklyn]

Gentlemen,
I am executing the following VB6/Excel:

Dim xlApp As Object
Dim xlWbk As Object
Dim xlWksht As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open("C:\BiWeeklyPeriod.xls")
Set xlWksht = xlWbk.Worksheets("Sheet1") <======= this line

Then I got the above subject line error on the last line of code. What have I done wrong?
Thanks.
Cassidy.

 

[PHV]

The sheet has been renamed ?
you may try this:
Set xlWksht = xlWbk.Worksheets(1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

I almost always use the Sheet CodeName property rather than the Name property which users can change.

Set xlWksht = xlWbk.Sheet1 <======= this line

If Sheet1 is indeed the CodeName of the sheet that once was Sheet1.

Skip,

_[red][/red]

 

[cassidybklyn]

Guys,
there's an already existing spreadsheet, so my intension is to open it as thus: "C:\BiWeeklyPeriod.xls" then refer to or use the same opened sheet for data collection.
Thanks.
C.

 

[SkipVought]

"there's an already existing... "WORKBOOK.

Skip,

_[red][/red]

 

[cassidybklyn]

Yes, there's already an existing (template) "workbook", which I already opened here:

Set xlWbk = xlApp.Workbooks.Open("C:\BiWeeklyPeriod.xls")

However, as I'm stepping through the codes, I got RT-Error 424 (Object required) on the third line below:

xlWksht.Activate
xlWksht.Range("A1").Activate
Selection.CurrentRegion.Select <===== Error here!!!
Selection.ClearContents
xlWksht.Range("A1").Select
Thanks.
C.

 

[cassidybklyn]

Sorry guys. Ignore my last post: I forgot to include MS Excel 9.0 object library in my references.
Thanks again.

 

[SkipVought]

xlWksht.Activate
xlWksht.Range("A1").Select
Selection.CurrentRegion.ClearContents

Skip,

_[red][/red]

 

[PHV]

In fact you have to qualify the Selection too:
xlApp.Selection.CurrentRegion.ClearContents

but, why not simply this ?
xlWksht.Range("A1").CurrentRegion.ClearContents

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886