Page 1 of 1

Save and Exit macro with prompting dialog box

PostPosted: Mon Feb 12, 2018 5:43 pm
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?

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

PostPosted: Mon Feb 12, 2018 7:07 pm
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.

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

PostPosted: Tue Feb 13, 2018 3:11 pm
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.

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

PostPosted: Tue Feb 13, 2018 3:26 pm
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.

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

PostPosted: Tue Feb 13, 2018 8:13 pm
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 ... me_Library)
https://forum.openoffice.org/en/forum/v ... =5&t=20557

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

PostPosted: Tue Feb 13, 2018 8:17 pm
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...

Re: Save and Exit macro with prompting dialog box

PostPosted: Thu Feb 22, 2018 7:15 pm
by UnklDonald418
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