The macro recorder sucks, sorry to say that. Just don't use it. Just
type the commands manually instead. It's a bit of a learning curve,
but it's worth it.
If you know Basic, installing Xray will help you a lot to figure out
most of the objects you need.
For example:
Option Explicit
Sub MyTestSub
Dim Sheet As Object
Sheet=ThisComponent.getSheets().getByName("Sheet1")
Xray Sheet
End Sub
If you installed Xray correctly, a dialogue will open. You can then
study all variables, methods etc. available for Sheet.
For example, highlight Methods, then sort it from A to Z by checking
the sort checkbox. Then look for things that start with ”get” and
”set”. Very useful indeed. For example you'll find
”getCellRangeByPosition()” and ”getCellRangeByName()” (or something
like that).
Then you can rewrite your subroutine like this, for example:
Option Explicit
Sub MyTestSub
Dim Sheet As Object, Cell As Object
Sheet=ThisComponent.getSheets().getByName("Sheet1")
Cell=Sheet.getCellByPosition(0,0) ' 0 means column A, the other 0
means row 1, so (0,0) means A1.
Xray Cell
End Sub
There are other things than Sheets to examine:
Option Explicit
Sub MyTestSub
Xray ThisComponent
End Sub
Note that I wrote this without testing or anything, so some of these
examples may not work, but still they might be giving you a hint of
some kind…
Google also works…
If you want to read more about Basic in OpenOffice.org, I would
recommend the ODF file by Andrew D. Pitonyak. Search for something
like ”AndrewMacro.odt” and I think you will find it. It's about 400
pages so it covers quite a lot, but not everything.
J.R.
2009/9/17 <[email protected]>:
> 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.
>
>
>
> REM ***** BASIC *****
>
> Sub Main
>
> End Sub
>
>
>
>
> sub Foo
> rem ----------------------------------------------------------------------
> rem define variables
> dim document as object
> dim dispatcher as object
> rem ----------------------------------------------------------------------
> rem get access to the document
> document = ThisComponent.CurrentController.Frame
> dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
>
> rem ----------------------------------------------------------------------
> dim args1(0) as new com.sun.star.beans.PropertyValue
> args1(0).Name = "Nr"
> args1(0).Value = 4
>
> dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
>
> rem ----------------------------------------------------------------------
> dim args2(2) as new com.sun.star.beans.PropertyValue
> args2(0).Name = "URL"
> rem args2(0).Value = "file:///C:/path/to/file.csv"
> args2(1).Name = "FilterName"
> args2(1).Value = "Text - txt - csv (StarCalc)"
> args2(2).Name = "FilterOptions"
> args2(2).Value = "44,0,ANSI,1"
>
> dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args2())
>
> rem ----------------------------------------------------------------------
> dim args3(0) as new com.sun.star.beans.PropertyValue
> args3(0).Name = "Nr"
> args3(0).Value = 1
>
> dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())
>
>
> end sub
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
