Using - OS- Windows 8.1, Open Office 4.1.5
Last time I visited, this forum, I was asked to read Chapter 15 of Macros Explained - by Andrew Pitonyak.
My Aim: I need to learn to step through sheets in a document and sort ranges, total ranges, copy some cell values to Master Sheet etc, etc.
In this chapter, the following code gives a run time error
"Object variable not set" - It is the second line after the last DIM statement.
As a beginning I thought I will examine the following code and modify it to suit my needs.
I have been trying to get past this line for the past 2 days.
[The code has been copied into module as per the usual rules of OOo.]
Please help
Thank You,
Arun
Code: Select all
REM ***** BASIC *****
Sub AccessSheets
Dim oSheets 'The sheets object that contains all of the sheets
Dim oSheet 'Individual sheet
Dim oSheetEnum 'For accessing by enumeration
Dim s As String 'String variable to hold temporary data
Dim i As Integer 'Index variable
Dim oDoc
oDoc = createNewCalcDoc()
oSheets = oDoc.Sheets 'Stops at this line Error Object Variable not set
REM Insert new sheet as the second sheet.
oSheets.insertNewByName("CreatedSheet", 1)
REM Create a new sheet named "First" at the start.
oSheets.insertNewByName("First", 0)
REM Verify that the sheet named "Sheet3" exists
If oSheets.hasbyName("Sheet3") Then
oSheet = oSheets.getByName("Sheet3")
oSheet.getCellByPosition(0, 0).setString("Test")
REM Copy "Sheet3" to the end. That is copy, not move!
oSheets.copyByName("Sheet3", "Copy1", oSheets.getCount())
End If
If oSheets.hasbyName("Sheet1") Then
oSheets.removeByName("Sheet1")
End If
REM The sheets are indexed starting at zero, but getCount() indicates
REM exactly how many sheets there are.
For i = 0 To oSheets.getCount()-1
s = s & "Sheet " & i & " = " & oSheets.getByIndex(i).Name & CHR$(10)
Next
Msgbox s, 0, "After Inserting New Sheets"
REM Now remove the new sheets that I inserted
oSheets.removeByName("First")
oSheets.removeByName("Copy1")
s = "" : i = 0
oSheetEnum = oSheets.createEnumeration()
Do While oSheetEnum.hasMoreElements()
oSheet = oSheetEnum.nextElement()
s = s & "Sheet " & i & " = " & oSheet.Name & CHR$(10)
i = i + 1
Loop
Msgbox s, 0, "After Deleting Sheets"
End Sub