Save and Exit macro with prompting dialog box

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
LukeSVK
Posts: 3
Joined: Mon Feb 12, 2018 5:29 pm

Save and Exit macro with prompting dialog box

Post by LukeSVK »

Hello there all,

I will appriciate any help with my goal that I want to accomplish with my [.ods] spreadsheet.
I have one spreadsheet with two subsheets named: Pokladnicna_kniha and DataVal.
I want to make Button (in Pokladnicna_kniha subsheet) with name: Save and Exit that will save the file to specific location: C:\username\desktop with name based on Cell value from, DataVal subsheet. Cell location is "P1".

I have the button, I have the cell "P1" with dynamic updateing based on some data input in spreadsheet, but I don't have the major part, which is code that Button should trigger.

I already find some written macros on some Forums, but it runs into runtime errors.

Can you help anyone?
Apache OpenOffice 4.1.5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Open Office Save and Exit macro with prompting dialog bo

Post by UnklDonald418 »

Go to Andrew Pitonyak's web site
http://www.pitonyak.org/oo.php
Download his document "OpenOffice.org Macro document" and look at section "5.17. Saving And Exporting A Document"
You can also download his book "OpenOffice.org Macros Explained". Look at section "10.13. Creating a UNO dialog". Chapter 15 is devoted to Calc macros.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
LukeSVK
Posts: 3
Joined: Mon Feb 12, 2018 5:29 pm

Re: Open Office Save and Exit macro with prompting dialog bo

Post by LukeSVK »

UnklDonald418 wrote:Go to Andrew Pitonyak's web site
http://www.pitonyak.org/oo.php
Download his document "OpenOffice.org Macro document" and look at section "5.17. Saving And Exporting A Document"
You can also download his book "OpenOffice.org Macros Explained". Look at section "10.13. Creating a UNO dialog". Chapter 15 is devoted to Calc macros.
Thank you very much,

That pointed me to right direction.
Apache OpenOffice 4.1.5
LukeSVK
Posts: 3
Joined: Mon Feb 12, 2018 5:29 pm

Re: Open Office Save and Exit macro with prompting dialog bo

Post by LukeSVK »

I tried to study http://www.pitonyak.org/oo.php documents. I did find some way to accomplish part of what I want, but I it's out of my league to connect theese information and write code that do the thing I demand. In the addition I want also to Macro ask before saving and exiting, question: Do you really want to save and exit? with YES and NO Button.

This is the code that saves document, but I don't know correct way to add destination folder and get name of document from cell:

If (ThisComponent.isModified()) Then
If (ThisComponent.hasLocation() AND (Not ThisComponent.isReadOnly())) Then
ThisComponent.store()
Else
REM Either the document does not have a location or you cannot
REM save the document because the location is read-only.
setModified(False)
End If
End If


Here is another example with specified destination folder but not the part (get name of the file from specified Cell (which is P2 in DataVal subsheet in my case):

Dim args(0) As New com.sun.star.beans.PropertyValue
Dim sUrl As String
sUrl = "file:///c:/My%20Documents/test_file.sxw"
args(0).Name = "Overwrite" 'This property is defined in Table 107
args(0).Value = False 'Do not overwrite an existing document.
ThisComponent.storeAsURL(sUrl, args())

Does anyone know the correct way to add File name based on cell value to this code?

Thanks in advance.
Apache OpenOffice 4.1.5
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Open Office Save and Exit macro with prompting dialog bo

Post by Zizi64 »

In the addition I want also to Macro ask before saving and exiting, question: Do you really want to save and exit? with YES and NO Button.
Use the MsgBox() feature as a function. The return value depend on wich button you clicked on.

https://wiki.openoffice.org/wiki/Docume ... e_Library)
viewtopic.php?f=5&t=20557
Last edited by Zizi64 on Tue Feb 13, 2018 8:28 pm, edited 1 time 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: Open Office Save and Exit macro with prompting dialog bo

Post by Zizi64 »

Does anyone know the correct way to add File name based on cell value to this code?
it is depend on the method, how you want to get the cell containing the filename string. You can get the Document, and then the Sheet, and then the Cell, finally you can get the text content of the cell. You can get the sheet, and the cell by its Name, or by its index(es).

See Andrew pitonyak's macro books...
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.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Save and Exit macro with prompting dialog box

Post by UnklDonald418 »

Based on the strategy outlined by Zizi64 I came up with the following macro for you to test

Code: Select all

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

Sub SaveToFileAtP2

  Dim oDoc as Variant
  Dim oSheets As Variant
  Dim oSheet As Variant
  Dim oCell As Variant
  Dim sString As String
  Dim args(0) As New com.sun.star.beans.PropertyValue
 
  oDoc = ThisComponent  ' get current document
  oSheets = oDoc.getSheets()  'get the sheets container
  oSheet = oSheets.getByName("Sheet1")  'get a specific sheet by name
  oCell = oSheet.getCellRangeByName("P2")  ' get the cell by its name
'  Alternatively  oCell = oSheet.getCellByPosition(15, 1)
'  when accessing rows and columns by index they begin numbering at 0 so the index row 2 is 1
'  and the index of Column P is 15 (since P is the 16th letter of the alphabet) 
  sString = oCell.Text.getString()  ' get the contents of the cell as a string
  sPath = "file:///C:/My%20Documents/"  'the path where the file will be saved.
  sURL = sPath & sString & ".ods"  'create a complete URL for the file to be saved

If FileExists (sURL) then
      If   MsgBox ("Do You want to overwrite " & sURL , 4 OR 32, "Overwrite File") = 6 Then
		args(0).Name = "Overwrite" 'This property is defined in Table 107  pg 318 ooME
		args(0).Value = TRUE
      Else 
         Exit Sub
      Endif
Else
      If   MsgBox ("Do You want to Save " & sURL , 4 OR 32, "Save File") = 6 Then
		args(0).Name = "Overwrite" 'This property is defined in Table 107  pg 318 ooME
		args(0).Value = FALSE
      Else 
         Exit Sub
      Endif
Endif      
     	oDoc.storeToURL(sUrl, args())  'this saves the document but continues to display the original document
'       oDoc.storeAsURL(sUrl, args())  ' or this will save and open the new document
 
End Sub
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply