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?
Save and Exit macro with prompting dialog box
Save and Exit macro with prompting dialog box
Apache OpenOffice 4.1.5
-
- 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
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.
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Open Office Save and Exit macro with prompting dialog bo
Thank you very much,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.
That pointed me to right direction.
Apache OpenOffice 4.1.5
Re: Open Office Save and Exit macro with prompting dialog bo
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.
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
Re: Open Office Save and Exit macro with prompting dialog bo
Use the MsgBox() feature as a function. The return value depend on wich button you clicked on.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.
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.
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.
Re: Open Office Save and Exit macro with prompting dialog bo
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).Does anyone know the correct way to add File name based on cell value to this code?
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.
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.
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Save and Exit macro with prompting dialog box
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11