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

[OldStd]

[Solved 2018-09-21]How to run a writer macro in a calc macro?

I have some experience in Microsoft Excel VBA but very new in OOBase.

I have recorded some macros in calc by dividing a text document into 5 pages, inserting some cells to the right to make indents. I then combine the 5 pages back to 1 page again in the 6 th page.
Next I copy this page 6 from the calc worksheet, open a blank writer doc and paste special into the writer page in rtf formatted page.
Then I select the whole page and format the page with number listing with levels.
Next I use the number of # to give the indents.
The calc macros work in the calc worksheet. The writer macros work in the writer doc.
Question: How to run all the calc macros and the writer macros in a combined macro in the calc worksheet?
I tried it many times but it macro broke due to unexpected errors.

The program broke down at this line of the code:(Within the sub RunWriterSubs)

Code: Select all

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:BulletsAndNumberingDialog", "", 0, Array())

I hope someone can help me resolve this.

Code: Select all

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

rem ---Step 1 -------------------------------------------------------------
run_calc_subs

rem ----Step 2-------------------------------------------------------------
 RunWriterSubs

end sub

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

dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Count"
args2(0).Value = 2
args2(1).Name = "Select"
args2(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectTable", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Delimiter"
args3(0).Value = "#"

dispatcher.executeDispatch(document, ".uno:ConvertTableToText", "", 0, args3())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:ConvertTableToText", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:BulletsAndNumberingDialog", "", 0, Array())


end sub

[Zizi64]

You can control more than one documents by a macro code (even if they are related to different applications). But you must WRITE your macro code based on the API functions instead of the usage of the RECORDed macros.

[OldStd]

Hi. Zizi64.
Thanks for your response.
I have just started learning OOBase a few days ago and didn't make much progress other than recording a few macros without much significant use and a few amendments to the recorded codes.
I would appreciate you or anybody else showing me the links to tutorials on learning how to write codes based on API functions.
In the meantime, I have uploaded the codes I use to run in the calc & writers docs.
Integrating_calc8_wrter8_macros.pdf (9 pages long)

Please suggest what few lines of API codes that can be added to make this work.

Code: Select all

sub Integrating_calc8_wrter8_macros
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 ---Step 1 -------------------------------------------------------------
run_calc_subs
rem ----Step 2-------------------------------------------------------------
RunWriterSubs
end sub

Sub run_calc_subs
CopyAllDestSheet
LoadEmptyWriterDoc
End sub

Sub RunWriterSubs
rem ----Step 3-------------------------------------------------------------
PasteClipboardAsTable
rem ----Step 4-------------------------------------------------------------
indent_all_lines
rem ----Step 5-------------------------------------------------------------
remove_all_bullets_Numbers
rem ----Step 6-------------------------------------------------------------
SaveWriter8FileAs
End sub

[Zizi64]

My first tip: Download anr study Andrew's Pitonyak's free macro books.
http://www.pitonyak.org/oo.php

CopyAllDestSheet

Which cellrange do you want to copy? With or without the formatting properties?
https://wiki.openoffice.org/wiki/Docume ... and_Ranges

LoadEmptyWriterDoc

Use the LoadComponentFromURL() API function for this task
https://wiki.openoffice.org/wiki/Docume ... tarDesktop

PasteClipboardAsTable

It is not a simple task...
viewtopic.php?t=9981

indent_all_lines

Use a Style instead of the direct adjusting the indent value.

remove_all_bullets_Numbers

Use the Styles...

SaveWriter8FileAs

USe the StoreToURL() or the StoreAsURL() API function.
https://wiki.openoffice.org/wiki/Saving_a_document


There are excellent working examples in the linked books, and on this forum. Just search them.

You must GET two document objects by your macro oDoc1 (the spreadsheet) and oDoc2 (the Writer document), and you must reference them in all of the macro code. You can pass the document objects to an another routine if it is needed. The thisComponent function always will get the actual document where the macro was launched. Therefore you must get the loaded (new) write document by the LoadComponentFromURL() function.
I suggest you: do not load an empty, new document from the default template, but load it from your well designed template - what contains the required paragraph styles and other styles.

