Re: [api-dev] Most convenient way to delete cells in a macro?
Johnny Andersson wrote: Hi! Sorry for bothering again... When I delete the contents of a cell in a macro (for a spreadsheet document) I just set ThisComponent.Value to 0 and .String to and maybe also .Formula to for each cell. Is there a quicker way to do this, requiring only one instruction? I want to obtain the same result as the backspace key does to a selected cell range. I recorded a macro when I deleted a cell range and the result looks like this: REM * BASIC * sub DeleteContentsOfCellRange 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 = ToPoint args1(0).Value = $A$5:$B$6 dispatcher.executeDispatch(document, .uno:GoToCell, , 0, args1()) rem -- dispatcher.executeDispatch(document, .uno:ClearContents, , 0, Array()) end sub I guess I could shorten this quite a bit and still keep the functionality, but isn't there an easier way? Johnny You could write the code in StarBasic using fewer words but what you have is adequate. You only need the one instruction - clearContents. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [api-dev] Most convenient way to delete cells in a macro?
Thanks! Now I found it (using Xray)! I looked for things like delete and erase. Clear never came to my mind... (maybe because English isn't my main language). Now I only have to figure out how the nContentFlags argument works, but I am sure I can find it now when I know what to look for. Johnny 2007/1/27, TerryJ [EMAIL PROTECTED]: Johnny Andersson wrote: Hi! Sorry for bothering again... When I delete the contents of a cell in a macro (for a spreadsheet document) I just set ThisComponent.Value to 0 and .String to and maybe also .Formula to for each cell. Is there a quicker way to do this, requiring only one instruction? I want to obtain the same result as the backspace key does to a selected cell range. I recorded a macro when I deleted a cell range and the result looks like this: REM * BASIC * sub DeleteContentsOfCellRange 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 = ToPoint args1(0).Value = $A$5:$B$6 dispatcher.executeDispatch(document, .uno:GoToCell, , 0, args1()) rem -- dispatcher.executeDispatch(document, .uno:ClearContents, , 0, Array()) end sub I guess I could shorten this quite a bit and still keep the functionality, but isn't there an easier way? Johnny You could write the code in StarBasic using fewer words but what you have is adequate. You only need the one instruction - clearContents. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [api-dev] Most convenient way to delete cells in a macro?
Hi Johnny, Alle 13:04, sabato 27 gennaio 2007, Johnny Andersson ha scritto: Hi! Sorry for bothering again... When I delete the contents of a cell in a macro (for a spreadsheet document) I just set ThisComponent.Value to 0 and .String to and maybe also Formula to for each cell. Is there a quicker way to do this, requiring only one instruction? I want to obtain the same result as the backspace key does to a selected cell range. I recorded a macro when I deleted a cell range and the result looks like this: [...] I guess I could shorten this quite a bit and still keep the functionality, but isn't there an easier way? You can also use regular Calc API's in this way: REM * BASIC * Option Explicit Sub TestClearContents Dim oRange As Object Dim iCellAttr As Integer iCellAttr = _ com.sun.star.sheet.CellFlags.VALUE + _ com.sun.star.sheet.CellFlags.DATETIME + _ com.sun.star.sheet.CellFlags.STRING + _ com.sun.star.sheet.CellFlags.ANNOTATION + _ com.sun.star.sheet.CellFlags.FORMULA + _ com.sun.star.sheet.CellFlags.HARDATTR + _ com.sun.star.sheet.CellFlags.STYLES + _ com.sun.star.sheet.CellFlags.OBJECTS + _ com.sun.star.sheet.CellFlags.EDITATTR oRange = ThisComponent.Sheets(0).getCellRangeByName(A1:B4) oRange.ClearContents(iCellAttr) End Sub ciao Paolo M - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [api-dev] Most convenient way to delete cells in a macro?
Thanks for the example, even if I already got the same answer (without an example). By the way, I have seen in most BASIC code that people put an o in the beginning of variable names for objects and other characters for other types. I suppose these are only some kind of recommendations, but it would be nice to know what the recommendations are. Is there a list of those somewhere? Maybe someone could send me a link? It looks like it's obvious most of the time, but suddenly (for example in Andrew Pitonyak's Macro document) there are cases where n seems to mean Integer and I have seen o, v, m and s for Variant. That's why I'm asking... Johnny 2007/1/27, Paolo Mantovani [EMAIL PROTECTED]: Hi Johnny, Alle 13:04, sabato 27 gennaio 2007, Johnny Andersson ha scritto: Hi! Sorry for bothering again... When I delete the contents of a cell in a macro (for a spreadsheet document) I just set ThisComponent.Value to 0 and .String to and maybe also Formula to for each cell. Is there a quicker way to do this, requiring only one instruction? I want to obtain the same result as the backspace key does to a selected cell range. I recorded a macro when I deleted a cell range and the result looks like this: [...] I guess I could shorten this quite a bit and still keep the functionality, but isn't there an easier way? You can also use regular Calc API's in this way: REM * BASIC * Option Explicit Sub TestClearContents Dim oRange As Object Dim iCellAttr As Integer iCellAttr = _ com.sun.star.sheet.CellFlags.VALUE + _ com.sun.star.sheet.CellFlags.DATETIME + _ com.sun.star.sheet.CellFlags.STRING + _ com.sun.star.sheet.CellFlags.ANNOTATION + _ com.sun.star.sheet.CellFlags.FORMULA + _ com.sun.star.sheet.CellFlags.HARDATTR + _ com.sun.star.sheet.CellFlags.STYLES + _ com.sun.star.sheet.CellFlags.OBJECTS + _ com.sun.star.sheet.CellFlags.EDITATTR oRange = ThisComponent.Sheets(0).getCellRangeByName(A1:B4) oRange.ClearContents(iCellAttr) End Sub ciao Paolo M - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [api-dev] Most convenient way to delete cells in a macro?
Alle 13:04, sabato 27 gennaio 2007, Johnny Andersson ha scritto: [] I recorded a macro when I deleted a cell range and the result looks like this: [] Just for info: if you often use the macro recorder for your macro projects, perhaps you may want to try the DispatchToAPI recorder, an alternative macro recorder (for Calc) that generate API based code. For example, the macro that you have recorded in order to select a range and delete it's content would appears like the following (just recorded by me): --- REM * BASIC * sub Main ' Recorded by Paolo Mantovani ' Date: Sat Jan 27 14:39:34 2007 ' get access to the document oDocumentModel = ThisComponent oDocumentView = oDocumentModel.getCurrentController() oDocumentFrame = oDocumentView.Frame ' the dispatcher service is used to send commands from the ' document frame to the underlaying office application oDispatcher = CreateUnoService(com.sun.star.frame.DispatchHelper) oCellRange = oDocumentView.ActiveSheet.getCellRangeByName(A5:B6) oDocumentView.select(oCellRange) iContentType = com.sun.star.sheet.CellFlags.STRING + _ com.sun.star.sheet.CellFlags.VALUE + _ com.sun.star.sheet.CellFlags.DATETIME + _ com.sun.star.sheet.CellFlags.FORMULA + _ com.sun.star.sheet.CellFlags.ANNOTATION + _ com.sun.star.sheet.CellFlags.FORMATTED + _ com.sun.star.sheet.CellFlags.OBJECTS oDocumentView.Selection.ClearContents(iContentType) end sub The dispatchToAPI recorder is deployed as OOo extension and is freely available from here: http://www.paolo-mantovani.org/downloads/DispatchToApiRecorder/ You need also to install at least one transformer (currently there is only a Calc Transformer) The transformer is also an extension and is the component that actually makes the Dispatch-to-API translation of the macro code http://www.paolo-mantovani.org/downloads/DispatchToApiRecorder/transformers/ After the installation, you do not need to know anything else: reboot openoffice and just use the macro recorder as usual. If you want to switch back to the classic macro recorder you have only to remove or disable both extensions from the extension manager dialog. Feel free to contact me for any further information ciao Paolo M - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [api-dev] Most convenient way to delete cells in a macro?
Thanks!
I have downloaded it and I will try it as soon as possible.
By the way, do you happen to know if there is a better way to check if a
cell range is empty than the following?
Dim Blah As Variant
Blah=ThisComponent.Sheets.getByIndex
(0).getCellRangeByPosition(0,2,1,2).getDataArray()
If IsEmpty(Blah) Then
{Do something}
EndIf
I am looking for something like the following, which doesn't seem to exist:
If ThisComponent.Sheets.getByIndex(0).getCellRangeByPosition(0,2,1,2).IsEmpty()
Then
{Do something}
EndIf
or perharps
If IsEmpty(ThisComponent.Sheets.getByIndex(0).getCellRangeByPosition(0,2,1,2))
Then
{Do something}
EndIf
I guess that won't work either since IsEmpty only works for the Variant
type.
Johnny
2007/1/27, Paolo Mantovani [EMAIL PROTECTED]:
Alle 13:04, sabato 27 gennaio 2007, Johnny Andersson ha scritto:
[]
I recorded a macro when I deleted a cell range and the result looks like
this:
[]
Just for info: if you often use the macro recorder for your macro
projects,
perhaps you may want to try the DispatchToAPI recorder, an alternative
macro
recorder (for Calc) that generate API based code.
For example, the macro that you have recorded in order to select a range
and
delete it's content would appears like the following (just recorded by
me):
---
REM * BASIC *
sub Main
' Recorded by Paolo Mantovani
' Date: Sat Jan 27 14:39:34 2007
' get access to the document
oDocumentModel = ThisComponent
oDocumentView = oDocumentModel.getCurrentController()
oDocumentFrame = oDocumentView.Frame
' the dispatcher service is used to send commands from the
' document frame to the underlaying office application
oDispatcher = CreateUnoService(com.sun.star.frame.DispatchHelper
)
oCellRange = oDocumentView.ActiveSheet.getCellRangeByName(A5:B6)
oDocumentView.select(oCellRange)
iContentType = com.sun.star.sheet.CellFlags.STRING + _
com.sun.star.sheet.CellFlags.VALUE + _
com.sun.star.sheet.CellFlags.DATETIME + _
com.sun.star.sheet.CellFlags.FORMULA + _
com.sun.star.sheet.CellFlags.ANNOTATION + _
com.sun.star.sheet.CellFlags.FORMATTED + _
com.sun.star.sheet.CellFlags.OBJECTS
oDocumentView.Selection.ClearContents(iContentType)
end sub
The dispatchToAPI recorder is deployed as OOo extension and is freely
available from here:
http://www.paolo-mantovani.org/downloads/DispatchToApiRecorder/
You need also to install at least one transformer (currently there is
only a
Calc Transformer)
The transformer is also an extension and is the component that actually
makes
the Dispatch-to-API translation of the macro code
http://www.paolo-mantovani.org/downloads/DispatchToApiRecorder/transformers/
After the installation, you do not need to know anything else:
reboot openoffice and just use the macro recorder as usual.
If you want to switch back to the classic macro recorder you have only
to
remove or disable both extensions from the extension manager dialog.
Feel free to contact me for any further information
ciao
Paolo M
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
