2023年9月7日木曜日

LibreOfficeで良く行う操作をマクロで自動的に行う方法について

 あまプロではLibreOfficeのマクロ( LibreOfficeBasic)をお教えする事があります。
LibreOfficeBasicの記事は少なくて検索しても出でこない場合が多いのであメログ。

 何らかの表で、月ごとにシートを作成して管理するというのはお仕事でも多いと考えます。
例えば支出一覧とか出勤簿などです。

良く有るシート

この良く有るシートを毎月右クリックでコピーしてシート名を翌月に変えてたら…結構大変です。
それだけなら未だ良いですが、シート内に月日のセルがあれば、そちらも毎月更新しなければならず…相当大変です。
そんな時は、LibreOfficeの数式機能とマクロ機能を使って楽をしましょう。

まず、シート内の月日ですが、毎月1日, 2日, 3日, 4日...と入力するのは大変です。
この場合、先頭の1日のみ値を入力して、残りは数式にします。

数式で左のセル+1にする

例えば1日がB2のセルで2日がC2のセルの場合は=B2+1とします。
それだけでC列は2日になります。
D列E列にもコピーすると3日4日になります。
ちなみにコピーはセルの右下にマウスカーソルを載せて、マウスカーソルが+の形に変わった状態でドラッグアンドドロップすると連続コピーできて便利です。

数式を使って当月の表は簡単に作れました。
しかし、このシートをコピーしても同じシート(の別名)ができるだけで、来月のシートになりません。
そこで、マクロ(LibreOfficeBasic)を使います。

まず、LibreOfficeでマクロの記録機能を有効化します。
メニューのツール > オプションのLibreOffice > 詳細で「マクロの記録を有効にする(限定的)」にチェックを入れます。

オプションで設定します
次にメニューのツール > マクロ > マクロの記録を押下してマクロを記録します。
小さな「マクロの記録」ダイアログが表示されます。
マクロの記録中にはこれが表示される
この状態で、シートを右クリックして「シートの移動またはコピー」を押下します。
翌月のシートを作成したいので、恐らく末尾に挿入で良いと考えます。
そしてコピーボタンを押下すると、当月のシート名に_2が付いた翌月のシートが作成されます。
これでシートのコピーができました。
次に記録の終了ボタンを押下します。
すると「BASICマクロ」というダイアログが表示されますので、新規モジュールボタンを押下します。
すると「新しいモジュール」ダイアログが表示されますので、OKボタンを押下します。
モジュールにはMainというサブルーチンが自動的に作成されますので、この状態で保存ボタンを押下します。

上書きを尋ねられますので、はいボタンを押下します。
これで、シートのコピーの操作をマクロとして記録できました。

では続きまして、シート名の変更作業もマクロに記録しましょう。
先程と同じくマクロの記録を開始して、シート名を右クリックして名前を変更します。

ここでは「5年9月_2」のシート名を「5年10月」に変更します。
シート名を変更したら、記録の終了を押下して、先程と同じく次はModule2にマクロを保存します。
これで、シートのコピーのマクロと、シート名の変更のマクロが記録されました。

保存したマクロを確認してみましょう。
メニューのツール > マクロ > マクロの編集を押下して、LibreOfficeBasicのIDEを起動します。


IDEが表示されます

左側のオブジェクトカタログのツリーにModule1とModule2があり、タブルクリックで選択すると、LibreOfficeBasicのコードが表示されます。
コメントや空行などが入っていますが、正味シートのコピーもシート名の変更も10行程のコードになります。

では次にこの記録されたLibreOfficeBasicのコードを応用して、

  1. 当月のシートをコピーして翌月のシートを作成する
  2. シート名を「当月_2」から「来月」に変更する(例えば当月が「5年9月」の場合「5年10月」)
  3. 起点となる1日の月日を「当月1日」から「来月1日」に変更する (例えば当月1日が「2023/09/01」の場合「2023/10/01」)

の処理を自動的に行うマクロを作成してみましょう。

IDEのメニューのツール > モジュールの選択を押下して「Basicマクロ管理」ダイアログを表示します。

次に表のファイルのStandardを選択して、新規作成ボタンを押下します。

「新しいモジュール」ダイアログが表示されますので、OKボタンを押下します。
Module3のSub Main〜End Subの間に、Module1とModule2のSub Main〜End Subの間をコピペします。
コピペしただけでは変数名の重複によるコンパイルエラーが発生しますので、不要な変数定義はコメントアウトや削除またはredimに変えて下さい。

