資料作成用に必要なデータをコンフィグファイルから抜き出してexcelに添付するのですが、
元のファイルが膨大で人力・目視で作成するのは時間の無駄でミスが発生してしまうので、
慣れないマクロを使って楽する工夫をば。
コンフィグサンプル
object-group network MANAGEMENT-PC
network-object host 10.1.1.1
network-object host 10.1.1.2
network-object host 10.1.1.3
object-group network DB-Server
network-object host 172.16.1.1
object-group network AP-Server-1
network-object host 172.16.2.1
object-group network AP-Server-2
network-object host 172.16.2.2
object-group network AP-Server-3
network-object host 172.16.2.3
object-group network JIMU-PC
network-object host 192.168.1.0 255.255.255.0
object-group network AP-SV
group-object AP-SV-1
group-object AP-SV-2
group-object AP-SV-3access-list ACL_int1 extended permit ip object-group MANAGEMENT-PC object-group DB-Server
access-list ACL_int1 extended permit ip object-group MANAGEMENT-PC object-group AP-Server
access-list ACL_int1 extended permit ip object-group MANAGEMENT-PC object-group JIMU-PC
access-list ACL_int1 extended permit icmp any any
access-list ACL_out1 extended permit ip object-group JIMU-PC object-group AP-Server
access-list ACL_out1 extended permit icmp any anyaccess-group ACL_int1 in interface inside
access-group ACL_out1 in interface outside
マクロ
Sub ボタン1_Click()
End Sub
Sub IPアドレスを一つのセルにまとめる()
'
' IPアドレスを一つのセルにまとめる Macro
'
Const TOP_OBJECT As Integer = 5 'コンフィグを入れる最初の行
Const IP_ADDRESS_COLUMN = 4 'IPアドレスがある列
Dim target_cells_top As Integer '一まとめにする対象のセルの一番上
Dim target_cells_bottom As Integer '一まとめにする対象のセルの一番下
Dim ipaddress As String '格納する文字
Dim put_cell As Range '格納するセルDim last_row As Integer '最終行を格納する変数
Dim i As Integer
Dim j As Integer
Dim k As Integer''''''''''''''''''''''''''''''''''''''''''''
'最終行の取得'最終行の取得ここまで
'初期化処理
ipaddress = ""
last_row = Cells(Rows.Count, 2).End(xlUp).Row
target_cells_top = TOP_OBJECT + 1
'初期化処理ここまで
For i = TOP_OBJECT To last_row
If Cells(i + 1, 1).Value = "" And i <> last_row Then
'空白行なら右端のセル(IPアドレス)を選択する
target_cells_bottom = i + 1
ElseIf Cells(i + 1, 1).Value <> "" Or i = last_row Then
'一まとめにする処理
For j = target_cells_top To target_cells_bottom
If ipaddress = "" Then
ipaddress = Cells(j, IP_ADDRESS_COLUMN).Value
Else
ipaddress = ipaddress & vbLf & Cells(j, IP_ADDRESS_COLUMN).Value
End If
Next
Cells(target_cells_top - 1, IP_ADDRESS_COLUMN).Value = ipaddress
ipaddress = ""
target_cells_top = target_cells_bottom + 2 'ipアドレスがある行の次の次の行から対象セル
target_cells_bottom = target_cells_top
End If
Next
'
Application.Goto Reference:="IPアドレスを一つのセルにまとめる"
Range("F6").Select
End SubSub セル区切り()
'
' セル区切り Macro
''
Range("A5:E18").Select
Selection.ClearContents
Sheets("元データ抜粋").Select
Range("A2:A15").Select
Selection.Copy
Sheets("object整地").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A5:A18").Select
Selection.TextToColumns Destination:=Range("A5"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
True
End Sub
Sub Macro6()
'
' Macro6 Macro
''
End Sub
Sub ボタン3_Click()
Const TOP_OBJECT As Integer = 5 'コンフィグを入れる最初の行
Const IP_ADDRESS_COLUMN = 4 'IPアドレスがある列
Dim target_cells_top As Integer '一まとめにする対象のセルの一番上
Dim target_cells_bottom As Integer '一まとめにする対象のセルの一番下
Dim ipaddress As String '格納する文字
Dim put_cell As Range '格納するセルDim delete_row As String
Dim last_row As Integer '最終行を格納する変数Dim i As Integer
Dim j As Integer
Dim k As Integer''''''''''''''''''''''''''''''''''''''''''''
'最終行の取得'最終行の取得ここまで
'初期化処理
ipaddress = ""
last_row = Cells(Rows.Count, 2).End(xlUp).Row
target_cells_top = TOP_OBJECT + 1
'初期化処理ここまで
For i = TOP_OBJECT To last_row
If Cells(i + 1, 1).Value = "" And i <> last_row Then
'空白行なら右端のセル(IPアドレス)を選択する
target_cells_bottom = i + 1
ElseIf Cells(i + 1, 1).Value <> "" Or i = last_row Then
Rows(target_cells_top & ":" & target_cells_bottom).Select
Selection.ClearContents
If delete_row = "" Then
delete_row = target_cells_top & ":" & target_cells_bottom
Else
delete_row = delete_row & "," & target_cells_top & ":" & target_cells_bottom
End If
target_cells_top = target_cells_bottom + 2 'ipアドレスがある行の次の次の行から対象セル
target_cells_bottom = target_cells_top
End IfNext
Range(delete_row).Select
'選択したセルがある行全体を削除
Selection.EntireRow.Delete
'選択状態の解除(一応)
Application.CutCopyMode = False
End Sub