複数のエクセルファイルを結合し、指定したシートの順番に並び替えるマクロ

 複数のエクセルを結合して一つのブックにまとめる際に、シートの順番もこちらで指定したいときがあるとします。ありますよね？あるとしましょう。例えば100でも200でも良いのですが結果の図表をエクセルに出す必要があるとき、二人で各50個作って最後にまとめる時…とかです。既にあるエクセルファイルに後からシートを指定した位置に追加する時もそうです。ブックの結合もめんどくさいですし、シート順の調整なんて手でやっていたら夜どころか朝になってしまいます。

以下に記載のVBAでそれなりに結合してシート順も指定できて便利です、との自作VBA自慢の記事です。この手の記事はLLMの登場以降ニーズが少ない気もしますが…LLMに聞けば出してくれますしね。そんなことは知らん。自慢すると言っておろうが。

1シート目に実行ボタンを付けて、結合前のファイルを格納したフォルダと結合後のファイルを出力するフォルダをそれぞれhogeとpiyoに指定します。sheetSortOrderシートには結合後のシートの順番を記載しています。後は画像下にあるvbaのコードを張り付けて実行すれば結合が出来るというわけです。

罫線を入れているA列に結合後のシート順をしてください。

複数ファイルを指定すると結合+シート順の並び替えになりますが、結合前フォルダに1つのエクセルファイルだけにするとシート順の並び替えだけになります。これだけでもとても便利。以下がVBAのソースコードになります。

Sub combineMacro()

    Dim folderPath As String

    Dim filename As String

    Dim targetBook As Workbook

    Dim afterBook As Workbook

    Dim targetSheet As Worksheet

    Dim sheetSordOrder As Range

    Dim sheetName As String

    Dim sortFlag As Boolean

    Dim runTime As String

    Dim savePath As String

        

    ' 結合するExcelブックが保存されているフォルダのパスを指定

    folderPath = ThisWorkbook.Sheets("Sheet1").Range("D12").Value & "\"

    

    ' 新しいブックを作成

    Set afterBook = Workbooks.Add

    

    ' 結合マクロが保存されているブックのSheetSortOrderに記載されているシート順を取得

    Set sheetSordOrder = ThisWorkbook.Sheets("SheetSortOrder").Range("A1").CurrentRegion

    

    ' フォルダ内のExcelブックを結合

    filename = Dir(folderPath & "*.xlsx")

    Do While filename <> ""

        Set targetBook = Workbooks.Open(folderPath & filename)

        

        ' シートを結合ブックに追加

        For Each targetSheet In targetBook.Sheets

            targetSheet.Copy After:=afterBook.Sheets(afterBook.Sheets.Count)

        Next targetSheet

        

        targetBook.Close False

        filename = Dir

    Loop

    

    ' シートを並び替える

    For Each targetSheet In afterBook.Sheets

        sheetName = targetSheet.Name

        sortFlag = False

        

        ' シート名がシート順に含まれているかチェック

        For Each V_CELL In sheetSordOrder

            If V_CELL.Value = sheetName Then

                targetSheet.Move Before:=afterBook.Sheets(V_CELL.Row)

                sortFlag = True

                Exit For

            End If

        Next V_CELL

        

        ' シート名がシート順に含まれていない場合は削除

        If Not sortFlag Then

            Sheets(sheetName).Delete

        End If

    Next targetSheet

    

    ' 結合ブックを保存

    ' 実行日時を取得

    runTime = Format(Now, "yyyymmdd_hhmmss")

    

    ' 保存パスを取得

    savePath = ThisWorkbook.Sheets("Sheet1").Range("D14").Value & "\"

    afterBook.SaveAs savePath & "結合後" & runTime & ".xlsx"

    ' ブックを閉じる

    afterBook.Close   

    ' メッセージを表示

    MsgBox "結合が完了しました。"

End Sub

OSSのライセンスについての話

 最近はRやpythonなどのOSSの利用が進んでいる。COREなんかはMITライセンスなのであまり細かいことは気にしなくても良いが、利用するパッケージによってはAGPL>3.0やapach>2.0等のライセンスが設定されている。apach>2.0はそのライセンスのOSS利用した場合は特許や許諾の表示を入れなければならないし*1、AGPL>3.0は成果物が公表された場合は利用したソースコードを公開しなければならない*2

…等と定められているが、例えばAGPL>3.0は2.Basic Permissionsに「The output from running a covered work is covered by this License only if the output, given its content, constitutes a covered work.」…実行された対象からの出力が、内容から判断してその対象物に該当する限りライセンスの対象にする…とある。製薬業界ではライセンス対象であるソースコード…往々にしてこれは当局への説明資料…が、最終成果物である医薬品には直接使用されていないので、ライセンス対象にはならないのではないか。もちろんこれは1ペーペーのおっさんが垂れ流している駄文なので正しいかはわからない。法務に詳しい方が見れば何を当たり前のことを…？/何を的外れなことを…？となるかもしれないが、現時点で何となく私の考えは上述の通り。

これまでSASという大きな傘の下にいたがOSSという大海原に漕ぎ出すにあたって、ライセンスなどの基本的な事項について一度立ち止まって考える機会はこれからも大切にしていきたい。

*1

https://licenses.opensource.jp/Apache-2.0/Apache-2.0.html

4.再頒布より

*2

https://www.gnu.org/licenses/agpl-3.0.html

2.Basic Permissionsより

Rで二つの日付の間の期間の日数を求める

 二つの期間の間の日数を求めたい時ありますよね？ADaM的にはADYの変数で定義されているようなやつです。せっかくなので流行りの{admiral}のお試しがてら期間の変数を算出します。別に{admiral}である必要はないのですが、せっかくよさげなパッケージがあるなら使ってみるのがOSSの醍醐味でしょう。

なにはともあれまずはパッケージのインポートですね。なかったらinstall.packages()してください。諸々実行して最後のオブジェクトHOGEにxxdyの変数が作成されています。便利な関数だなあ…！(ダイマ)

library(admiral)

library(dplyr)

library(lubridate) # 日付を操作するパッケージ

library(tibble) # テストデータを作るのに使用(tribble関数)

# データ作成

datain <- tribble(

  ~TRTSDTM,              ~ASTDTM,               ~AENDT,

  "2014-01-17T23:59:59", "2014-01-18T13:09:09", "2014-01-20"

) %>%

  mutate(

    TRTSDTM = as_datetime(TRTSDTM),

    ASTDTM = as_datetime(ASTDTM),

    AENDT = ymd(AENDT)

  )

head(datain)

'''

> head(datain)

# A tibble: 1 × 3

  TRTSDTM             ASTDTM              AENDT

  <dttm>              <dttm>              <date>

1 2014-01-17 23:59:59 2014-01-18 13:09:09 2014-01-20

'''

# 相対日を追加

# source_varsに期間の後端を示す変数を格納する。

# HOGEDY = TRTSDTMのようにすると、出力する期間の変数名を指定できる

# 変数名のみ指定すると期間の変数名はxxDYの形に指定される

# (個人的にはプログラム中に出力変数名が出てこないのは嫌なので変数名は指定したい)

hoge <- derive_vars_dy(

  datain,

  reference_date = TRTSDTM,

  source_vars = exprs(HOGEDY = TRTSDTM, ASTDTM, AENDT)

)

head(hoge)

'''

> head(hoge)

# A tibble: 1 × 6

  TRTSDTM             ASTDTM              AENDT      HOGEDY ASTDY AENDY

  <dttm>              <dttm>              <date>      <dbl> <dbl> <dbl>

1 2014-01-17 23:59:59 2014-01-18 13:09:09 2014-01-20      1     2     4

'''

指定したフォルダに格納されているファイルの、ファイル名と更新日時を取得する

vbscriptが廃止になると聞いて…(略。powershell5で指定したフォルダの直下にあるファイルのファイル名と最終更新日を取得するものを作成した。powershellなので実行には癖があるため実行用exeを作っても良い。こいつまたpowershellで変なもの作ってるな。

変数_ipathに対象のフォルダを指定して実行すればok。powershell本体のps1ファイルがある場所に結果となるoutput.txtが保存され、そのtxtを自動で開く形にしている。ps1ファイルの場所を自動で取得する$PSScriptRoot変数は結構便利。ちなみにソース中の#はコメントだ。

今回のは指定フォルダ内のサブフォルダは取得対象外にしているが、サブフォルダも含めたい場合はget-childitemに-recurseオプションを付ければよい。また最終更新日はこのままだと時分まで取得するので、日付までで良い場合はToStringの箇所をyyyy/mm/ddとする。

オサレポイントは出力時に`tと、ファイル名と日時をタブ区切りにしているところか。丸ごとエクセルにコピペするとファイル名セルと日時セルに分かれるのでちょっと便利。そのほかにもGet-childitemはいろいろ取得できるので必要に応じて修正できる。

最後は取得したGet-childitemをパイプでout-fileで外部ファイルのoutput.txtに出力している。パイプは便利だが結構何が送られているのかを見逃しがちになるので使いどころには気を付けたい。と言いつつもGet-childitemで取得したフォルダの中身をパイプでForEach-Objectに送って全件ループ、みたいな書き方が結構便利。わざわざforでループ用変数作って…みたいなことをしなくて良いのが助かる。

以下ソースコード

$_ipath = ""

$_opath = $PSScriptRoot

Write-Host "$_ipath 直下のファイルの更新日時を取得します"

# ファイルの更新日時を取得してテキストの書き込み

# サブフォルダの中身を含めて取得するにはget-childitemに-recurseを付ける

# 更新日時が日付までで良い場合はToString以下をyyyy/mm/ddにする

Get-childitem -Path $_ipath -File | ForEach-Object {

    $format_date = $_.LastWriteTime.ToString("yyyy/MM/dd HH:mm")

    "$($_.Name)`t$format_date" 

} | Out-File -filepath $_opath\output.txt

#結果ファイルを開く

invoke-item $_opath\output.txt

Write-Host "end"

複数の変数に同じ処理をかける時は配列が使えるの話

 例えばA、B、Cの3つの変数に同じ処理…今回はそれぞれに+1する…を実行する時、arrayが使えることもある。同じ処理をピコピコと繰り返し書くのが面倒な時は配列に記載してやればいっぱい似たようなものを書かなくて良い。具体例は以下の通り。

配列hogeを定義して、do overで全部にdo以下の処理を当てはめている。dimで要素数取って、とかをやっても良いのだが、今の場合のように全部に処理をすることが明らかな場合はわざわざdimで要素数を取らなくても良い。

data _null_ ;

    a = 1 ;

    b = 2 ;

    c = 3 ;

    array hoge a b c ;

    do over hoge ;

        hoge = hoge + 1 ;

    end ;

    putlog a b c ;

run ;

rtfの表に上付きの🄬を出力する

rtfの表内に上付き文字を出したいときはインラインフォーマットで^{super A}とすると上付き文字のAが出せますし、ギリシャ文字なんかを出すときは^{unicode 03B2}で出したりします。

環境依存文字の🄬も似たように^{unicode 24C7}でrtf上に出せますが、上付きの🄬…ユニコード指定の文字を上付き…で出すには工夫が要るのか…？という話です。特段の工夫とかは要らず、hoge^{super ^{unicode 24C7}}とやるとhogeの右肩に🄬を出すことができます。便利ですね。

インラインフォーマット指定前の^はods escapecharacterで指定しています。試してないのですが、defaultの(*esc*)だと重ねれない気がするので何かしらの文字をエスケープ文字として設定してください 

最新の更新日時のフォルダ/ファイルを取得する

指定したフォルダの中から最も新しいファイル(例えばxlsxなりcsv…) のファイル名を取得したい時があります。ファイル名が同じで中身だけ更新されるならプログラムを変えなくても良いですが、更新のたびにな前の末尾に日付が入ったりしてファイル名が更新される時があります。そんな時に毎度プログラムを更新するのはちょっとめんどくさいので、更新日時が一番最後のファイルの名前をマクロ変数に格納するプログラムの紹介です。

以下のデータステップで、if文に記載の拡張子が.xlsxとファイル名に_Specを持つファイルの中から、最も更新日が新しいファイルの名前をマクロ変数_spec_fileに格納しています。

%let _path = hogehoge ;

filename PPASS pipe "dir /od /b &_path ";

data _null_ ;

  length _inpass $200 ;

  infile PPASS DLM='09'X DSD MISSOVER LRECL=5000 ;

  input _inpass $ ;

  if ( index(_inpass,".xlsx")>0 and index(_inpass, "_Spec")>0 ) then call symputx("_SPEC_FILE",_inpass) ;

run ;

重要なのはfilename PPASS pipe "dir &_mspec_path /b /od";の部分です。コマンドプロンプトで使用するdirコマンドをsasで使用して、その結果をinfileでデータステップに回収しています。/odで更新日時順、/bでファイル名だけ取っています。データステップに更新日時順でファイル名を持ったレコードが発生するので、一番最後を取れば更新日時が一番最後、つまり一番最新のファイルが取得できる…というわけです。便利ですね。