[OldStd]

Hi. Zizi64.
Thank you so much for your point by point extensive tutorial.
I really appreciate it.
I am out of the house now and will look up the materials when I get home later.
Best regards.

[OldStd]

Hi. Zizi64.

The problem is basically resolved by adopting some of your suggestions as follows:
1. Use load an existing document instead of an empty one.
2. Load two documents, the spreadsheet as the source and the writer doc as the destination page.

The other suggestions take more time to learn as I work through the topics later.

Thanks and regards

[OldStd]

For those who are following this post, I have made changes / added the following subroutines:

Code: Select all

sub LoadExistingWorkbook
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
dim TestDoc1 as object

rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

REM edit the file name as needed
    Target = "G:\OO_Dev_Guide\macros\Spreadsheet_macros\Test_01.ods"
    TargetURL = convertToURL(Target)
    TestDoc1 = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())

	Combine_all_sheets
	
end sub

sub LoadExistingWriteDoc
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
dim TestDoc2 as object

rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

REM edit the file name as needed
    Target = "G:\OO_Dev_Guide\macros\Write_macros\Test_03.odt"
    TargetURL = convertToURL(Target)
    TestDoc2 = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())
	
end sub

Sub run_calc_subs
LoadExistingWorkbook
CopyAllDestSheet
LoadExistingWriteDoc
End sub

[Zizi64]

If I Understand it exactly: This is the main routine in your macro:

Code: Select all

Sub run_calc_subs
LoadExistingWorkbook
CopyAllDestSheet
LoadExistingWriteDoc
End sub

- Then you need use FUNCTIONS for the sub tasks if you want to pass the document objects to an another Function/Sub. The result of the loading function will be a DOCUMENT object.
- or you must use Global variables for the document objects. The global variables are valid in all of the procedure, in all of the modules of the Library.
- or you need load them in the main routine. Then the object variables will be valid in the only one Sub.

And you do not need the dispatcher for the loading Functions/Subs:

Code: Select all

Function LoadExistingWorkbook() as object

dim Target as string
dim TargetURL as string

    Target = "G:\OO_Dev_Guide\macros\Spreadsheet_macros\Test_01.ods"
    TargetURL = convertToURL(Target)
    
LoadExistingWorkbook = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())

REM you can pass more informations/parameters in the Array(): ...a password, when the file is password protected, of if you want to open it hidden...    
end sub

usage of the function in the main Sub:

Code: Select all

REM ...
oDoc1 = LoadExistingWorkbook
REM do something with the oDoc1: you can get a sheet of the oDoc1, and select, copy the content...
REM ... 

[OldStd]

Hi. Tibor Kovacs, Hungary.
Thanks for your coaching.
I have yet to learn how to define the global variable in OOBase.
I can try the FUNCTION idea later tonight.
Thanks for your assistance, sir.

Best regards.

[robleyd]

See Scope and Life Span of Variables - a lot more documentation is linked from there.

[Zizi64]

I have yet to learn how to define the global variable in OOBase.

OOBase???

The question was related to the AOO Calc and Writer Macros, to the StarBasic and to the API functions, but not to the Base application, what is a database management application.

[OldStd]

Sorry. I got confused with the terms.
I thought for a while OOBase stands for OpenOffice Basic, something like VBA in the Microsoft Office.
I have not even heard of a Database for the OO.

[OldStd]

Hi. David.
Thanks for the link on the "scope and life span of variables"

[OldStd]

Hi. Zizi64.

After some struggling, I was able to rewrite the codes, to make use of the oDoc1 as the output of the function LoadExistingWorkbook as an input parameter in every subroutine in the group under [Sub run_calc_subs]

Sub run_calc_subs
oDoc1 = LoadExistingWorkbook
combine_all_sheets (oDoc1)
CopyAllDestSheet (oDoc1)
End sub

