How to refer a particular excel sheet through vb6 code

[Hema Suresh]

I am doing a small project using vb6 and automating Excel through vb6

First, I will tell about my project briefly
I am trying to save the database of 3 kinds of books
I used VB6 forms as the user interface
I coded VB6 in the way to accept the user's entry and saving it in Excel sheet

I created 12 worksheets in an Excel workbook and re-named it from Jan, Feb,....till Dec

I used
Set oXLSheet=oXLBoo k.Worksheets(1) to refer the first worksheet
Ya.it is successful
When the user enters data, it automatically save the data in first worksheet

But when i change it to
Set oXLSheet=oXLBoo k.Worksheets(2), the data is not stored in the second sheet. i hope my code is correct. But the code which follows it showing the error "Activate method of Range class failed"

I will paste my code below

// I set the Excel object variables here//

Set oXLBook = oXLApp.Workbooks.Open(TheFileName)
Set oXLSheet = oXLBook.Worksheets(1)
Set oXLRange = oXLSheet.UsedRange

// I gave the value of E1 and F1 cells as "BOOKS Records here "//

oXLSheet.Range("E1:F1").Value = "BOOKS RECORDS"

// I left two rows blank after the activated cell "F1" here //

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

BlankRowNum = oXLApp.ActiveCell.Row + 1

NextToBlankRow = oXLSheet.Cells(BlankRowNum, 1).Offset(1, 0).Row + 1

// I gave a cell value as "record" after the 2 blank rows //

TitleRowCell = "E" & NextToBlankRow
oXLSheet.Range(TitleRowCell) = "RECORD"

// I activated the cell with value "record" and saved the BooksNames values following the activated cell //

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "E" & newFstRow
oXLSheet.Range(newFstCell) = BooksNamesTitle
newScndCell = "F" & newFstRow
oXLSheet.Range(newScndCell) = BooksNamesWithNum

Everthing is working

But when i referred the sheet number other than 1 such as
Set oXLSheet=oXLBoo k.Worksheets(2), it is not working and it gives the error "Activate method of range class failed"

I understand none of the cells are activated. But i am not sure how to solve it

Please help me out with this
Thank you

[Luuk]

sorry, i dont have Exel on my computer.....

[Hema Suresh]

Its ok
thank u.............. ............... ....

[NeoPa]

Like cells, worksheets can also be selected and activated (not the same). I suspect the reference is determined to be invalid either because the specified worksheet itslef is not a selected sheet, or because no references to non-selected sheets can take place while the workbook is still in a hidden state. Try playing with those ideas and see what you find.