[Solved] How to run a Writer macro in a Calc macro?

[OldStd]

Code: Select all

sub SaveWriter8FileAs
Dim FileProperties(0) As New com.sun.star.beans.PropertyValue
Dim s as String
Dim Url As String
oDoc2 = StarDesktop.CurrentComponent
rem test_03.odt saved as test_05.odt
s = "G:\OO_Dev_Guide\macros\Write_macros\test_05.odt"
Url = convertToURL(s)
FileProperties(0).Name = "Overwrite"
FileProperties(0).Value = True
oDoc2.storeAsURL(Url, FileProperties())
end sub

[Zizi64]

oDoc2 = StarDesktop.CurrentComponent

Why you get the documents again and again? You have got the oDoc2 into as a global variable at the begin of the routines...

(I clearly understand only that three routine, quoted above. All of the other routines of your macro (with the dispatcher) are confusing for me. I can not modify them to a clear, API based code.)

[Zizi64]

For more clarification:

Thanks for the comments. I meant to save oDoc2.
Codes modified as follows:


sub SaveWriter8FileAs
Dim FileProperties(0) As New com.sun.star.beans.PropertyValue
Dim s as String
Dim Url As String
oDoc2 = StarDesktop.CurrentComponent
rem Saving test_03.odt as test_05.odt
s = "G:\OO_Dev_Guide\macros\Write_macros\test_05.odt"
Url = convertToURL(s)
FileProperties(0).Name = "Overwrite"
FileProperties(0).Value = True
oDoc2.storeAsURL(Url, FileProperties())
end sub

When your SaveWriter8FileAs routine is called, the oDoc2 variable CONTAINS the desired document (because you got it before). Not needed get it again by the emphasized line.

[OldStd]

Hi. Zizi46.
Thanks for the reply.
I am equally confused.
It is because I don't know how to [reference/activate/bring to the front] the loaded global variables oDoc1 and oDoc2.
I did the call
Quote
oDoc2 = StarDesktop.CurrentComponent
Unquote
because when I replace the [document] from the recorded codes with [oDoc2] inside the sub routine, a runtime error occurs saying the variable is not defined, or something to that effect. Let me see if I can do it in some other way, before I learn how to write the same codes using the API approach.
I don't have enough knowledge of the OOBasic to overcome this.
The three routines you mention are copied from someplace in the Internet and modified. Haha.
I am trying to read through some basic guide from the tutorials/dev guide I can lay my hands on.
Hopefully, after a long while, I am able to write some clean API codes.

[OldStd]

Success finally

Hi. Zizi46 and Everyone.

Thanks for all the advice given in solving my problems.

A. In this exercise, I learn
I. How to load 2 Open Office documents using a Macro from a launching document, any Calc workbook or Writer text document.
II. Using Macros stored in the [My Macros] to run both Writer macros and Calc macros in the 2 respective documents
III. Close the two loaded documents, 1 Writer Doc, and 1 Calc workbook at the end of the program.
IV. Resolved the issue of the OpenOffice crashing for unexpected problems just before the opening of the ".uno:BulletsAndNumberingDialog"
a.

Edit: The solution lies in pausing the processing by one tenth of a second (wait 100) just before the dialog is called.

B. I also learn
I. How to declare and use global variables
II. The difference between the documents and their frames
a. I note that in each of the recorded macros they make use of local variables [document] and the subsequent frames [document = ThisComponent.CurrentController.Frame].
b. So I take advantage by declaring 2 global objects oFrame1 and oFrame2, remove all the statements [dim document] and [document = ThisComponent.CurrentController.Frame]. from all the macros and substitute the parameter [document] with oFrame1 and oFrame2 respectively.
c. In some places, I have to replace document, or ThisComponent with oDoc1 and oDoc2, respectively.

While it took a lot of my time, I enjoy the learning.

[Zizi64]

Just a tip: if you use the global variables extensively, thew you can make the "dispatcher" to a global variable too at the beginning of the macro.
In this case not needed get the dispatcher again and again by the repeated line:

