Save and Exit macro with prompting dialog box

Creating a macro - Writing a Script - Using the API

Save and Exit macro with prompting dialog box

Postby LukeSVK » Mon Feb 12, 2018 5:43 pm

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
LukeSVK
 
Posts: 3
Joined: Mon Feb 12, 2018 5:29 pm

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

Postby UnklDonald418 » Mon Feb 12, 2018 7:07 pm

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 subject line
Apache OpenOffice 4.1.5 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 741
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

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

Postby LukeSVK » Tue Feb 13, 2018 3:11 pm

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

Postby LukeSVK » Tue Feb 13, 2018 3:26 pm

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
LukeSVK
 
Posts: 3
Joined: Mon Feb 12, 2018 5:29 pm

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

Postby Zizi64 » Tue Feb 13, 2018 8:13 pm

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 ... me_Library)
https://forum.openoffice.org/en/forum/v ... =5&t=20557
Last edited by Zizi64 on Tue Feb 13, 2018 8:28 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.6 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.2 and 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: 6576
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Zizi64 » Tue Feb 13, 2018 8:17 pm

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; LO4.4.7, LO5.4.6 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.2 and 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: 6576
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save and Exit macro with prompting dialog box

Postby UnklDonald418 » Thu Feb 22, 2018 7:15 pm

Based on the strategy outlined by Zizi64 I came up with the following macro for you to test
Code: Select all   Expand viewCollapse view
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 subject line
Apache OpenOffice 4.1.5 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 741
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 10 guests