[Solved] Manipulate Sheets in a Calc document

Creating a macro - Writing a Script - Using the API

[Solved] Manipulate Sheets in a Calc document

Postby Arun P. Pai » Sun Jul 01, 2018 8:40 am

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   Expand viewCollapse view
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
Arun P. Pai
 
Posts: 13
Joined: Thu Apr 26, 2018 7:53 am

Re: Manipulate Sheets in a Calc document

Postby Zizi64 » Sun Jul 01, 2018 9:13 am

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



Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

[Solved] Re: Manipulate Sheets in a Calc document

Postby Arun P. Pai » Sun Jul 01, 2018 6:07 pm

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
Arun P. Pai
 
Posts: 13
Joined: Thu Apr 26, 2018 7:53 am

Re: Manipulate Sheets in a Calc document

Postby Zizi64 » Sun Jul 01, 2018 6:21 pm

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.png
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   Expand viewCollapse view
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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Manipulate Sheets in a Calc document

Postby Zizi64 » Sun Jul 01, 2018 6:28 pm

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   Expand viewCollapse view
Dim sURL as string

if you use the
Code: Select all   Expand viewCollapse view
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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

[Solved ]Re: Manipulate Sheets in a Calc document

Postby Arun P. Pai » Tue Jul 03, 2018 4:25 am

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
Arun P. Pai
 
Posts: 13
Joined: Thu Apr 26, 2018 7:53 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests