カテゴリー: Calc

Version: 7.3.7.2

住所から郵便番号を返すWebAPIの結果をWEBSERVICE関数でセルに表示する。下記はExcelAPIを利用する関数の使用例。

=WEBSERVICE("http://api.excelapi.org/post/zipcode?address="&ENCODEURL(A1))

Dim args(2) as new com.sun.star.beans.PropertyValue
args(0).Name = "PrintArea"
args(0).Value = "$A$1:$U$30"
args(1).Name = "PrintRepeatRow"
args(1).Value = "$A$10"
args(2).Name = "PrintRepeatCol"
args(2).Value = ""
Dim dispatcher as Object
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:ChangePrintArea", "", 0, args())

ThisComponent.enableautomaticCalculation False

「ツール」→「マクロ」→「ダイアログの管理」から追加した”Dialog1″を表示してダイアログ内のテキストフィールドの値を取得するコード。ちなみにダイアログをコードで閉じるにはdialogオブジェクトのendexecute()メソッドを呼ぶ。

'Show dialog
DialogLibraries.LoadLibrary("Standard")
dialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
dialog.execute()
'Get text
Dim textField As Object
textField = dialog.getControl("TextField1")
Dim value As String
value = textField.getText()

Dim validation As Object
validation = ThisComponent.Sheets.getByIndex(0).getColumns.getByIndex(5).Validation
validation.Type = com.sun.star.sheet.ValidationType.ANY
ThisComponent.Sheets.getByIndex(0).getColumns.getByIndex(5).Validation = validation

Dim startRow As Integer
startRow = ThisComponent.CurrentSelection.RangeAddress.StartRow

Dim endRow As Integer 'endRowは2になる
endRow = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1:A3").RangeAddress.EndRow

Dim startColumn As Integer 'startColumnは1になる
startColumn = ThisComponent.Sheets.getByName("sheet1").getCellRangeByName("B1:C6").RangeAddress.StartColumn

Dim endColumn As Integer 'endColumnは3になる
endColumn = ThisComponent.Sheets.getByName("sheet2").getCellRangeByName("A1:D9").RangeAddress.EndColumn

Dim fileLocation As String
Dim directory As String
fileLocation = ConvertFromURL(ThisComponent.getLocation())
directory = Left(fileLocation, Len(fileLocation) - Len(ThisComponent.getTitle()))
MsgBox(directory)

Dim styleFamilies As Object
Dim styles As Object
Dim style As Object
styleFamilies = ThisComponent.StyleFamilies
styles = styleFamilies.getByName("PageStyles")
style = styles.getByName("標準")
style.HeaderIsOn = false 'ヘッダーOFF
style.FooterIsOn = false 'フッターOFF
style.CenterHorizontally = true '横方向中央配置
style.Height = 25000 '用紙高さ250mm
style.Width = 17600 '用紙幅176mm
style.scaleToPages = 1 '1ページに収める

主なプロパティ

Dim document As Object
Dim dispatcher As Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Dim args(1) As new com.sun.star.beans.PropertyValue
args(0).Name = "Template"
args(0).Value = "標準"
args(1).Name = "Family"
args(1).Value = 8 'シートに適用する場合は8

dispatcher.executeDispatch(document, ".uno:StyleApply", "", 0, args())