Hi, and thank you all for the suggestions. I did manage to get the macro doing exactly what is needed by following Carl's example. My version is pasted below.
There are several similar spreadsheets that need this functionality (without requiring the user to install an extension). I copied the macro to another spreadsheet using copy-n-paste and it works. To make this 'perfect' I would like to assign the macro to the SaveDocument event. I somehow managed to do this for the first spreadsheet, and it works wonderfully! However... it seems odd, but I now cannot figure out how to assign the macro on the additional spreadsheets. Here are a couple of screendumps from the 'working' spreadsheet: http://img17.yfrog.com/img17/4925/oooorganisemacros.png http://img17.yfrog.com/img17/7965/ooomacroassign.png On the other spreadsheet, to which the macro has been copied, the dialogs show the same except that 'Save Document' does not have an Assigned Action. If I click on Macro... in the second dialog above, it brings up another dialog but that only lists My Macros and OpenOffice.org Macros. How can I assign a spreadsheet Basic macro to an event (to replicate what is shown in the above screendumps)? Thanks! REM ***** BASIC ***** rem ---------------------------------------------------------------------- sub SaveConfig rem ---------------------------------------------------------------------- dim document as object dim dispatcher as object document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") rem ---------------------------------------------------------------------- dim sURL, sCSV as string sURL = ThisComponent.getURL() sCSV = left(sURL,len(sURL)-4)+".hex" rem ---------------------------------------------------------------------- Dim mFilePropertiesCSV(1) As New com.sun.star.beans.PropertyValue mFilePropertiesCSV(0).Name = "FilterName" mFilePropertiesCSV(0).Value = "Text - txt - csv (StarCalc)" mFilePropertiesCSV(1).Name = "FilterOptions" mFilePropertiesCSV(1).Value = "44,0,ANSI,1" ThisComponent.CurrentController.setActiveSheet(ThisComponent.sheets.getbyname(".hex_file")) ThisComponent.storeAsURL(sCSV, mFilePropertiesCSV()) rem ---------------------------------------------------------------------- Dim mFileProperties(0) As New com.sun.star.beans.PropertyValue mFileProperties(0).Name = "FilterName" mFileProperties(0).Value = "calc8" ThisComponent.CurrentController.setActiveSheet(ThisComponent.sheets.getbyname("Config")) ThisComponent.storeAsURL(sURL, mFileProperties()) end sub On Thu, Sep 17, 2009 at 01:07:22PM -0600, Carl Moller wrote: > One solution and hopefully some code you can use is from the forums: > http://www.oooforum.org/forum/viewtopic.phtml?t=23307&highlight=csv > My post (swingkyd) shows how to march through a sheet and export each > page as a csv file. Furthermore, saving is also illustrated. > Filter options for magic codes are found on the wiki: > http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options > > I hope this helps! > > [email protected] wrote: > >Hi, > > > >I am trying to make a macro for use in Calc, which will: > > > > 1. - switch to a particular sheet > > > > 2. - save the sheet in .csv format (applying specific format options > > for the .csv format) > > > > 3. - switch back to the first sheet > > > > 4. - save the whole Spreadsheet (all sheets) in .ods format > > > > > >I recorded steps 1, 2 & 3 using: Tools | Macros | Record Macro... > > > >However, the resulting Basic code has the filename is hardcoded, which > >means that the macro cannot be used on other spreadsheets. > > > >I tried commenting out the line that hardcodes the filename value. > >This does cause the current spreadsheet filename to be used but then > >loses the direction to save as .csv format. > > > >Below is pasted the Basic code. If someone could show me how to make > >this generic to any spreadsheet that would be much appreciated! > > > >Also, I looked for documentation to explain the arguments to the > >.SaveAs method but couldn't find any. I'm sure this must be written > >somewhere, so any pointer to RTFM would also be appreciated. > > > >OOO310m11 (Build:9399) on Windows XP. > > > >Thanks. > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
