Cell操作
[ 挿入・削除 ]
現在のセルは下に移動(Shift down)
sub InsertCellDown()
Dim Doc As Object, oSheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oSheet =oDoc.getSheets().getByIndex(0)
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 2
CellRangeAddress.StartRow = 2
CellRangeAddress.EndColumn = 4
CellRangeAddress.EndRow = 4
oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellInsertMode.DOWN)
msgbox "Success"
End Sub
【 解説 】
このLibreOfficeマクロは、指定したシートにC3~E5にセルを挿入するものです。
元のセルは下方向に移動します。
(1)最初に Calcのセル範囲を表わすオブジェクトを設定
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
(2)シートインデックスを指定して 対象シートを指定
CellRangeAddress.Sheet = 0
(3)Cellを挿入する範囲を指定
CellRangeAddress.StartColumn ~ CellRangeAddress.EndRow
(4)現状のセルをどの方向に移動するか指定
com.sun.star.sheet.CellInsertMode.DOWN : 下方向
(3)の範囲を変更すると、任意の場所にセルを挿入することができます。
【マクロの記録】現在のセルは下に移動(Shift down)
Sub UnoInsertCell()
Dim oDoc as Object, oCtrl as Object, oFrame as Object
Dim oDispatcher as Object
Dim oProp(0) as new com.sun.star.beans.PropertyValue
oFrame = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
'
oProp(0).Name = "ToPoint"
oProp(0).Value = "A1" ' "1:1" → 行全体を下げる挿入
oDispatcher.executeDispatch(oFrame, ".uno:GoToCell", "", 0, oProp())
' Insert Cell ( Direction of Existed Cell is Down )
oDispatcher.executeDispatch(oFrame, ".uno:InsertCellsDown", "", 0, Array())
'
msgbox "InsertCellsDown" & Chr$(10) & "(Dispatcher)",0,"Insert Cells"
End Sub
現在のセルは右方向に移動Shift right)
Sub InsertCellRight()
Dim Doc As Object, oSheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oSheet =oDoc.getSheets().getByIndex(0)
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 2
CellRangeAddress.StartRow = 2
CellRangeAddress.EndColumn = 4
CellRangeAddress.EndRow = 4
oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellInsertMode.RIGHT)
msgbox "Success"
End Sub
【マクロの記録】現在のセルは右方向に移動Shift right)
Sub UnoInsertCell()
Dim oDoc as Object, oCtrl as Object, oFrame as Object
Dim oDispatcher as Object
Dim oProp(0) as new com.sun.star.beans.PropertyValue
oFrame = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
'
oProp(0).Name = "ToPoint"
oProp(0).Value = "A1" ' "A:A" で 列全体を右に移動
oDispatcher.executeDispatch(oFrame, ".uno:GoToCell", "", 0, oProp())
' Insert Cell ( Direction of Existed Cell is Right )
oDispatcher.executeDispatch(oFrame, ".uno:InsertCellsRight", "", 0, Array())
'
msgbox "InsertCellsRight" & Chr$(10) & "(Dispatcher)",0,"Insert Cells"
End Sub
【マクロの記録】現在のセルは右・下に移動
Sub UnoInsertCell()
Dim oDoc as Object, oCtrl as Object, oFrame as Object
Dim oDispatcher as Object
Dim oProp(0) as new com.sun.star.beans.PropertyValue
oFrame = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
'
oProp(0).Name = "ToPoint"
oProp(0).Value = "B3:C5"
oDispatcher.executeDispatch(oFrame, ".uno:GoToCell", "", 0, oProp())
'
oProp(0).Name = "Flags"
oProp(0).Value = ">"
oDispatcher.executeDispatch(oFrame, ".uno:InsertCell", "", 0, oProp())
'
msgbox "Success", 0,"Uno / Insert"
End Sub
'
' [ Note ]
' V : Cellを下に移動
' > : Cellを右に移動
' R : 行全体を下に移動
' C : 列全体を右に移動
Cellの削除(1)[既存データは上方向に移動](Shift up)
Sub DeleteCellUp()
Dim Doc As Object, oSheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
oSheet =ThisComponent.getSheets().getByIndex(iSheetindex)
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 2
CellRangeAddress.StartRow = 2
CellRangeAddress.EndColumn = 5
CellRangeAddress.EndRow = 5
oSheet.removeRange(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.UP)
End Sub
Cellの削除(2)[既存データは左方向に移動](Shift left)
Sub oDeleteCellLeft()
Dim Doc As Object, oSheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim iStart as integer
Dim iRows as integer
Dim iSheetindex as integer
iSheetindex = 0
iStart = 0
iRows=3
oDoc = ThisComponent
oSheet =oDoc.getSheets().getByIndex(iSheetindex)
CellRangeAddress.Sheet = iSheetindex
CellRangeAddress.StartColumn = 0
CellRangeAddress.StartRow = iStart
CellRangeAddress.EndColumn = 2
CellRangeAddress.EndRow = iStart + iRows-1
oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.LEFT)
End Sub
Cellの削除(3)[行全体が上方向に移動]
Sub oDeleteCellUp()
Dim Doc As Object, oSheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim iStart as integer
Dim iRows as integer
Dim iSheetindex as integer
iSheetindex = 0
iStart = 0
iRows=3
oSheet=ThisComponent.getSheets().getByIndex(iSheetindex)
CellRangeAddress.Sheet = iSheetindex
CellRangeAddress.StartColumn = 0
CellRangeAddress.StartRow = iStart
CellRangeAddress.EndColumn = 2
CellRangeAddress.EndRow = iStart + iRows-1
oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.ROWS)
End Sub
Cellの削除(4)[列全体が左方向に移動]
Sub oDeleteCellLeft()
Dim Doc As Object, oSheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim iStart as integer
Dim iRows as integer
Dim iSheetindex as integer
iSheetindex = 0
iStart = 0
iRows=3
oSheet =ThisComponent.getSheets().getByIndex(iSheetindex)
CellRangeAddress.Sheet = iSheetindex
CellRangeAddress.StartColumn = 0
CellRangeAddress.StartRow = iStart
CellRangeAddress.EndColumn = 2
CellRangeAddress.EndRow = iStart + iRows-1
oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.COLUMNS)
End Sub
Cellの削除(5)[左・上・行全体・列全体が移動]
Sub CalcDeleteCell()
Dim oDoc as Object, oCtrl as Object, oFrame as Object
Dim oDispatcher as Object
Dim oProp1(0) as new com.sun.star.beans.PropertyValue
Dim oProp(0) as new com.sun.star.beans.PropertyValue
oDoc=ThisComponent
oCtrl = oDoc.getCurrentController()
oFrame = oCtrl.getFrame()
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oProp1(0).Name = "ToPoint"
oProp1(0).Value = "A1:B6"
oDispatcher.executeDispatch(oFrame, ".uno:GoToCell", "", 0, oProp1())
oProp(0).Name = "Flags"
oProp(0).Value = "U"
oDispatcher.executeDispatch(oFrame, ".uno:DeleteCell", "", 0, oProp())
msgbox "Success"
End Sub
'
' [ Flag Value ]
' U : Cell を 上に移動
' L : Cell を 左に移動
' R : 行全体を削除
' C : 列全体を削除