野口香社会保険労務士事務所

群馬県にある社会保険労務士事務所のページです。

  • 【ExcelVBA】ファイルのタイムスタンプ

【ExcelVBA】ファイルのタイムスタンプ

  • [記事公開]2023.04.25[最終更新]2023.04.26
  • VBA

Yahoo!知恵袋で回答した中から、ファイルのタイムスタンプを取得する方法について紹介します。

目次

質問

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13279016978

A列にファイルパスが入っています。
A列のファイルパスからB列に作成日時を取り出したいです。
尚、A列のファイルパスは毎回抽出件数が異なります。
5行の場合もあれば100行になる場合もあります。

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13279016978

回答

私が考えたコードはこちら↓です。

Option Explicit
Enum e
ファイルパス = 1
作成日時
最終更新日
TheEnd = 最終更新日
End Enum
Public Sub GetTimeStamp()

    'ファイルパスが入っているシート
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets(1)
    
    'ヘッダ行(表見出しが入っている行の行番号)
    Const clStart As Long = 1
    
    '最大行番号
    Dim lRow As Long
    lRow = sh.Cells(clStart, e.ファイルパス).End(xlDown).Row 'セルA1からCTRL＋下矢印を押すとたどり着くセルが最終行のはず
    'レコードが0件の場合に備えて、データ修正
    If lRow = sh.Rows.Count Then
        lRow = 0
    End If
    
    'ファイルのタイムスタンプなどの情報をゲットするためのFileSystemObjectを生成
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'ループカウンタ
    Dim ii As Long
    'ファイルパスを一時的に入れる変数
    Dim stPath As String
    'データ更新時刻を一時的に入れる変数
    Dim d As Date
    '属性情報(ファイルかフォルダか)を一時的に入れる変数
    Dim attr As VbFileAttribute
    
    'ループ突入
    'レコードの数だけ調べる
    For ii = clStart + 1 To lRow
    
        'パス情報把握
        stPath = sh.Cells(ii, e.ファイルパス)
        
        'フィルではなくフォルダの場合もあるので、ここでチェック
        attr = GetAttr(stPath)
        If attr = vbDirectory Then
            'フォルダなので、何もしない(読み飛ばす)
        Else
            'フォルダではない＝＝＞ファイルのはず＝＝＞処理対象
            
            'データ作成日時
            d = FSO.GetFile(stPath).DateCreated
            sh.Cells(ii, e.作成日時).Value = Format(d, "yyyy/mm/dd hh:mm:ss")
            
            'データ更新日時
            d = FSO.GetFile(stPath).DateLastModified
            sh.Cells(ii, e.最終更新日).Value = Format(d, "yyyy/mm/dd hh:mm:ss")
            
        End If
        
    Next ii
    
    'オブジェクト解放
    Set sh = Nothing
    Set FSO = Nothing
End Sub

解説

作成日時を取得するには

ファイルの更新日時を取得するためのメソッドはVBAで標準で用意されていまして、「FileDateTime」といいます。

ところが今回の質問者さんの質問文を読むと「作成日時」となっていまして、新規作成したときの日時なのか、更新したときの日時なのか判断がつきませんでした。

更新したときの日時でよいのならFileDateTimeでよいのですが、新規作成したときの日時のことならちょっとそれでは用をなしません。

そこで、新規作成したときの日時と、更新したときの日時両方を取得することにしました。

こういうときに便利なのが、FileSystemObjectです。

FileSystemObjectはファイルのいろいろなプロパティを取得できます。ファイルに対するいろいろな操作(メソッド)もできます。

詳しくは、OfficeTANAKAさんやWebの有益サイトをご覧ください(他力本願)。

ファイルを新規作成した日時を取得するには、DateCreatedを使います。

ファイルを更新したときの日時を取得するには、DateLastModifiedを使います。

他にファイルに最終アクセスした日時を取得する、DateLastAccessedなどというものもあります。

この辺、OfficeTANAKAさんを参照するのがよいと思います。コンパクトにまとめられており、私はいつも重宝に使っています。

変動する件数

レコード件数に変動があるとのこと。

私はいつもCTRL+↓でゲットできるセルの行番号をとってくるようにして、レコード件数を把握しています。

これをVBAのコードで表すと、

Range(“A1”).End(xlDown).Row

となります。

Rangeというのはセルのことで、Cells(行番号, 列番号)と言う風に表現することもできます。

また、どこのシートのセルか明らかにして指定することもできるので、

sh.Cells(clStart, e.ファイルパス).End(xlDown).Row

というふうに書きました。shはワークシート関数を示すユーザー定義の変数です。

このやり方で問題が出ることがあります。

例えば、次のような状態です。

上図ではレコードが1件もありません。

この状態でCTRL+↓をするとどうなるかというと・・・・

一番下の行に来てしまいました。

このとき、

sh.Cells(clStart, e.ファイルパス).End(xlDown).Row

がとる値は、1048576です。

ところが、データはゼロ件が正しいです。