I haven't touched the other parts of the codes, except that [dim document as object] is moved to the input parameter as (oDoc1 as object) and [document = ThisComponent.CurrentController.Frame] is moved inside the function as [oDoc1 = ThisComponent.CurrentController.Frame] once only.

I need some time to learn how to replace the auto generated codes for actions like insert, copy, paste, gotostart, etc in the API manner.

Thanks for the tips so far.

[OldStd]

Hi. Everyone.

Now it works and now it doesn't

The reason I changed the status from [Solved] to [Not yet solved] is that although the program can run correctly for a first time early in a fresh day, subsequent running of the program either cause the OpenOffice to hang, or will stop after the Calc table copied from the spreadsheet to the writer document after the table was converted to text without the multi-level numbering is completed.
The error is as follows:

Quote
OpenOffice Document Recovery

Due to an unexpected error, OpenOffice crashed. All the files you were working on will now be saved. The next time OpenOffice is launched, your files will be recovered automatically.

The following files will be recovered:
Calc_Macro_Launching.ods
Test_01.ods
Test_03.odt

Unquote

After the recovery, with all the spreadsheet and writer documents open, if I run the macros in the writer documents where it stopped before, I was able to complete the application.

I thought the problem might be corrected by bringing the selected writer document to the front. Unfortunately, although I have got hold of some codes, I can't make the codes work.

I don't know what question to pose to search for an answer either in google or in this forum.

Some ideas on how to search, what key words to use, or any straight out explanation would be much appreciated.

Best regards, always.

[Zizi64]

please upload sample files and the full macrocode here.

[OldStd]

Hi. Zizi46.

I have changed the original macros beyond recognition.
I need to restore them and after that I'll upload them again soon with a sample text file that the macros are processing on.

Thanks and regards.

[OldStd]

Hi. Zizi64.

A. Attached in the zip file are the following OO docs.
I. Sampletext.odt. (The raw data) (No macros)
II. Test_01.ods (The source workbook) (With macros)
III. Test_03.odt (The target writer doc) (No macros)
IV. Calc_Macro_Launching.ods (The integration macros) (With macros)
V. Test_05.odt (The final result) (No macro)
VI. The code sheet for the macro group Integrating_calc8_writer8_macros.txt

B. Processing procedures
I. The contents from Sampletext.odt are copied and pasted into Sheet1 of Test_01.ods.
II. Sheet1 is then borken into 5 smaller sheets, Shet1 to Sheet5, manually, with the aid of the maco Move_data_to_new_sheet.
III. Sheet1 to Sheet5 are then indented with the macro Cells_to_right
IV. After this the 5 sheets are recombined into a sheet like Sheet6 again using the macro Combine_all_sheets.
a. The above macros are stored in Test_01.ods
V. Calc_Macro_Launching.ods macros
a. load the the docs Test_01.ods (calc) and Test_03.odt (writer)
b. and transfer the text from the spreadsheet to the text doc and reformat the text into a multi-level numbering list.

C. Comments
I. The whole process is probably taking more time than manually doing the indenting, but it does give me an opportunity to learn some OOBasic macros recording and codes writing.

Best regards.

[OldStd]

Hi. Everyone.
In my hurry to upload the codes, I didn't realize that I have somehow deleted a few lines of codes from some subroutines, probably because I put the codes in the spreadsheets for editing, and as I selected a few sheets in a group, deleting some codes from one sheet was also transmitted the delete to other sheets without my realizing the fact.
I have to go over the codes and resubmit the amended codes later.
Sorry for the inconvenience caused.

[Zizi64]

All of the differently indented paragraphs (generally: all of the text) have same paragraph style in your target file named test_05.odt. (It is the "Default" paragraph style.) It is a very ineffective approach of the task. Use the styles.

Predefine the styles in a "template" document - associated to the Outline Numbering, if it is necessary -, load that template with your macro, and apply the existing styles by the macro to the copied paragraphs.

