Save and Exit macro with prompting dialog box

Save and Exit macro with prompting dialog box

Posted: 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

Posted: Mon Feb 12, 2018 7:07 pm

by UnklDonald418

Go to Andrew Pitonyak's web sitehttp://www.pitonyak.org/oo.phpDownload 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

Posted: Tue Feb 13, 2018 3:11 pm

by LukeSVK

UnklDonald418 wrote:Go to Andrew Pitonyak's web sitehttp://www.pitonyak.org/oo.phpDownload 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

Posted: 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 IfEnd 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):

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

Posted: 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

Posted: 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

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