Code: Select all

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

And a second tip:
If you want study the properties and methods of the API objects "in situ", then you need at least one of the useful object inspection tools. Intalll the Xray or the MRI:
viewtopic.php?f=20&t=86461
https://wiki.openoffice.org/wiki/Extens ... X-Ray_tool

MRI:
https://extensions.openoffice.org/project/MRI
https://extensions.libreoffice.org/exte ... tool/1.1.2

Xray:
http://berma.pagesperso-orange.fr/index2.html
(documentation and the macro code are in one downloadable .odt file)

[OldStd]

Hi. Zizi64

Thank you for your tips.
I have copied the URLs to my PC for easier reference and browsing.
After reading all these and trying out the extension tools I am sure I would love to use the OpenOffice more.

[OldStd]

writing to and from clipboard

Right from day 1, Tibor has been encouraging me to forget about recording macro and learn to write in API.

I am taking up his advice and start learning such coding.

My current application consists of, in the first part, copying data from 5 worksheets and pasting the contents onto the Sheet 6 in the same workbook.

For this part, I can find the copyRange syntax and after some practice, I think I can manage to learn this.

The second part consists of copying the entire sheet 6 to the clipboard and then paste from the clipboard to the writer document using paste special RTF. The syntax [uno:copy] is quite different from the copyRange syntax because no destination range is specified.

The dispatcher service did it for me when I recorded the macro.

I have been trying all night to look for the syntax on how how to copy cells from a CALC worksheet onto the clipboard without success.

There were some discussion in some forum on how to paste from the clipboard to the document but no conclusive answer was provided.

Can someone please help me with some URL where I learn how to do this in API codes?

Best regards.

Ps:

Shall I start a new thread on this topic on clipboard?

Quote
Just a tip: if you use the global variables extensively, thew you can make the "dispatcher" to a global variable too at the beginning of the macro.
In this case not needed get the dispatcher again and again by the repeated line:
CODE: SELECT ALL   EXPAND VIEW
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

The above is implemented. Thanks.

[Zizi64]

Frow Andrw Pitonyak's free macro programming book (OOME version 4):

11.3. Dispatch failure – an advanced clipboard example
The request was easy, copy an entire Writer document to the clipboard. The macro recorder very quickly provided a solution. Unfortunately, the macro fails in LO when called from a button added to the document; but it works from the IDE and from AOO when called from a button. The solution is to set the focus on the document, before executing the dispatches.
Listing 248. Select all and copy using dispatches.

Code: Select all

Sub CopyToClipboard_Dispatch
  dim document   as object
  dim dispatcher as object
  document   = ThisComponent.CurrentController.Frame

  ' This next line was NOT added by the macro recorder.
  ' Without the next line, this fails in LO when called from a button.
  document.ContainerWindow.setFocus

  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())
  dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
End Sub

The API is more stable than the dispatches and works where a dispatch may fail. Another example where the API may be required is when the macro is invoked from LibreOffice started in headless mode; a topic not otherwise discussed in this document.
Listing 249. Select all and copy using the API.

Code: Select all

Sub CopyToClipboard_API()
  Dim o         ' Transferable content.
  Dim oClip     ' Clipboard service.
  Dim oContents
  Dim sClipName As String 

  ThisComponent.CurrentController.Select(ThisComponent.Text)
  o = ThisComponent.CurrentController.getTransferable() 

  sClipName = "com.sun.star.datatransfer.clipboard.SystemClipboard"
  oClip = createUnoService(sClipName)
  oContents = oClip.setContents(o, null)
End Sub 

11.4. Conclusion
The dispatch commands are powerful and require little knowledge of the internal workings of LO. Although some functionality, such as the Undo command, can only be used in the dispatcher, macros that will be used for a long time are better off using the internal objects directly.

http://www.pitonyak.org/oo.php

[Zizi64]

...but im am not sure if you need the clipboard to copy some cell content onto and another sheet. it is depend on: what you want to copy? The formatting properties and the styles? The formulas? Or only numbers, dates, strings?
Maybe the GetDataArryaa(), SetDataArray() is enough for you.
viewtopic.php?f=20&t=88259

