|
▼くま さん:
配列処理で
Sub Testa()
Dim io As Integer
Dim strFnm As String
Dim buf() As Byte
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Long
Dim y As Long
Dim z As Long
Dim d As Variant
Dim r As Variant
Dim v As Variant
Dim v1 As Variant
Dim v2 As Variant
Dim hd As Variant
strFnm = "D:\excel\test9\test.log"
If Dir(strFnm) = "" Then Exit Sub
hd = Array("hostname", "vlan ID")
Cells.ClearContents
Range("A1").Resize(1, 2).Value = hd
io = FreeFile()
Open strFnm For Binary As io
ReDim buf(1 To LOF(io))
Get io, , buf
Close io
v = Split(StrConv(buf, vbUnicode), "hostname")
For i = 1 To UBound(v)
d = Split(v(i), vbCrLf)
ReDim r(UBound(d), 1)
k = -1
For j = 0 To UBound(d)
On Error Resume Next
If Val(Split(d(j), "vlan ")(1)) Then
If Err.Number = 0 Then
v1 = Split(Split(d(j), "vlan ")(1), ",")
For x = 0 To UBound(v1)
v2 = Split(v1(x), "-")
If UBound(v2) > 0 Then
For z = v2(0) To v2(1)
k = k + 1
r(k, 0) = Trim(d(0))
r(k, 1) = z
Next
Else
k = k + 1
r(k, 0) = Trim(d(0))
r(k, 1) = Val(v1(x))
End If
Next
End If
End If
On Error GoTo 0
Next
Cells(Range("A" & Rows.Count).End(xlUp).Row + 1, 1).Resize(k + 1, 2).Value = r
Next
End Sub
|
|