更新日時が一番最後のフォルダを取るときはデータステップのif文で条件を変えても行けますが、余計なファイルをはじくようにdirのオプションに/adを指定しても良いです。/adでフォルダだけ取得できるので…

これで勝手に取得できますが、念のため何をマクロ変数に入れたかは都度確認が必要です。意図通りのものと違うものがマクロ変数化されているとややこしいので…

powershellで作るlog確認ツールの話

 そろそろvbsが非推奨になるとかなんとかがで、log確認ツールをpowershellで作った。もちろんpowershellである必要は無いのだが、powershellだとwindowsで標準なので特段の準備が不要なのはとても良い(powershell5.xに限る)…7だと標準じゃないので、それ使うならpythonなり使った方が融通が利いて便利そう…

ps1ファイルの実行には癖があるが、実行用batを作るのは一つの手ではないか。ps1ファイルと同じ場所に以下の実行用batを置いておくと、batをクリックして実行することができる。ps1のファイル名は適当なので適宜変更してほしい。
set _folder=%~dp0
powershell -ExecutionPolicy RemoteSigned -File %_folder%_logCheck.ps1

実際のプログラム本体は末尾に示すとしていくつか説明を。確認したいlogファイルを収めたフォルダに、本体のps1ファイルと検索したいワードを収めた"_logChrList.txt"の二つを格納する。_logChrList.txtの中身の例は以下の画像に示す。

powershell5ではtextファイルのエンコードを自動で正しく取れないので、実行時にファイルのエンコードをs/uで入力する。これはlogファイルごとに判定しているのではなく、フォルダ内の全てのlogのエンコードを一括で指定しているので、フォルダ内に複数のエンコードが混在していると文字化けしてしまう。

実行すると同じフォルダに_LogCheckResult.txtの名前でチェック結果が格納される。フォルダ内のすべてのlogファイル内に、_logChrList.txtで指定した文字列が何件含まれているかを出力する。この際ファイル毎/文字列毎に件数を出す。上記の画像の例だとerrorの件数とwarningの件数をそれぞれ数える。面倒だったので実行結果を固定名で出しているので、同じファイル名の実行結果があると問答無用で上書きされる。

繰り返すがチェックしたいlogが格納されているフォルダに、ps1ファイルの本体と、_logChrList.txtの二つを追加して実行、実行結果が同じフォルダに_LogCheckResult.txtが出力される。この時注意としては_logChrList.txtはutf8で作成し、_LogCheckResult.txtはutf8で出力される。

以下がps1ファイルの中身。

#エンコードを入力して指定

do {

    $input = Read-Host "logのエンコード方式を選択してください ('s' で shift-jis, 'u' で utf8)"

    switch ($input) {

        's' { $encode = 'default'; break }

        'u' { $encode = 'utf8'; break }

        default { Write-Host "無効な入力です。再度入力してください。" }

    }

} while ($input -ne 's' -and $input -ne 'u')

#本体の処理

$_inpath = $PSScriptRoot ;

$_list = Get-Content -Encoding utf8 "$_inpath\_logChrList.txt"

$_resFileName = "_LogCheckResult.txt"

New-Item -ItemType file -Path $_inpath\$_resFileName -Force 

Get-ChildItem -Path $_inpath -Filter *.log -Recurse | ForEach-Object {

    # logファイルの中身を取得 

    $_content = Get-Content -Encoding $encode $_.FullName 

    # ファイル名を出力

    Add-Content -Path $_inpath\$_resFileName -Value "$($_.FullName):" 

    $count = @{}

    foreach ($_msg in $_list) {

        

        $count[$_msg] = ($_content | Select-String -Pattern $_msg).count

        #該当箇所が0件でなかったら出力

        if ($($count[$_msg]) -ne 0) {

            Add-Content -Path $_inpath\$_resFileName -Value " $_msg : $($count[$_msg])" 

        }

    }

    # ファイルの区切りを判別するために空行を挿入

    Add-Content -Path $_inpath\$_resFileName -Value " "

}

#BOMアリのutf8をbom無しに変換する powershell5は標準出力がbomアリのutf8のため変換が必要

$_outfile = -join($_inpath,"\",$_resFileName)

$_chgnobom = Get-Content $_outfile

$Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding($False)

[System.IO.File]::WriteAllLines($_outfile, $_chgnobom, $Utf8NoBomEncoding)

write-host "end"