Andrew Pitonyak:

15.5.5. Copy data between documents
The copyRange method only supports copying a range in the same document. Other methods must be used to copy data between different documents.
Data functions
Use getData() and setData() to copy numbers. Use getDataArray() and setDataArray() to copy numbers and strings. The data functions are fast and easy, but they only copy data, not formatting.

[OldStd]

Thank you Tibor for your tips.

I need to copy the entire sheet from the Calc sheet and paste to next sheet as part of a sheet as formatted cells, and later an entire sheet to be pasted into a writer document as a table. I need to experiment as to which method to use.

I am getting out of the house most of the day today and will try it out later tonight.

In the meantime, are there any equivalent to the home cell and special last cell, or count of row, count of column in a range, and an entire sheet in the Calc,
and how do we move from row to row, column to column in a Calc sheet, or line to line, in writer doc, first line, and last line in a page, and how do we address an entire page?

Best regards.

[Zizi64]

In the meantime, are there any equivalent to the home cell and special last cell, or count of row, count of column in a range, and an entire sheet in the Calc,

If I understood it exactly: you want to get "rectangle" of the the used cellrange by your macro in a Sheet.

Please read Andre Pitonyak's books and documentations. There is an another with a title: AndrewMacro.pdf. Here is a sample based on the information of this document, how to get the number of rows and columns of the used area of a sheet:

Code: Select all

REM  *****  BASIC  *****

Function LastRowOfUsedArea(lSheetNumber as long, optional oDoc as object) as long 
rem The sheet numbers are Zero based values in the macros.

 Dim oSheet as object
 Dim	oDocument as object
 Dim oCursor as object

	rem Here is a sample for the handling of the "optional passed paramater":
	If IsMissing( oDoc ) Then
		oDocument = ThisComponent
	else oDocument = oDoc
	EndIf
	rem End of checking of the optional parameter. The function got the passed document  or "this" (the actual) one.
	
	oSheet = oDocument.Sheets(lSheetNumber)
	oCursor = oSheet.createCursor
	oCursor.gotoEndOfUsedArea(False)

 LastRowOfUsedArea = oCursor.RangeAddress.EndRow

End function


Function LastColumnOfUsedArea(lSheetNumber as long, optional oDoc as object) as long 
rem The sheet numbers are Zero based values in the macros.

 Dim oSheet as object
 Dim	oDocument as object
 Dim oCursor as object

	rem Here is a sample for the handling of the  "optional passed paramater":
	If IsMissing( oDoc ) Then
		oDocument = ThisComponent
	else oDocument = oDoc
	EndIf
	rem End of checking of the optional parameter. The function got the passed document  or "this" (the actual) one.
	
	oSheet = oDocument.Sheets(lSheetNumber)
	oCursor = oSheet.createCursor
	oCursor.gotoEndOfUsedArea(False)

 LastColumnOfUsedArea = oCursor.RangeAddress.EndColumn

End function

Getlast.ods

(10.67 KiB) Downloaded 124 times

[OldStd]

Thank you Tibor for the tips.

I have downloaded the sample codes, in Getlast.ods, and also AndrewMacro.pdf, and see what I can make out of them.

Best regards.

[OldStd]

Hi. Tibor and everyone.

From the sample codes from AndrewMacro.pdf, I have made a small application to copy contents from 5 sheets and paste them on top of one another into a sheet 6, using the API approach. The codes work for both getCellRangebyName and getCellRangebyPosition.

I made use of 2 functions to get the last row and last column of used cells and the procedure for copyRange.

Thanks for the tips.
Best regards

[Zizi64]

They are quite different from the recorded macros

Yes.
And you can work more effective with them, and they can run faster.

[OldStd]

Quote
11.4. Conclusion
The dispatch commands are powerful and require little knowledge of the internal workings of LO. Although some functionality, such as the Undo command, can only be used in the dispatcher, macros that will be used for a long time are better off using the internal objects directly
Unquote

