[Solved] Manipulate Sheets in a Calc document

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Arun P. Pai
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

[Solved] Manipulate Sheets in a Calc document

Post by Arun P. Pai »

Dear Friends,

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
Last edited by Arun P. Pai on Thu Jul 05, 2018 5:36 am, edited 2 times in total.
Arun
OS - Windows 8.1
Open Office 4.1.5
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Manipulate Sheets in a Calc document

Post by Zizi64 »

Please use the Code tag for your sanple code in this forum...


Code: Select all

oDoc = createNewCalcDoc()
The createNewCalcDoc() is not a predefined API function, but it is an another sample function in the book. You must copy the code of the function into your Module. Then you will able to call it. From the OOME 3.0.15:
Listing 2. Create a new Calc document.

Code: Select all

Function createNewCalcDoc
  Dim noArgs()          'An empty array for the arguments
  Dim sURL As String    'URL of the document to load
  Dim oDoc

  sURL = "private:factory/scalc"
  oDoc = StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, noArgs())
  createNewCalcDoc = oDoc
End Function
The API function - what will really create a new empty document - is the function 'LoadComponentFromUrl()' with some passed special parameters: '(sURL, "_blank", 0, noArgs())'
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Arun P. Pai
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

[Solved] Re: Manipulate Sheets in a Calc document

Post by Arun P. Pai »

Dear Friend,

I am new to this forum.

Thank you so much for your help, the Macro worked after I inserted the Function CreateNewCalcDoc()
[The listing 403 Create a new Calc Document is in the book. But I failed to appreciate it's use and application.]

In the Function - What does this line mean ? "sURL = private:factory/scalc".

In your reply you have mentioned....
"Please use the Code tag for your sanple code in this forum..." - ?
What is a Code Tag?

Thank you very much.

Arun
Arun
OS - Windows 8.1
Open Office 4.1.5
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Manipulate Sheets in a Calc document

Post by Zizi64 »

What is a Code Tag?
When you click on the button POSTREPLY (but not on the Quick Reply), then will appeare a text editor panel with some formatting features.
Formatting functions of the forum
Formatting functions of the forum
You can use the bold, italic, underlined tags as you use them in a normal document. The formatting "commands" (tags) will be appeared in the text while you edit it, but the formatted text will be appeared after you send it to the forum.
And there are some other features: Code, Quote, and others... A text between the opening and closeing Code tags will be appeared in green, and with monospaced characters. I have used the Quote TAG in the first line of this post for qouting your question, and here are an example for the Code tag:

Code: Select all

It is a code like text.
Last edited by Zizi64 on Sun Jul 01, 2018 7:02 pm, edited 3 times in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Manipulate Sheets in a Calc document

Post by Zizi64 »

In the Function - What does this line mean ? "sURL = private:factory/scalc".
"sURL" is the name of the variable. The sURL is a STRING type variable. The starting letter (s) is a convention, but it does not specify the type of the variable.
You must use the

Code: Select all

Dim sURL as string
if you use the

Code: Select all

option explicit
in the first code line of the Module.

Here are some information about the parameter "private:factory/scalc":
https://www.openoffice.org/api/docs/com ... oader.html
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Arun P. Pai
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

[Solved ]Re: Manipulate Sheets in a Calc document

Post by Arun P. Pai »

Dear Sir,

Noted your reply to my queries, will surely check out the link given by you.

Very interesting, must also catch with my reading of Mr. Andrew's book, but I think it is going to take life time to complete.

Thank you very much for spending your valuable time to clear my doubts.

Regards,

Arun.
Arun
OS - Windows 8.1
Open Office 4.1.5
Post Reply