Page 1 of 1
[Solved] How to "Save As" in a Calc Macro
Posted: Fri Jun 27, 2008 4:36 pm
by bwm
Welcome beginner. Please answer all of the questions below which may provide information necessary to answer your question.
-----------------------------------------------------------------------------------------------------------
Which version of OpenOffice.org are you using? 2.4.1
What Operating System (version) are you using? Windows XP
What is your question or comment?
Can anyone help me write a macro to "save as" allowing me to specify the path + a value in a cell?
The file name would appear as c:/my documents/01trans061208.wks <<< The file name is derived from a value in a cell.
Re: How to "Save As" in a Calc Macro
Posted: Sat Jun 28, 2008 7:51 am
by squenson
Here is a macro that you should modify to your needs:
Code: Select all
Sub SaveAs
Dim sFileName As String
Dim sPath As String
Dim sSaveToURL as string
sPath = "C:\"
sFileName = thisComponent.getSheets.getByName("Sheet1").getCellRangeByName("F3").getString
sSaveToURL = ConvertToURL(sPath & sFileName)
thisComponent.storeToUrl(sSaveToURL, Array(MakePropertyValue("FilterName", "Calc8")))
End Sub
Function MakePropertyValue(Optional sName As String, Optional sValue) As com.sun.star.beans.PropertyValue
'-------------------------------------------------------------------
' Create and return a new com.sun.star.beans.PropertyValue
'-------------------------------------------------------------------
Dim oPropertyValue As New com.sun.star.beans.PropertyValue
If Not IsMissing(sName) Then
oPropertyValue.Name = sName
EndIf
If Not IsMissing(sValue) Then
oPropertyValue.Value = sValue
EndIf
MakePropertyValue() = oPropertyValue
End Function
Change the destination path and the reference of the cell containing your file name (in my example, it on Sheet1, cell F3)
Re: How to "Save As" in a Calc Macro
Posted: Mon Jun 30, 2008 8:46 pm
by bwm
This is great and it works the first time. After that the macro goes into error state on the line
thisComponent.storeToUrl(sSaveToURL, Array(MakePropertyValue("FilterName", "Calc8")))
When saving a file in Calc using "SaveAs", the file name changes to that new file name. When this macro routine runs, say from a Forms Button, the file is saved with the correct name and in the correct location. The file name does not change in the current sheet however as it would if you used the menu items manually.
As well, if you open the file created by the routine, and want to save it again with the Forms Button, the error occurs on the above listed line of the macro.
My original intent of this macro was to give a user a button to save the current spreadsheet using a naming routine and thus not corrupting the original data entry spreadsheet. If a change to the NEW sheet created by the macro is opened and needs to be changed, then the button would save the file again using the same name. The system would then show the standard "File exists, do you want to replace the file?" message.
We are so close! Please assist me one more time. Thanks.
Re: How to "Save As" in a Calc Macro
Posted: Wed Jul 02, 2008 11:47 am
by rmdemi
Hi,
storeToURL behavior for UI's export feature.
storeAsURL behavior for UI's "save-as" feature.
So yo use storeAsURL method.
I think, OOo doesn't supply
.wks format. 
Re: How to "Save As" in a Calc Macro
Posted: Wed Jul 02, 2008 12:43 pm
by bwm
That was it! Thanks so much it works now! This is a great forum.
Re: [Solved] How to "Save As" in a Calc Macro
Posted: Thu Jan 29, 2009 6:25 pm
by danharper
Hello,
This Macro doesn't seem to save in Microsoft format only rename the file with xls file extension as when I open it in Excel it says invalid format and returns loads of rubbish in the spreadsheet.
Any help on this would be greatly appreciated.
Cheers Dan
Re: [Solved] How to "Save As" in a Calc Macro
Posted: Thu Jan 29, 2009 7:33 pm
by FJCC
To export as an Excel document you have to set the Filtername to the correct value. The following is from page 636 of the Developer's Guide
(http://api.openoffice.org/docs/Develop ... Guide.pdf)
For exporting purposes, a filter name can be passed that triggers an export to other file formats.
The property needed for this purpose is the string argument FilterName that takes filter names
defined in the configuration file:
<OfficePath>\share\config\registry\instance\org\openoffice\Office\TypeDetection.xml
In TypeDetection.xml look for <Filter/> elements, their cfg:name attribute contains the needed
strings for FilterName. The proper filter name for StarWriter 5.x is "StarWriter 5.0", and the export
format "MS Word 97" is also popular. This is the element in TypeDetection.xml that describes the
MS Excel 97 filter:
<Filter cfg:name="MS Excel 97">
<Installed cfg:type="boolean">true</Installed>
<UIName cfg:type="string" cfg:localized="true">
<cfg:value xml:lang="en-US">Microsoft Excel 97/2000/XP</cfg:value>
</UIName>
<Data cfg:type="string">5,calc_MS_Excel_97,com.sun.star.sheet.SpreadsheetDocument,,3,,0,,</Data>
</Filter>
The following method stores a document using this filter:
Code: Select all
/** Store a document, using the MS Excel 97/2000/XP Filter
*/
protected void storeDocComponent(XComponent xDoc, String storeUrl) throws java.lang.Exception {
XStorable xStorable = (XStorable)UnoRuntime.queryInterface(XStorable.class, xDoc);
PropertyValue[] storeProps = new PropertyValue[1];
storeProps[0] = new PropertyValue();
storeProps[0].Name = "FilterName";
storeProps[0].Value = "MS Excel 97";
xStorable.storeAsURL(storeUrl, storeProps);
}
Re: [Solved] How to "Save As" in a Calc Macro
Posted: Tue Aug 25, 2009 3:05 am
by jsarao
Additional help...
I tend to use templates to start my documents (or spreadsheets, etc.). I would like to embed this macro in a template (mySStemplate.ots) and have it execute when I do a File -> Save As...
I've added this macro in the following way:
1. Menu -> Tools -> Macros -> Organize Macros -> OpenOffice.org Basic...
2. In the "Macro from" column, select mySStemplate.ots
3. Click the New button
4. Copy and paste the code segment (see above)
5. Menu -> File -> Save
QUESTION: How do I associate with File -> Save As... operation or does that happen automatically the next time I open the file?
Re: [Solved] How to "Save As" in a Calc Macro
Posted: Thu Feb 18, 2016 8:21 pm
by MATT.B
jsarao wrote:Additional help...
I tend to use templates to start my documents (or spreadsheets, etc.). I would like to embed this macro in a template (mySStemplate.ots) and have it execute when I do a File -> Save As...
I've added this macro in the following way:
1. Menu -> Tools -> Macros -> Organize Macros -> OpenOffice.org Basic...
2. In the "Macro from" column, select mySStemplate.ots
3. Click the New button
4. Copy and paste the code segment (see above)
5. Menu -> File -> Save
QUESTION: How do I associate with File -> Save As... operation or does that happen automatically the next time I open the file?
the way I did this was changed the menu buttons I have one for saveas and one for saveas xls
goto tools/customize click on the menus tab select what menu you want the button in and click add, in the category section scroll down till you see macros and select the library the macro is stored in, then select the macro in second column after its added you can use the buttons off to the right to change the location, text, and icon of your new menu item, you can also do the same thing for toolbars