I need time to learn more about this. Where can I find more example on this?

In the meantime, I use the dispatcher to copy a Calc worksheet and paste special in RTF to a writer document.
The part on the [copy] is adapted from an example from AndrewMacro.pdf, and the part on [paste special] is adapted from the recorded macro.

The codes work.

Thanks and regards.

[Zizi64]

Where can I find more example on this?

On the World Wide Web.

You can search
- In this english forum, and in the related locale forums
- in Andrew's documents
- on the Wiki pages
- on the official site of the AOO and LO
- in the API descriptions
- In the HELPs of the office suites and the built-in StarBasic
- and on many-many other sites...

[OldStd]

Hi. Zizi46 and everyone.

I have managed to convert my recorded macros into something that resemble API codings.

However, there are a number of operations that I don't know how to code in API codes without resorting to the use of dispatcher commands. They are the following commands:

.uno:Copy
.uno:ClipboardFormatItems
.uno:ConvertTableToText
.uno:SelectAll
.uno:BulletsAndNumberingDialog
.uno:IncrementIndent
.uno:DefaultNumbering

At this stage od my leaning, I don't know how to look for material to do the converting.

Any guidience here would be mcuh appreciated.

Best regards.

Ps:

I am attaching the modified codes and modified, simpler, sample working sheets and writer document to illustrate the application. It runs much faster than the original recorded macros.
The macros are found in 2 modules inside the calc workbook:
Running_writer_macros_in_calc_macro.ods

[Zizi64]

Step 6
Apply indenting of paragraphs based on the number of # found at the start of the paragraphs. One # equals one indent.

Apply predefined (or newly created) paragraph styles instead of the direct formatting. Use an .ott template with the predefined styles.

[Zizi64]

However, there are a number of operations that I don't know how to code in API codes without resorting to the use of dispatcher commands.

.uno:SelectAll

Here is a sample based on the code of this topic:
viewtopic.php?f=21&t=78814

Code: Select all

REM  *****  BASIC  *****

Sub Sel_All()

 Dim oDoc as object
 Dim oCursor as object
 Dim oText as object
 
	oDoc = ThisComponent
	oText = oDoc.Text
	oCursor = oText.createTextCursor()
	oCursor.gotoStart(false)
	oCursor.gotoEnd(true)
	oDoc.CurrentController.select(oCursor)
	
End Sub

select all.odt

(18.99 KiB) Downloaded 135 times

[OldStd]

Hi. Tibor.
Quote
Sub Sel_All()

Thanks for the codes.
Hope to use this in my future coding.

[OldStd]

Hi. Tibor.

Apply predefined (or newly created) paragraph styles instead of the direct formatting. Use an .ott template with the predefined styles.

Thanks for the tips.

In the past few days, I was struggling to learn how to translate some of the recorded macros to the equivalents in API coding. I did learn something out of the activities.

Next, I'll start learning something new, such as making use of the templates, and style, topics completely new to me for the time being.

Hope I can learn the basics soon.

I'll then use the same example, ans see if I can produce a different way to solve the same problem.

Thanks for your patient guidance.

[OldStd]

Hi. Tibor and Everyone.
For those of you who are still following this discussion.
I was bothered by the tedious work of having to format a multi-level numbering list with 248 indents because I started with a 5 page document as unformatted text.
To push the indenting button manually 248 times took me about 18 to 20 minutes.
After some basic tutorials in lists and headings, I noted the following:
1. The [levels] in [headings] are linked to [levels] of [numbering list].
2. Most of the levels in my sample document is that of [level 3], with some 25 [level 2] numbers and a handful of [level 4] and [level 5] numbers.
3. There are spacing between paragraphs in [Text body indent] as well as in list.
4. That I could modify some aspects of the numbering and bullet styles in the list and store the style in a template for future use.
With that I open the new template, pasted the text into it, select all, apply the style, and press [Ctrl 3] to turn every paragraph in a [level 3] paragraph.
After that, I press [Ctrl 1] & [Ctrl 2] to take care of [level 1] and [level 2] paragraphs and apply the new style again.
Finally, I adjust the odd [level 4] and [level 5] paragraphs and remove numbering for the beginning paragraphs and the last that don't require numbering
The whole process now takes about 5 to 7 minutes only.
Although there isn't much time saved, it is much more pleasant and much less tedious than pushing the indenting button 248 times, and having to remove the numbers or bullets in the empty lines between the paragraphs.
I hope some of you can show me a better way to handle reformatting of the list numbering.
Of course, one other way is to switch on the style before typing the document and pushing the indenting button as we type, if we happen to be the author.

