Se este não for o caminho, alguém que saiba, favor informar.
The great advantage of these two subroutines is the ease with which even those without programming experience can develop their first basic routines.
Macro 1:
Sub GoToAdd ( xAdd As String )
'' Supports the following commands: ( xAdd )
'' Worksheet .......... = "Sheet1"
'' Worksheet.Cell ..... = "Sheet1.C2"
'' Worksheet.Area ..... = "Sheet1.D5:V9"
'' Cell ............... = "C3"
'' Area ............... = "H6:K12"
'' Named Area ......... = "NamedArea"
'' Row ................ = "13"
'' Range of Rows ...... = "16:19"
'' Column ............. = "H:H"
'' Range of Columns ... = "D:U"
dim document, dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Dim Address(0) as new com.sun.star.beans.PropertyValue
Address(0).Name = "ToPoint" : Address(0).Value = xAdd
Dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, Address())
End Sub
This routine, with a simple “GoToAdd” call, can execute all the options described above.
Example: Go to cell C2 of Sheet1
Sub Test1
GoToAdd "Sheet1.C2"
End Sub 'Test1
Macro 2:
Sub Execute ( oQue$, Optional xValue% )
'In commands starting with Go___, it is mandatory
''to have the value of the xValue variable greater than 1 (one).
dim document, dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
If IsMissing ( xValue ) Then : xValue = 1 : End If
Dim Exec(1) as new com.sun.star.beans.PropertyValue
Exec(0).Name = "By" : Exec(0).Value = xValue
Exec(1).Name = "Sel" : Exec(1).Value = false
dispatcher.executeDispatch(document, ".uno:" & oQue & "", "", 0, Exec())
End Sub
This routine, with a simple call to “Execute,” can trigger various commands (see list below) very easily.
Example: Go to cell C2 of Sheet1, copy and paste into cell D8 of Sheet2.
Sub Test2
GoToAdd "Sheet1.C2"
Execute "Copy"
GoToAdd "Sheet2.D8"
Execute "Paste"
End Sub 'Test2
List of possible commands:
’ Open Selection List … = DataSelect (ONLY WORKS WITH TEXT)
’ Group Cells … = ToggleMergeCells
’ Align Top … = AlignTop
’ Align Right … = AlignRight
’ Align Left … = AlignLeft
’ Align Horizontal Center … = AlignHorizontalCenter
’ Align Vertical Center … = AlignVCenter
’ Delete Note … = DeleteNote
’ Show Note … = ShowNote
’ Show/Hide Note … = NoteVisible
’ Hide Note … = HideNote
’ Update Link … = EditLinks
’ Update Link Allow … = UpdateTableLinks
’ Paste … = Paste
’ Paste Only Value … = PasteOnlyValue
’ Paste Unformatted Text … = PasteUnformatted
’ Convert Formula to Value . = ConvertFormulaToValue
’ Convert to Uppercase … = ChangeCaseToUpper
’ Copy … = Copy
’ Cut … = Cut
’ Delete Active Column … = DeleteColumns
’ Delete Active Row … = DeleteRows
’ GoDown ( Qty ) … = GoDown ( oQtde%)
’ GoUp ( Qty ) … = GoUP ( oQtde%)
’ GoRight ( Qty ) … = GoRight ( oQtde%)
’ GoLeft ( Qty ) … = GoLeft ( oQtde%)
’ GoUpToStartOfData ( oQtde%) )
’ Shift First Left ( Qty ) . = GoLeftToStartOfData(oQtde%)
’ Move Last Down (Qtde) … = GoDownToEndOfData(oQtde%)
’ Move Last Right (Qtde) … = GoRightToEndOfData(oQtde%)
’ Close Worksheet … = CloseWin
’ Anchor Image Cell … = SetAnchorToCell
’ Size Image Cell … = FitCellSize
’ Print … = Print
’ Print Add Area … = AddPrintArea
’ Print Default … = PrintDefault
’ Print Define Area … = DefinePrintArea
’ Insert Column Before … = InsertColumnsBefore
’ Insert Column After … = InsertColumnsAfter
’ Insert Current Date … = InsertCurrentDate
’ Insert Current Time … = InsertCurrentTime
’ Insert Row Above … = InsertRowsBefor
’ Insert Row Below … = InsertRowsAfter
’ Clear Contents … = ClearContents
’ Clear Direct Formatting … = ResetAttributes
’ Show Columns …= ShowColumn
’ Show Rows … = ShowRow
’ Hide Column … = HideColumn
’ Hide Row … = HideRow
’ Sort Ascending … = SortAscending
’ Sort Descending … = SortDescending
’ JumpToNextTable … = JumpToNextTable
’ Jump ToPrevious Table … = JumpToPrevTable
’ Recalculate … = Calculate
’ Save … = Save
’ Save File Close With Confirmation … = CloseDoc
’ Save As … . = SaveAs
’ Select Below (Qty) … = GoDownSel(oQty%)
’ Select Above (Qty) … = GoUpSel(oQty%)
’ Select Right (Qty) … = GoRightSel(oQty%)
’ Select Left (Qty) … = GoLeftSel(oQty%)
’ Select To End Below (Qty) … = GoDownToEndOfDataSel(oQty%)
’ Select To End Right (Qty) … = GoRightToEndOfDataSel(oQty%)
’ Select From Beginning Above (Qty) … = GoUpToStartOfDataSel(oQty%)
’ Select From Beginning Left (Qty) . = GoLeftToStartOfDataSel(oQty%)
’ Select Column … = SelectColumn
’ Select Row … = SelectRow
’ Adjust Column …= SetOptimalColumnWidthDirect
There must be more possible commands.