|
さきほどのコード 出力先を(イミディエイト・ウィンドウから)
シート(ActiveSheet)に変更してみました。
他は変更なしです。
Sub Try4_MultiFile()
Const myPath = "C:\マクロ"
Dim io As Integer
Dim myLogFile As String
Dim buf() As Byte
Dim ss() As String, s As String, Series, num As Long
Dim hostname As String, ok As Boolean
Dim i As Long, j As Long
Dim vout() As String
Dim L As Long, Lmax As Long: Lmax = 500
ReDim vout(1 To 2, Lmax)
vout(1, 0) = "hostname": vout(2, 0) = "vlan ID"
Dim rex As RegExp
Dim mm As Match
Set rex = New RegExp
rex.Pattern = "[\d-]+"
rex.Global = True
ChDrive myPath
ChDir myPath
myLogFile = Dir("*.log", vbNormal)
Do While Len(myLogFile) > 0
'------------ ファイルを読み込む
io = FreeFile()
Open myLogFile For Binary As io
ReDim buf(1 To LOF(io))
Get io, , buf
Close io
ss = Split(StrConv(buf, vbUnicode), vbCrLf)
'------------ 抽出
For i = 0 To UBound(ss) - 1
If Not ok Then
If ss(i) Like "hostname*" Then
hostname = Split(ss(i))(1)
ok = True
End If
ElseIf ss(i) Like "vlan*" Then
If Mid$(ss(i), 6, 1) Like "#" Then
For Each mm In rex.Execute(ss(i))
s = mm.Value
If s <> "-" Then
Series = Split(s, "-")
num = Series(0)
For j = num To Val(Series(UBound(Series)))
L = L + 1
If L > Lmax Then
Lmax = Lmax + 500
ReDim Preserve vout(1 To 2, Lmax)
End If
vout(1, L) = hostname
vout(2, L) = j
Next
End If
Next
End If
End If
Next
ok = False '◆ファイル読みが終了したらok=Falseに戻す
'-------------- 次のログファイル
myLogFile = Dir$()
Loop
'-------------------- シートに書き出す
If L > 0 Then
With ActiveSheet
.UsedRange.ClearContents
.Range("A1").Resize(L, 2).Value = _
Application.Transpose(vout)
End With
Beep
End If
End Sub
|
|