Dim filePicker As Object
Dim saveFiles() As Variant
Dim saveFileUrl As String
Dim saveFilePath As String
filePicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
filePicker.initialize(Array(com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_SIMPLE))
filePicker.appendFilter("テキストファイル(*.txt)", "*.txt")
If filePicker.execute() = 1 Then
saveFiles = filePicker.getFiles()
saveFileUrl = saveFiles(0)
saveFilePath = ConvertFromURL(saveFileUrl)
MsgBox(saveFilePath) '拡張子付いてないかも
End If
ファイルの選択ダイアログを表示する
Dim filePicker As Object
Dim loadFiles() As Variant
Dim loadFileUrl As String
Dim loadFilePath As String
filePicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
filePicker.initialize(Array(com.sun.star.ui.dialogs.TemplateDescription.FILEOPEN_SIMPLE))
filePicker.appendFilter("テキストファイル(*.txt)", "*.txt")
filePicker.appendFilter("HTMLファイル(*.htm, *.html)", "*.htm;*.html")
filePicker.appendFilter("すべてのファイル(*.*)", "*.*")
If filePicker.execute() = 1 Then
loadFiles = filePicker.getFiles()
loadFileUrl = loadFiles(0)
loadFilePath = ConvertFromURL(loadFileUrl)
MsgBox(loadFilePath)
End If
LibreOffice Calcでセルの行番号/列番号を取得する
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
ADODBでデータベースに接続する(Windows限定)
Dim connection As Object
Dim recordset As Object
Dim command As Object
connection = CreateObject("ADODB.Connection")
recordset = CreateObject("ADODB.Recordset")
command = CreateObject("ADODB.Command")
'SQLServerに接続してみる
connection.Open "Provider=SQLOLEDB;Data Source=DB_SERVER\SQLEXPRESS;Initial Catalog=DB_NAME;User ID=sa;Password=123456;"
command.ActiveConnection = connection
command.CommandTimeout = 0
command.CommandText = "SELECT * FROM TABLE_NAME;"
recordset = command.Execute
If recordset.RecordCount > 0 Then
If recordset.Eof = false Then
recordset.MoveFirst
MsgBox(recordset.Fields.Item("field1").Value)
'MsgBox(recordset.Fields.Item("field1").Name) ←これは使えない
End If
End if
LibreOffice Calcで開いているファイルの場所を取得する
Dim fileLocation As String Dim directory As String fileLocation = ConvertFromURL(ThisComponent.getLocation()) directory = Left(fileLocation, Len(fileLocation) - Len(ThisComponent.getTitle())) MsgBox(directory)
ディレクトリ選択ダイアログを表示する
Dim folderPicker As Object
Dim isAccept As Integer
Dim folders()
Dim folderUrl as String
Dim folderName as String
folderPicker = createUnoService("com.sun.star.ui.dialogs.FolderPicker")
folderPicker.setTitle ("ディレクトリの選択")
folderPicker.setDescription("ディレクトリを選択してください。")
folderPicker.setDisplayDirectory("C:\Users\Test\Desktop")
isAccept = folderPicker.execute()
If isAccept = 1 Then
folders() = folderPicker.getDirectory()
folderUrl = folders(0)
folderName = ConvertFromURL(folderUrl)
MsgBox(folderName)
Else
folderPicker.cancel()
End If
LibreOffice CalcのStyleを変更する
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ページに収める
主なプロパティ
| プロパティ | 型 |
|---|---|
| CenterHorizontally | boolean |
| CenterVertically | boolean |
| PrintAnnotations | boolean |
| PrintGrid | boolean |
| PrintHeaders | boolean |
| PrintCharts | boolean |
| PrintObjects | boolean |
| PrintDrawing | boolean |
| PrintFormulas | boolean |
| PrintZeroValues | boolean |
| PrintDownFirst | boolean |
| LeftPageHeaderContent | XHeaderFooterContent |
| LeftPageFooterContent | XHeaderFooterContent |
| RightPageHeaderContent | XHeaderFooterContent |
| RightPageFooterContent | XHeaderFooterContent |
| FirstPageNumber | short |
| PageScale | short |
| ScaleToPages | short |
| ScaleToPagesX | short |
| ScaleToPagesY | short |
| BackColor | ::com::sun::star::util::Color |
| BackGraphicURL | string |
| BackGraphicFilter | string |
| BackGraphicLocation | GraphicLocation |
| BackTransparent | boolean |
| LeftMargin | long |
| RightMargin | long |
| TopMargin | long |
| BottomMargin | long |
| LeftBorder | ::com::sun::star::table::BorderLine |
| RightBorder | ::com::sun::star::table::BorderLine |
| TopBorder | ::com::sun::star::table::BorderLine |
| BottomBorder | ::com::sun::star::table::BorderLine |
| LeftBorderDistance | long |
| RightBorderDistance | long |
| TopBorderDistance | long |
| BottomBorderDistance | long |
| ShadowFormat | ::com::sun::star::table::ShadowFormat |
| IsLandscape | boolean |
| NumberingType | short |
| PageStyleLayout | PageStyleLayout |
| PrinterPaperTray | string |
| RegisterModeActive | boolean |
| RegisterParagraphStyle | string |
| Size | ::com::sun::star::awt::Size |
| Width | long |
| Height | long |
| TextColumns | ::com::sun::star::text::XTextColumns |
| UserDefinedAttributes | ::com::sun::star::container::XNameContainer |
| HeaderBackColor | [ maybevoid ] ::com::sun::star::util::Color |
| HeaderBackGraphicURL | [ maybevoid ] string |
| HeaderBackGraphicFilter | [ maybevoid ] string |
| HeaderBackGraphicLocation | [ maybevoid ] GraphicLocation |
| HeaderLeftMargin | [ maybevoid ] long |
| HeaderRightMargin | [ maybevoid ] long |
| HeaderBackTransparent | [ maybevoid ] boolean |
| HeaderLeftBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| HeaderRightBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| HeaderTopBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| HeaderBottomBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| HeaderLeftBorderDistance | [ maybevoid ] long |
| HeaderRightBorderDistance | [ maybevoid ] long |
| HeaderTopBorderDistance | [ maybevoid ] long |
| HeaderBottomBorderDistance | [ maybevoid ] long |
| HeaderShadowFormat | [ maybevoid ] ::com::sun::star::table::ShadowFormat |
| HeaderBodyDistance | [ maybevoid ] long |
| HeaderIsShared | [ maybevoid ] boolean |
| HeaderHeight | [ maybevoid ] long |
| HeaderIsDynamicHeight | [ maybevoid ] boolean |
| HeaderIsOn | boolean |
| HeaderText | [ maybevoid ] ::com::sun::star::text::XText |
| HeaderTextLeft | [ maybevoid ] ::com::sun::star::text::XText |
| HeaderTextRight | [ maybevoid ] ::com::sun::star::text::XText |
| FooterBackColor | [ maybevoid ] ::com::sun::star::util::Color |
| FooterBackGraphicURL | [ maybevoid ] string |
| FooterBackGraphicFilter | [ maybevoid ] string |
| FooterBackGraphicLocation | [ maybevoid ] GraphicLocation |
| FooterLeftMargin | [ maybevoid ] long |
| FooterRightMargin | [ maybevoid ] long |
| FooterBackTransparent | [ maybevoid ] boolean |
| FooterLeftBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| FooterRightBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| FooterTopBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| FooterBottomBorder | [ maybevoid ] ::com::sun::star::table::BorderLine |
| FooterLeftBorderDistance | [ maybevoid ] long |
| FooterRightBorderDistance | [ maybevoid ] long |
| FooterTopBorderDistance | [ maybevoid ] long |
| FooterBottomBorderDistance | [ maybevoid ] long |
| FooterShadowFormat | [ maybevoid ] ::com::sun::star::table::ShadowFormat |
| FooterBodyDistance | [ maybevoid ] long |
| FooterIsDynamicHeight | [ maybevoid ] boolean |
| FooterIsShared | [ maybevoid ] boolean |
| FooterHeight | [ maybevoid ] long |
| FooterIsOn | boolean |
| FooterText | [ maybevoid ] ::com::sun::star::text::XText |
| FooterTextLeft | [ maybevoid ] ::com::sun::star::text::XText |
| FooterTextRight | [ maybevoid ] ::com::sun::star::text::XText |
| FootnoteHeight | long |
| FootnoteLineWeight | short |
| FootnoteLineColor | ::com::sun::star::util::Color |
| FootnoteLineRelativeWidth | byte |
| FootnoteLineAdjust | short |
| FootnoteLineTextDistance | long |
| FootnoteLineDistance | long |
| WritingMode | short |
| GridMode | short |
| GridColor | ::com::sun::star::util::Color |
| GridLines | short |
| GridBaseHeight | long |
| GridRubyHeight | long |
| GridRubyBelow | boolean |
| GridPrint | boolean |
| GridDisplay | boolean |
| HeaderDynamicSpacing | [ maybevoid ] boolean |
| FooterDynamicSpacing | [ maybevoid ] boolean |
| BorderDistance | long |
| FooterBorderDistance | [ maybevoid ] long |
| HeaderBorderDistance | [ maybevoid ] long |
LibreOffice CalcのActiveSheetに対してスタイルを適用する
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())
LibreOffice Calcでシートを他ドキュメントにコピーする
コピー先ドキュメントを指定する場合は args(0).Value の箇所にTitleを指定する。TitleはgetArgs()メソッドで取得できる配列から取得する。※ドキュメントのウインドウタイトルとは異なるので注意
Dim newSheetName As String
newSheetName = "コピー先"
ThisComponent.Sheets.copyByName("コピー元シート名", newSheetName, 0)
ThisComponent.CurrentController.Select(ThisComponent.Sheets.getByName(newSheetName))
Dim document as Object
Dim dispatcher as Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Dim args(2) as new com.sun.star.beans.PropertyValue
args(0).Name = "DocName"
args(0).Value = ""
args(1).Name = "Index"
args(1).Value = 32767
args(2).Name = "Copy"
args(2).Value = false
dispatcher.executeDispatch(document, ".uno:Move", "", 0, args())
LibreOffice Calcドキュメントの表示名ではないTitleを取得する
Dim title As String
Dim args As Object
args = ThisComponent.getArgs()
For i = 0 To Ubound(args)
If args(i).Name = "Title" Then
title = args(i).Value
Exit For
End if
Next i
MsgBox(title)