[OldStd]

Hi. Tibor and everyone.

In the following codes, I am trying to select all the text and apply the predefined style contained in a template.
I recorded the macro that makes use of [.uno.StyleApply].
How do I go about learning to use the [internals] in the place of

Code: Select all

oDisp.executeDispatch(oSel, ".uno:StyleApply", "", 0, args2())

Thanks & regards.

Code: Select all

sub Apply_template_style (oSel as object)
rem oDisp.executeDispatch(oFrame2, ".uno:SelectAll", "", 0, Array())
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Template"
args2(0).Value = "Test_Numbering_01"
args2(1).Name = "Family"
args2(1).Value = 16
oDisp.executeDispatch(oSel, ".uno:StyleApply", "", 0, args2())
end sub

function fnSel_All as object
Dim oSels As Object, oSel As Object
Dim oCursor as object
Dim oText as object
rem Doc2 was declared as global variables elsewhere
oText = oDoc2.Text
oCursor = oText.createTextCursor()
oCursor.gotoStart(false)
oCursor.gotoEnd(true)
oDoc2.CurrentController.select(oCursor)
oSels = oDoc2.getCurrentSelection()
oSel = oSels.getByIndex(0)
fnSel_All = oSel
End function

[Zizi64]

viewtopic.php?f=20&t=53581
http://ooo-forums.apache.org/en/forum/v ... 20&t=93911
https://michal.kosmulski.org/computing/ ... acros.html
and more...

...and Pitonyak's books - for ever.

[OldStd]

Hi. Tibor.
Thanks for the tips and the URLS.

I will take time to go over them.

Another question:
I tried to run some macros from the opened template, so that I don't have to come back to the launching worksheet, and I found that I couldn't do it, because the new document opened from the template doesn't have adequate level of macro security assigned.
Question: How do I assign macro security to such a document?
If I saved that template as an .odt document, and reopened it, the macros stored in it could run, because the macro security in the document was set to run all macros (not recommended)

Best regards.

[OldStd]

Hi. Tibor.

I found this code sample in a search in this forum.
Is it possible to rewrite the codes without using the dispatcher and UNO command?

Code: Select all

sub ChStyle001
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Template"
args1(0).Value = "ChStyle001"
args1(1).Name = "Family"
args1(1).Value = 1

dispatcher.executeDispatch(document, ".uno:StyleApply", "", 0, args1())

end sub 

[Zizi64]

If you want to modify some properties of an existing paragraph style:
https://ask.libreoffice.org/en/question ... roperties/

[OldStd]

Hi. Tibor.

Using the example from your tip/post, I tried to adapt the codes to my example.

I still got stuck trying to apply the template style to the selected document.

Is there any way to replace [.uno: StyleApply], using the dispatcher, with API codes?

I seached the eBooks and the web, but couldn't find anything.

Code: Select all

Sub ChangeDocStyle
dim oFamilies as object, oStyle as object, oSel as object
oSel = fnSel_All(oDoc2)
oFamilies = oDoc2.StyleFamilies.getByName("NumberingStyles")
oStyle = oFamilies.getByName("Test_Numbering_01")
msgbox oStyle.name

rem This is as far as I can go
rem How do I apply oStyle to oSel, or oFrame2 ???

End Sub

[Zizi64]

Apply Paragraph Styles.odt

(29.97 KiB) Downloaded 122 times

Created and tested in LibreOffice 6.1.1