[OldStd]

Hi Zizi46

Thank you for your suggestion on using style to format the text document.

Unfortunately, I have not yet made use of any templates either in Microsoft word or OO Writer.

In any case, the example I use is one of a kind and can hardly be used a template, I think?

All the indenting were done manually, as I can not think of a way to make it into a formula.

Glancing through the few eBooks I have downloaded so far didn't help much as there were not much mention of style for outline levels or how to name the levels. I need to dig further in the Internet or this forum, now that I know the direct to search, which is a great help.

In the meantime, I had [repaired] my codes by recording some macros where they were damaged earlier.

The same problem still persisted. That the codes worked some of the time and crashed for unexpected errors at other times.

To paraphrase the problem in other words, it is precisely that part of codes dealing with the style that break down from time to time.

After the table was copied from the spreadsheet, pasted onto the writer document, with the table converted to plain text, and the multi-level numbering style (default) didn't happen as the Open Office somehow crashed for unexpected problem and I have no way to find out what that problem is, as today, the program ran successfully for a number of times, not just once as before.

Hopefully, when I finally learned how to use the outlining levels and the codes become more efficient things may turn out to be different.

Thank you for the lead ideas and the direction for new search.

Attached is text file for the amended codes in notepad.

Best regards.

[Zizi64]

Unfortunately, I have not yet made use of any templates either in Microsoft word or OO Writer.

A Template is very similar as a normal document, but it is saved into .ott format, instead of the .odt format.
OTT: open template text
ODT: open document text

When you open a template, the appared document will named as Untitled X, but not as the original template, because it have not any URL (you can not save back into the original place/file directly).

You can create all of desired styles in a template (in same way as in the normal documents you can to do it). A template maybe contains some textual content or maybe not - it is your choice.

[Zizi64]

Code: Select all

rem --- No.4. ---Finally, ------------------------------------------------								
sub Combine_all_sheets (oDoc1 as object)								
rem define variables								
								
dim dispatcher as object								
dim ct as integer								
dim startNum as integer								
								
rem get access to the oDoc1								
oDoc1 = ThisComponent.CurrentController.Frame

Code: Select all

rem ---No. 2 -------------------------------------------------------------								
sub Copy_from_sheet (ct as Integer, oDoc1 as object)								
oDoc1 = ThisComponent.CurrentController.Frame

I think, you had not understood the "passed document object as a parameter" method.
- You not need pass a document object, when you will get the document (again) in the called macro.
- Or: you not need to get the document again, when you have passed it at the calling of the function/procedure.

Code: Select all

global oDoc1 as object
global oDoc2 as object

- And you never need to pass a document object to a procedure/function if it is a Global variable. Just get it once (in the first called routine), and then reference it by same name in every function or procedure.)

[RoryOF]

I use Global variables for just as Zizi64 says: my globals remember if a certain macro has been run in an editing session. If it has, it remembers the last value selected in that macro, to offer that value for future uses of the macro within that editing session. If the offered value is not required, selection of a different value can be made and will be remembered.

[Zizi64]

Code: Select all

Target = "G:\OO_Dev_Guide\macros\Spreadsheet_macros\Test_01.ods"								
TargetURL = convertToURL(Target)								
TestDoc1 = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())								
TestDoc1 = ThisComponent.CurrentController.Frame								
LoadExistingWorkbook = TestDoc1	

You had just loaded a document from the URL into the variable TestDoc1, and you destroy it (rewrite it) with the Thiscomponent in the next line of the code. The referenced "Thiscomponent" always means the Document, where the macro was launched, but not the actually loaded one. (Because you can not launch a macro from a document before it loaded in...)
https://wiki.openoffice.org/wiki/Docume ... /Documents
And you must decide, what you want to return from the function: the Document, or a Document Frame.

If you want to return the Document:

Code: Select all