そのため、次のようなif文をいれて、間違った行番号とならないよう制御しています。

    'レコードが0件の場合に備えて、データ修正
    If lRow = sh.Rows.Count Then
        lRow = 0
    End If

sh.Rows.Countというのは、ワークシートの行全部の数という意味です。つまり、1048576です。

では、

If lRow = sh.Rows.Count Then

ではなく、

If lRow = 1048576 Then

と書けばよいではないかと思うかもしれません。

しかし、Excelはバージョンによって最大行数は異なります。昔のExcelだと65536行でした。

そういうバージョンによってこのプログラムが使えなくなるのを避けるために、あえてsh.Rows.Countを使っています。

Enumと表のヘッダ

質問者様は初心者だとのことだったので、Enumについても解説しておきます。

Enumは連番を扱いたいときに便利な型です。

型って何？と思ったと思いますが、ひとまずそういうもんだと流してください。

単なる整数だけだと人間が見て分かりづらいですよね。プログラムの世界ではマジックナンバーと言ったりします。マジックナンバーは可読性が落ちるから使ってはダメだよなんて、先輩から教わったりするもんです。

だから、整数にニックネームをつけて扱いやすくするための工夫がいろいろあります。Enumもその一つです。

今回、私が用意したワークシートはこんな感じです。

1行目が表のヘッダ(見出し行)です。

左から順にファイルパス、作成日時、最終更新日となっています。

今回3列しかないので、普通にA列、B列、C列として扱ってもよいのですが、これが例えば250列もあるような巨大な表だった場合、それだと扱いづらくなります。

そこで、1行目をコピーして、適当な空白領域で値を選択して貼り付けます。その際、値貼り付けかつ、行列を入れ替えてください。

そうすると、こんな風になります。

そしたらこれをまたコピーして、今度はVBEでコードに貼り付けます。

あとは、「Enum 適当な名前」と「End Enum」で挟みます。適当な名前は、今回私は「e」にしました(Enumだから・・・本当に適当です・・・・すみません・・・・)。

念のため、先頭の「ファイルパス」のところに=1をつけておきます。

そうすると、以下書いてなくても作成日時は2、最終更新日は3という意味になります。

ワークシートのこれ↓は消しておいてください。もう使いません。

Enumをプロシージャ内で使うときは、適当につけた名前.ファイルパスというふうに、ドットでつなげて使います。

コーディングを早くするための工夫でもあります。

RangeとCellsの違い

RangeとCellsの違いについても解説しておきます。

RangeはRange(“A1”)というように、引数にセルアドレスを書きます(本当は違うんですが、ここでは初心者向け解説ってことで・・・＾＾；)。

セルアドレスはアルファベット(列を示す)＋番号(行を示す)の順です。

一方、CellsはCells(2, 3)というように、引数が二つとも数字です。

順番が決まっていまして、第1引数が行、第2引数が列を示しています。

ところが、Cellsの場合、こういう書き方もできます。

Cells(2, “A”)

直感的にこっちの方が分かりやすいかも？慣れないうちはこういう書き方をしてもよいと思います。とにかく自分の分かるコードを書くのが一番です。

ただ、この書き方だと遅かれ早かれ壁にぶつかります。そういう壁にぶつかったときに、別の書き方も試してみてください。

フォルダとファイルを区別するには

質問者様から追加で質問がありました。

コードを実行したところ、エラーとなったとのこと。

エラーの個所は、FSOでFileGetしているところでした。

引数のstPathは属性がファイルでなければならないのですが、フォルダを指定する場合もあるとのこと。

原因がわかりました。取得したファイルパスの中にフォルダパスのみも含まれていた為でした。ファイルパスのみにしたところ動きました。
フォルダパスの場合は空白で返すことは可能なのでしょうか？

ファイルかフォルダかは、GetAttrというメソッドを使えば分かります。

GetAttrの型は、上図を見て分かる通り、VbFileAttribute型です。引数は一つだけで、文字列型です。今回はここにstPathを入れてあげればよいでしょう。

VbFileAttribute型が何なのか、私も分からなかったので、F2のオブジェクトブラウザで見てみると、

上図のとおり、フォルダだとvbDirectory(16)とありますので、GetAttrの戻り値がvbDirectoryだったらフォルダという判定ができるということになります。

以上を踏まえて、ファイルかフォルダかチェックするステップを追加しました。現在掲示されているコードは、このステップを追加したものです(20230426現在)。

おまけ

今日作ったものをZIPにして置いておきます。

https://kn-sharoushi.com/wp-content/uploads/2023/04/20230425chie_ファイルの作成日時.zip

～～～宣伝～～～～

Excel相談では現在(=この記事執筆時点)無料で相談を受け付けています。すべてのご相談に回答できることは保証できませんが、本日の記事のようなことであれば分かりますので、こういった内容でお困りの場合はお気軽にご相談ください。

メールでのやり取りとなります。また、ご相談内容はブログで紹介されることがありますので、くれぐれも個人情報や機密情報をメールで送らないでください。また、ご相談は匿名でできます。

詳しくはこちら。