[Solved] How to "Save As" in a Calc Macro

[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.

[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)

[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.

[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.

[bwm]

That was it! Thanks so much it works now! This is a great forum.

[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

[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);
}

[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?

[MATT.B]

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