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=oXLBook.Worksheets(1)
to refer the first worksheetYa.it is successful
When the user enters data, it automatically save the data in first worksheet
But when i change it to
Set oXLSheet=oXLBook.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
Expand|Select|Wrap|Line Numbers
- // 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
But when i referred the sheet number other than 1 such as
Set oXLSheet=oXLBook.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