Target = "G:\OO_Dev_Guide\macros\Spreadsheet_macros\Test_01.ods"								
TargetURL = convertToURL(Target)								
TestDoc1 = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())								
rem TestDoc1 = ThisComponent.CurrentController.Frame								
LoadExistingWorkbook = TestDoc1	

If you want return the Frame of the loaded document:

Code: Select all

Target = "G:\OO_Dev_Guide\macros\Spreadsheet_macros\Test_01.ods"								
TargetURL = convertToURL(Target)								
TestDoc1 = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())								
TestDoc1Frame = TestDoc1.CurrentController.Frame		 rem !!!!! (or some similar code... Maybe you neet SET the controller...) !!!!!						
LoadExistingWorkbook = TestDoc1Frame	

[OldStd]

How to stop the display of dialog using the OOBasic codes?

Thank you for your explanation of the difference between the [Loaded document] and [the Frame of the loaded document ]

Before I learn up the template, I concentrate on amending the codes by using the global variables, the [Loaded document] and in some sub routines, [the Frame of the loaded document]

I was able to remove the following lines of codes from all sub routines making use of oDoc1 and oDoc2 apart from the initial calls for the two global variables, with the exception of a few sub routines, where I have to replace oDoc2 with oDoc2.CurrentController.Frame

Dim oDoc1 as object
Dim oDoc2 as object
oDoc1 = ThisComponent.CurrentController.Frame
oDoc2 = ThisComponent.CurrentController.Frame

This is the offending sub routine:

sub From_text_to_list
rem ----------------------------------------------------------------------
rem define variables
dim oDoc2Frame
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the oDoc2
oDoc2Frame = ThisComponent.CurrentController.Frame
rem oDoc2Frame = oDoc2.CurrentController.Frame ''' This caused an error
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(oDoc2Frame, ".uno:SelectAll", "", 0, Array())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(oDoc2Frame, ".uno:BulletsAndNumberingDialog", "", 0, Array())
end sub

Not only that, after a successful running once, this sub routine has consistently caused the program to crash.

However, after the crash if I run the subsequent macros including this one, I can complete the rest of the application. But I can only run the codes in the recovery, writer document opened earlier, and not from any other OO documents.

My guess is that this macro is running a dialog where I have to choose an option and this display of the dialog somehow causes the application to crash.

I can't find a way to allow the application to choose the first default option, disable the display of the dialog and quietly proceed with the rest of the macros.

In VBA for Excel there is a line code that would satisfy this, like
Application.DisplayAlert = False

I can't find an equivalent for this in OOBasic, as indeed someone said there is no such equipment.

How to overcome this?

[OldStd]

Hi. RoryOF.
Thanks for sharing on the use of global variables.

[Zizi64]

How to stop the display of dialog using the OOBasic codes?

Do not use the "dispatcher", and do not record your macros, but WRITE them - based on the API functions. Then you will able to control the showing/hiding the dialogs as you want it.


It is a right direction, because there is not any "dispatcher" calling, you have used API functions only:

sub LoadExistingWorkbook
Dim Target as string
Dim TargetURL as string

Target = "G:\OO_Dev_Guide\macros\Spreadsheet_macros\Test_01.ods"
TargetURL = convertToURL(Target)
oDoc1 = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())

end sub

The oDoc1 is an object type global variable. If you need same document object during all the procedures, then do not get the oDoc1 again in any other procedure or function (do not rewrite it). If you need a property, method or other sub-object thing of the oDoc1 object, then you can call it by usage the existing oDoc1 object.

[Zizi64]

And it is a right direction too (I just deleted the dispatcher related lines and the REM lines):

Code: Select all

sub LoadExistingWriteDoc
Dim Target as string
Dim TartgetURL as string

Target = "G:\OO_Dev_Guide\macros\Write_macros\Test_03.odt"
TargetURL = convertToURL(Target)
oDoc2 = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Array())
end sub

[Zizi64]

Code: Select all

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

Which document have you want to save? The "oDoc2" or some other one: "Doc"?