mp3タグが文字化けしないよう、文字コードが調べたかった。
最初の3バイトが「ID3」で21バイト目が「01」ならOK!とやるだけでよかった。
それだけでよかったのに、、、
なんか長いのが出来た。
独学なので勘弁。
Option Explicit Sub メイン処理() Dim mp3b As classBinaryID3V2 Dim fileList() As String Dim iRow As Long Dim tmp As String Cells.ClearContents 'このエクセルファイルが置かれたフォルダが対象 Call makeFileList(ThisWorkbook.Path, True, fileList) Dim i As Long For i = LBound(fileList) To UBound(fileList) If Right(fileList(i), 4) <> ".mp3" Then GoTo fileSkip Set mp3b = New classBinaryID3V2 With mp3b Cells(i, 1).Value = fileList(i) '先頭10バイトだけ読み込む。 .SetHexList = readBinary(fileList(i), 10) 'v2.3とv2.4以外を弾く。 Dim strErrMsg As String: strErrMsg = .checkID3v2 If Len(strErrMsg) > 0 Then Cells(i, 2).Value = strErrMsg: GoTo fileSkip End If 'ヘッダーとフレームを全部読み込む。 .SetHexList = readBinary(fileList(i), .GetAllFrameSize + 10) .NextFrame '最初のフレームをセット。 Do While .GetFrameID <> "" '文字コードチェック 'タイトル、アーティスト、アルバムを確認 Select Case .GetFrameID Case "TIT2", "TPE1", "TALB" tmp = .GetFrameCharacterCode Cells(i, 2).Value = Choose(Int(tmp) + 1, "ISO-8859-1", "UTF-16", "UTF-16", "UTF-8") End Select 'タグ情報出力。 Select Case .GetFrameID Case "TIT2" 'タイトル Cells(i, 3).Value = .GetFrameValue Case "TPE1" 'アーティスト Cells(i, 4).Value = .GetFrameValue Case "TALB" 'アルバム Cells(i, 5).Value = .GetFrameValue Case "TRCK" 'トラック Cells(i, 6).Value = .GetFrameValue Case "TCON" 'ジャンル Cells(i, 7).Value = .GetFrameValue Case "COMM" 'コメント Cells(i, 8).Value = .GetFrameValue Case Else End Select .NextFrame '次のフレームを読み込む。 Loop End With fileSkip: Set mp3b = Nothing Next 'カラム名後付け Rows(1).Insert Range("A1:H1").Value = Array("フルパス", "メモ", "タイトル", "アーティスト", "アルバム", "トラック", "ジャンル", "コメント") End Sub
標準モジュールに追加
Option Explicit '16進数を10進数に変換 Function HexToDec(sHex As String) As Long HexToDec = BinToDec(HexToBin(sHex)) End Function '2進数を10進数に変換 Function BinToDec(sBin As String) As Long Dim tmp As String tmp = StrReverse(sBin) '文字を逆にする Dim iSum As Long: iSum = 0 Dim i As Long For i = 1 To Len(tmp) If Mid(tmp, i, 1) = "1" Then iSum = iSum + 2 ^ (i - 1) End If Next BinToDec = iSum End Function 'Syncsafe Integerを計算。16進数を10進数で返却。 '最上位8ビット目をスキップして連結 Function HexToSyncsafeInteger(sHex As String) As Long Dim rtnStr As String: rtnStr = "" Dim tmp As String tmp = HexToBin(sHex) '16進数を2進数に変換 tmp = StrReverse(tmp) '文字を逆にする '最上位8ビット目をスキップして連結 Dim i As Long For i = 1 To Len(tmp) If i Mod 8 <> 0 Then rtnStr = Mid(tmp, i, 1) & rtnStr Next HexToSyncsafeInteger = BinToDec(rtnStr) End Function '16進数を2進数に変換(マイナスとか色々考慮してません) Function HexToBin(sHex As String) As String Dim rtnStr As String: rtnStr = "" Dim tmp As String Dim i As Long For i = Len(sHex) To 1 Step -1 tmp = WorksheetFunction.Hex2Bin(Mid(sHex, i, 1)) tmp = Right("000" & tmp, 4) '桁埋め rtnStr = tmp & rtnStr Next HexToBin = rtnStr End Function Sub 単体チェック用() Dim filePath As String filePath = "C:\音楽\〇〇〇.mp3" Call getMp3Info(filePath) End Sub '単体チェック用 Function getMp3Info(filePath As String) As Boolean Dim iFrameCnt As Long: iFrameCnt = 0 Dim mp3b As classBinaryID3V2 Set mp3b = New classBinaryID3V2 With mp3b '先頭10バイトだけ読み込む。 .SetHexList = readBinary(filePath, 10) 'v2.3とv2.4以外を弾く。 Dim strErrMsg As String: strErrMsg = .checkID3v2 If Len(strErrMsg) > 0 Then Debug.Print strErrMsg: GoTo readEnd 'ヘッダー部分を全部読み込む。 .SetHexList = readBinary(filePath, .GetAllFrameSize + 10) Debug.Print .GetAllTagBinary Debug.Print "----------------------------------------" Debug.Print .GetHeaderBinary(" ") Debug.Print .GetHeaderID, .GetVersion, .GetAllFrameSize .NextFrame '次のフレーム(最初) Do While .GetFrameID <> "" Debug.Print "----------------------------------------" iFrameCnt = iFrameCnt + 1 If iFrameCnt > 20 Then Exit Do '安全装置。タグが20個以上あるならコメントアウト。 Debug.Print .GetFrameBinary(" ") Debug.Print .GetFrameID, .GetFrameNameJP, .GetFrameSize, .GetFrameValue .NextFrame '次のフレーム Loop End With readEnd: Set mp3b = Nothing End Function 'ファイルをバイナリ形式で読み出す。オプションでバイトサイズ指定。 Function readBinary(filePath As String, Optional readNumByte As Long = 0) As String() 'ファイルサイズが0バイトは終了 Dim iFileLen As Long iFileLen = FileLen(filePath) If iFileLen = 0 Then End Dim iFileNo As Integer iFileNo = FreeFile On Error GoTo OpenErr Open filePath For Binary As #iFileNo Dim bData() As Byte If readNumByte = 0 Then ReDim bData(1 To iFileLen) '全て読み込む Else ReDim bData(1 To readNumByte) End If Get #iFileNo, , bData Close #iFileNo '読み込んだデータの整形 Dim sHex() As String ReDim sHex(LBound(bData) To UBound(bData)) Dim i As Long For i = LBound(bData) To UBound(bData) sHex(i) = Right("0" & Hex(bData(i)), 2) Next OpenErr: readBinary = sHex End Function 'ファイルを検索してファイルリストを返却。 Sub makeFileList(sFolderPath As String, subFolderSearch As Boolean, ByRef fileList() As String) Dim fso As Object Dim folder As Object Dim subfolder As Object Dim file As Object Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(sFolderPath) 'サブフォルダが無ければループに入らず次へ If subFolderSearch Then For Each subfolder In folder.SubFolders Call makeFileList(subfolder.Path, subFolderSearch, fileList) Next subfolder End If For Each file In folder.files 'Debug.Print file.Name, file.Path, file.Size Call addArrayString(fileList, file.Path) Next file Set subfolder = Nothing Set folder = Nothing Set fso = Nothing End Sub '配列を1つ増やして追加する。 Private Sub addArrayString(ByRef arr, s) On Error GoTo add1 Dim i As Long: i = 0 i = UBound(arr) '配列でない場合にエラー add1: i = i + 1 ReDim Preserve arr(1 To i) arr(i) = s End Sub
classBinaryID3V2クラスを作成
Option Explicit Private sHex() As String 'バイナリ Private Location As Long 'フレームの開始位置 Public FrameCount As Long 'フレーム数 '主要タグ Public TagTitle As String Public TagArtist As String Public TagAlbum As String Public TagTrackNo As String Public TagGenre As String Public TagComment As String 'コンストラクタ Private Sub Class_Initialize() Location = 1 FrameCount = 0 TagTitle = "" TagArtist = "" TagAlbum = "" TagTrackNo = "" TagGenre = "" TagComment = "" End Sub 'バイナリを読み込む。 Public Property Let SetHexList(sHexList() As String) sHex = sHexList End Property '全てのフレームを巡回し、主要タグのテキストを取得する。 Public Sub AllTagRead() Dim backupLocation As Long backupLocation = Location FrameCount = 0 Location = 1 NextFrame Do While GetFrameID <> "" FrameCount = FrameCount + 1 Select Case GetFrameID Case "TIT2" 'タイトル TagTitle = GetFrameValue Case "TPE1" 'アーティスト TagArtist = GetFrameValue Case "TALB" 'アルバム TagAlbum = GetFrameValue Case "TRCK" 'トラック TagTrackNo = GetFrameValue Case "TCON" 'ジャンル TagGenre = GetFrameValue Case "COMM" 'コメント TagComment = GetFrameValue Case "APIC" 'アートワーク Case Else End Select NextFrame '次のフレームを読み込む。 Loop Location = backupLocation End Sub 'sHex配列の数を返す。 Private Function sHex_getCount() As Long Dim iCnt As Long: iCnt = 0 On Error GoTo Err1 iCnt = UBound(sHex) Err1: sHex_getCount = iCnt End Function 'sHex配列から指定した範囲で文字列を取得し、そのまま返す。 Private Function sHex_getString(iStart As Long, iLen As Long, Optional sDelimiter As String = "") As String Dim tmp As String: tmp = "" Dim i As Long Dim iEnd As Long: iEnd = (iStart + iLen - 1) If sHex_getCount < iEnd Then iEnd = sHex_getCount For i = iStart To iEnd If Len(sDelimiter) > 0 And Len(tmp) > 0 Then tmp = tmp & sDelimiter tmp = tmp & sHex(i) Next sHex_getString = tmp End Function 'sHex配列から指定した範囲で文字列を取得し、アスキー変換する。 Private Function sHex_getStringAscii(iStart As Long, iLen As Long) As String Dim tmp As String: tmp = "" Dim i As Long For i = iStart To (iStart + iLen - 1) tmp = tmp & Chr(HexToDec(sHex(i))) Next sHex_getStringAscii = tmp End Function 'sHex配列から指定した範囲で文字列を取得し、Shift-JISに変換する。 Private Function sHex_getStringShiftJIS(iStart As Long, iLen As Long) As String Dim sFrameVal As String: sFrameVal = "" Dim i As Long, tmp As String Dim iEnd As Long: iEnd = (iStart + iLen - 1) For i = iStart To iEnd 'If sHex(i) = "00" And i = iEnd Then Exit For '終了フラグ If sHex(i) = "00" Then Exit For '終了フラグ If Is2byteShiftJIS(sHex(i), sHex(i + 1)) Then tmp = sHex(i) & sHex(i + 1) i = i + 1 Else tmp = sHex(i) End If sFrameVal = sFrameVal & Chr(HexToDec(tmp)) Next sHex_getStringShiftJIS = sFrameVal End Function 'sHex配列から指定した範囲で文字列を取得し、Unicodeに変換する。 Private Function sHex_getStringUTF16(iStart As Long, iLen As Long, flgBigEndian As Boolean) As String Dim sFrameVal As String: sFrameVal = "" Dim i As Long, tmp As String For i = iStart To (iStart + iLen - 1) Step 2 If flgBigEndian Then tmp = sHex(i) & sHex(i + 1) Else tmp = sHex(i + 1) & sHex(i) End If If tmp = "0000" Then Exit For '終了フラグ sFrameVal = sFrameVal & ChrW(HexToDec(tmp)) Next sHex_getStringUTF16 = sFrameVal End Function 'sHex配列から指定した範囲で文字列を取得し、UTF-8に変換する。UTF-8はBE、LEの概念が無い。 Private Function sHex_getStringUTF8(iStart As Long, iLen As Long) As String Dim sFrameVal As String: sFrameVal = "" Dim tmp As String, tmpBin As String Dim i As Long, k As Long, iByte As Long Dim iEnd As Long: iEnd = (iStart + iLen - 1) For i = iStart To iEnd If sHex(i) = "00" Then Exit For '終了フラグ iByte = NumByteUTF8(sHex(i)) If iByte = 0 Then Exit For Select Case iByte Case 1 tmpBin = Right(HexToBin(sHex(i)), 7) Case 2 To 6 tmpBin = Right(HexToBin(sHex(i)), 7 - iByte) For k = 1 To iByte - 1 tmp = Right(HexToBin(sHex(i + k)), 6) tmpBin = tmpBin + tmp Next Case Else '到達不可 End Select tmp = ChrW(BinToDec(tmpBin)) sFrameVal = sFrameVal & tmp i = i + iByte - 1 Next sHex_getStringUTF8 = sFrameVal End Function 'UTF-8のバイト数を判定。 Private Function NumByteUTF8(sHex1 As String) As Integer Dim rtnInt As Integer Dim sBin As String: sBin = HexToBin(sHex1) Select Case True Case Left(sBin, 2) = "10" rtnInt = 0 Debug.Print "先頭バイトではありません(" & sHex1 & ")" Case Left(sBin, 1) = "0" rtnInt = 1 Case Left(sBin, 3) = "110" rtnInt = 2 Case Left(sBin, 4) = "1110" rtnInt = 3 Case Left(sBin, 5) = "11110" rtnInt = 4 Case Left(sBin, 6) = "111110" rtnInt = 5 Case Left(sBin, 7) = "1111110" rtnInt = 6 Case Else rtnInt = 0 Debug.Print "判別不能(" & sHex1 & ")" End Select NumByteUTF8 = rtnInt End Function 'Shift-JISのバイト数を判定。超無理矢理。1or2バイト。 '2バイト文字の1バイト目は0x81~9F、0xE0~EF。 '2バイト文字の2バイト目は0x40~7F、0x80~FC。 'これ以外を1バイト文字と判定。 Private Function Is2byteShiftJIS(sHex1 As String, sHex2 As String) As Boolean Is2byteShiftJIS = False If (HexToDec("81") <= HexToDec(sHex1) And HexToDec(sHex1) <= HexToDec("9F")) Or _ (HexToDec("E0") <= HexToDec(sHex1) And HexToDec(sHex1) <= HexToDec("EF")) Then If (HexToDec("40") <= HexToDec(sHex2) And HexToDec(sHex2) <= HexToDec("7F")) Or _ (HexToDec("80") <= HexToDec(sHex2) And HexToDec(sHex2) <= HexToDec("FC")) Then Is2byteShiftJIS = True End If End If End Function 'ヘッダーIDを取得(マジックナンバーID3固定) Property Get GetHeaderID() As String '1~3バイト固定 If sHex_getCount < 3 Then GetHeaderID = "" Else GetHeaderID = sHex_getStringAscii(1, 3) End If End Property 'ID3v2のバージョンを取得。 Property Get GetVersion() As String Dim tmp As String If sHex_getCount < 5 Then tmp = "" Else Select Case sHex(4) & sHex(5) '4~5バイト固定 Case "0200" tmp = "2" Case "0300" tmp = "3" Case "0400" tmp = "4" Case Else tmp = "" End Select End If GetVersion = tmp End Property 'ヘッダーのフラグを渡す。非同期化、拡張ヘッダ、実験中等を示す。 Property Get GetHeaderFlg() As String '6バイト目固定 If sHex_getCount < 6 Then GetHeaderFlg = "" Else GetHeaderFlg = sHex_getString(6, 1) End If End Property 'ID3v2.3、v2.4であることを確認する。問題無ければ空文字を返す。 Function checkID3v2() As String Dim rtnStr As String: rtnStr = "" Dim tmp As String Dim iCnt As Long: iCnt = sHex_getCount If iCnt < 10 Then rtnStr = "読み込めませんでした。(size " & iCnt & ")": GoTo checkEnd End If If GetHeaderID <> "ID3" Then rtnStr = "ID3v2のヘッダーではありません": GoTo checkEnd End If tmp = GetVersion If tmp <> "3" And tmp <> "4" Then rtnStr = "ID3v2.3かID3v2.4である必要があります。(ID3v2." & tmp & ")": GoTo checkEnd End If tmp = GetHeaderFlg If tmp <> "00" Then rtnStr = "フラグ(" & tmp & ")を持つファイルです。読み込みを中止しました。": GoTo checkEnd End If checkEnd: checkID3v2 = rtnStr End Function 'ヘッダーに書かれている全体フレームサイズを渡す。 Property Get GetAllFrameSize() As Long '7~10バイト固定 Dim tmp As String If sHex_getCount < 10 Then GetAllFrameSize = "" Else tmp = sHex(7) & sHex(8) & sHex(9) & sHex(10) GetAllFrameSize = HexToSyncsafeInteger(tmp) End If End Property '次のフレームを読み込む。 Public Sub NextFrame() If Location = 1 Then Location = 11 Else Dim iFS As Long: iFS = GetFrameSize If iFS > 0 Then Location = Location + iFS + 10 End If End Sub '指定されたフレームIDを探す。見つからなければ空文字を返す。 '本処理を使用してフレームが見つからなかった場合、Location情報は失われる。 Property Get SearchFrame(sFrameID As String) As String Location = 11 Dim tmp As String Dim tmpFrameID As String: tmpFrameID = "" Dim iFS As Long Do While Location < GetAllFrameSize tmp = sHex_getString(Location, 1) If tmp = "00" Then Exit Do tmpFrameID = sHex_getStringAscii(Location, 4) If tmpFrameID = sFrameID Then Exit Do iFS = GetFrameSize If iFS > 0 Then Location = Location + iFS + 10 Loop If tmpFrameID = sFrameID Then SearchFrame = tmpFrameID Else Location = 1 SearchFrame = "" End If End Property '現在のフレームIDを渡す。各フレームの頭1~4バイト固定。 Property Get GetFrameID() As String If Location < GetAllFrameSize Then Dim tmp As String tmp = sHex_getString(Location, 1) If tmp = "00" Then GetFrameID = "" '1バイト目が00なら空文字を返す Else GetFrameID = sHex_getStringAscii(Location, 4) End If Else GetFrameID = "" End If End Property '現在のフレームサイズを渡す。各フレームの5~8バイト固定。 Property Get GetFrameSize() As Long Dim tmp As String tmp = sHex_getString(Location + 4, 4) GetFrameSize = HexToDec(tmp) End Property '現在フレームのフラグを返す。各フレームの9~10バイト固定。 Property Get GetFrameHeaderFlg() As String GetFrameHeaderFlg = sHex_getString(Location + 8, 2) End Property '現在フレームの値について、文字コード部を返す。 Property Get GetFrameCharacterCode() As String '00:ISO-8859-1、中身はShift-JISを想定。 '01:UTF-16BOM有 '02:UTF-16BOM無(v2.4) '03:UTF-8(v2.4) Dim tmp As String tmp = sHex_getString(Location + 10, 1) GetFrameCharacterCode = tmp End Property '現在フレームの値を返す。 Property Get GetFrameValue() As String Dim tmp As String Dim rtnStr As String: rtnStr = "" Dim thisLocation As Long thisLocation = Location + 10 Dim strCode As String Dim flgBigEndian As Boolean Dim iOffset As Long '最初の1バイトは文字コード判別用。 strCode = sHex_getString(thisLocation, 1) Select Case strCode Case "00" '00:ISO-8859-1、中身はShift-JISを想定。 rtnStr = sHex_getStringShiftJIS(thisLocation + 1, GetFrameSize - 1) Case "01", "02" '01:UTF-16BOM有、02:UTF-16BOM無(v2.4) iOffset = 1 '文字コード分 flgBigEndian = False tmp = sHex(thisLocation + 1) & sHex(thisLocation + 2) 'BOMの判別 If tmp = "FEFF" Or strCode = "02" Then: flgBigEndian = True If tmp = "FEFF" Or tmp = "FFFE" Then iOffset = iOffset + 2 'BOM分 rtnStr = sHex_getStringUTF16(thisLocation + iOffset, GetFrameSize - iOffset, flgBigEndian) Case "03" '03:UTF-8(v2.4) iOffset = 1 '文字コード分 tmp = sHex_getString(thisLocation + 1, 3) 'BOMの判別。3バイト。 If tmp = "EFBBBF" Then iOffset = iOffset + 3 'BOM分 rtnStr = sHex_getStringUTF8(thisLocation + iOffset, GetFrameSize - iOffset) Case Else Debug.Print "文字コード指定がありません。開始文字(" & strCode & ")" rtnStr = sHex_getStringShiftJIS(thisLocation, GetFrameSize) End Select GetFrameValue = rtnStr End Property '主要抜粋。 Property Get GetFrameNameJP() As String Dim rtnStr As String Select Case GetFrameID Case "" rtnStr = "" Case "TIT2" rtnStr = "タイトル" Case "TPE1" rtnStr = "アーティスト" Case "TALB" rtnStr = "アルバム" Case "TCON" rtnStr = "ジャンル" Case "TRCK" rtnStr = "トラックNo" Case "TYER" rtnStr = "リリース年" 'TYER(v2.3)、TDRC(v2.4) Case "COMM" rtnStr = "コメント" Case "APIC" rtnStr = "アートワーク" Case Else rtnStr = "" Debug.Print "■フレーム「" & GetFrameID & "」初出" End Select GetFrameNameJP = rtnStr End Property '以下、デバッグ用。 'ヘッダーのバイナリを返す(デバッグ用) Property Get GetHeaderBinary(Optional sDmt As String) As String GetHeaderBinary = sHex_getString(1, 10, sDmt) End Property 'フレームのバイナリを返す(デバッグ用) Property Get GetFrameBinary(Optional sDmt As String) GetFrameBinary = sHex_getString(Location, GetFrameSize + 10, sDmt) End Property 'ID3v2の全バイナリを返す。mp3本体以降は含まない。(デバッグ用) Property Get GetAllTagBinary(Optional sDmt As String) GetAllTagBinary = sHex_getString(1, GetAllFrameSize + 10, sDmt) End Property
書き換えはタグ編集ソフトでやる。
以上。