次にシート名を変更している箇所を

args1(0).Value = "5年10月"
から
dim odsSheet As Object: odsSheet = ThisComponent.CurrentController.ActiveSheet
dim nextMonth As Integer: nextMonth = CInt( Mid( odsSheet.Name, InStr( odsSheet.Name, "年") + 1, InStr( odsSheet.Name, "月") - 3)) + 1
args1(0).Value = Mid( odsSheet.Name, 1, InStr( odsSheet.Name, "年")) & nextMonth & "月"
に変更します。
こちらはシート名の月の部分を加算して来月にしています。
当然12月に加算すると13月になってしまうのですが、年毎に表を作るという前提で省略します。

次に下記のコードを追加します。

ThisComponent.CurrentController.ActiveSheet.getCellByPosition( 1, 1).FormulaLocal = "=DATE( "& YEAR( NOW) &", " & nextMonth &", 1)"
こちらはB2セル(getCellByPosition( 1, 1))に、コピー作成年の来月の1日を数式で指定してます。
日付の数式を用いる場合はFormulaLocalを指定します。
Formulaを用いると生成された数式で予期せぬエラーが発生します。

ちなみにLibreOfficeBasicで文字列はダブルクォート"で括ります。
文字列内でダブルクォートを指定したい場合は""でエスケープします。
例えば

Dim s As String: s = "=CELL( ""filename"")"
などです。

さて、これでModule3のMainサブルーチンが完成しました。
コード全体は以下の様になった筈です。

コメントや空行などありますが、正味20行程度のコードになります。
コードが完成しましたら、IDEのメニューの実行 > コンパイルを行うを押下して、コンパイルエラーがない事を確認して下さい。
エラーが有ればエラーが表示され、エラーが無ければ何も表示されません

このマクロはメニューから実行させる事が可能ですが、毎回メニューを開くのは面倒です。
そこで、ボタンを追加し、ボタンクリック時にマクロを実行する様に設定します。
メニューの挿入 > フォームコントロール > ボタンを押下して、適当な大きさのボタンを挿入します。
ボタンを右クリックして「コントロールのプロパティ」を押下して「属性」ダイアログを表示します。

Sub Main 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(2) as new com.sun.star.beans.PropertyValue args1(0).Name = "DocName" args1(0).Value = "勤務表や出納帳など" args1(1).Name = "Index" args1(1).Value = 32767 args1(2).Name = "Copy" args1(2).Value = true  dispatcher.executeDispatch(document, ".uno:Move", "", 0, args1())  rem シート名を変更 dim odsSheet As Object: odsSheet = ThisComponent.CurrentController.ActiveSheet dim nextMonth As Integer: nextMonth = CInt( Mid( odsSheet.Name, InStr( odsSheet.Name, "年") + 1, InStr( odsSheet.Name, "月") - 3)) + 1 redim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "Name" args1(0).Value = Mid( odsSheet.Name, 1, InStr( odsSheet.Name, "年")) &nextMonth & "月"  dispatcher.executeDispatch(document, ".uno:RenameTable", "", 0, args1())  rem 1日を更新 ThisComponent.CurrentController.ActiveSheet.getCellByPosition( 1, 1).FormulaLocal = "=DATE( "& YEAR( NOW) &", " & nextMonth &", 1)"  End Sub
全般タブのタイトルに適当な名前を設定します。
次にイベントタブの実行時の…ボタンを押下します。
「アクションの割り当て」ダイアログが表示されますので、マクロボタンを押下します。
「マクロセレクター」ダイアログが表示されますので、Module3のMainサブルーチンを選択して、OKボタンを押下します。
割り当てられますので、OKボタンを押下します。

これでボタンにマクロの割り当てが完了しました。

割り当てたマクロを実行してみましょう。
メニューのツール > フォーム > デザインモードのチェックを外して、デザインモードを終了します。これでボタンの押下が可能になります。

ボタンをクリックすると

見事、来月のシートが作成されて、シート名も加算され、月日も来月1日から始まっています。

サンプルのodsファイルはGithubに上げております。
是非ご参考に楽しいLibreOffice生活をお楽しみ下さい。


0 件のコメント:

コメントを投稿

つっこみ