【资料名称】：虹信 AC数据制作模板

【资料作者】：dd

【资料日期】：2012-10-15

【资料语言】：中文

【资料格式】：DOC

【资料目录和简介】：

Sub AP()
Const ForReading = 1, ForWriting = 2, ForAppending = 8, TristateFalse = 0
Set fso = CreateObject("Scripting.FileSystemObject")

'创建wlan部分代码
Dim crewlan(4) As Variant
crewlan(0) = Range("sheet1!a3")
crewlan(1) = Range("sheet1!a4")
crewlan(2) = Range("sheet1!b3")
crewlan(3) = Range("sheet1!b4")
crewlan(4) = Range("sheet1!c12")

Dim cre(12) As Variant
cre(0) = Range("sheet1!b7")
cre(1) = Range("sheet1!a15")
cre(2) = Range("sheet1!b15")
cre(3) = Range("sheet1!c15")
cre(4) = Range("sheet1!d14")
cre(5) = Range("sheet1!d15")
cre(6) = Range("sheet2!a5")
cre(7) = Range("sheet1!a12")
cre(8) = Range("sheet1!b12")
cre(10) = Range("sheet1!e14")
cre(11) = Range("sheet1!e15")
cre(12) = Range("sheet1!c7")

Dim path(1) As String
path(0) = ActiveWorkbook.path
path(1) = Range("sheet1!d12")

Dim mac As Variant
mac = Range("sheet1!d7")

Dim na(1) As Variant
na(0) = Range("sheet1!b7")
na(1) = Range("sheet1!c7")
If mac = "从交换机读MAC" Then

'把sheet2A列mac地址区间，赋值给一个数组，创建wtp时，包含mac
With Sheet2.Range("a5:a504")
o = .Count
ReDim c(1 To o)
For p = 1 To o
c(p) = .Cells(p).Text
Next
End With
Else
With Worksheets("sheet3").Range("b2:b201")
o = .Count
ReDim c(1 To o)
For p = 1 To o
c(p) = .Cells(p).Text
Next
End With
End If
'wtpname 中加上楼层标识
With Worksheets("sheet3").Range("c2:c201")
x = .Count
ReDim f(1 To x)
For y = 1 To x
f(y) = .Cells(y).Text
Next
End With
'配置频点
With Worksheets("sheet3").Range("d2:d201")
Z = .Count
ReDim ch(1 To Z)
For u = 1 To Z
ch(u) = .Cells(u).Text
Next
End With

'判断指定路径文件是否存在，不存在则创建，存在则打开
Select Case crewlan(0)
Case 0
If Dir(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt") = "" Then
Set sFile = fso.createTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
Else
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
End If
Case Else
If Dir(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt") = "" Then
Set sFile = fso.createTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
Else
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
End If
End Select
'interface code
' sFile.WriteLine ("interface eth0-" & cre(7) & "." & cre(8))
' sFile.WriteLine ("ip add xxx.xxx.xxx.xxx/24")
' sFile.WriteLine ("exit")
'wlan code
If crewlan(0) <> 0 Then
sFile.WriteLine ("create wlan " & crewlan(0) & " " & crewlan(1) & " " & crewlan(1))
sFile.WriteLine ("config wlan " & crewlan(0))
sFile.WriteLine ("apply securityID " & crewlan(4))
sFile.WriteLine ("wlan apply interface eth0-" & cre(7) & "." & cre(8))
sFile.WriteLine ("exit")
End If
If crewlan(2) = 0 Then
Set fso = Nothing
Set sFile = Nothing
Else
sFile.WriteLine ("create wlan " & crewlan(2) & " " & crewlan(3) & " " & crewlan(3))
sFile.WriteLine ("config wlan " & crewlan(2))
sFile.WriteLine ("apply securityID " & crewlan(4))
sFile.WriteLine ("wlan apply interface eth0-" & cre(7) & "." & cre(8))
sFile.WriteLine ("exit")
Set fso = Nothing
Set sFile = Nothing
End If


'creat wtp 代码
Dim k As Integer
k = 1
Dim kk As Variant
kk = Sheet1.Range("f15")
For n = cre(0) To cre(12)
Set fso = CreateObject("Scripting.FileSystemObject")
Select Case crewlan(0)
Case 0
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
Case Else
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
End Select
If mac = "手抄MAC" Then
sFile.WriteLine ("create wtp " & n & " " & cre(1) & cre(2) & f(k) & cre(3) & " " & cre(4) & " " & cre(5) & " " & c(k))
Else
Select Case Sheet1.Range("f14")
Case "默认"
sFile.WriteLine ("create wtp " & n & " " & cre(1) & cre(2) & k & cre(3) & " " & cre(4) & " " & cre(5) & " " & c(k))

Case "指定"
sFile.WriteLine ("create wtp " & n & " " & cre(1) & cre(2) & kk & cre(3) & " " & cre(4) & " " & cre(5) & " " & c(k))
kk = kk + 1
End Select
End If
sFile.WriteLine ("config wtp " & n)
sFile.WriteLine ("wtp apply interface eth0-" & cre(7) & "." & cre(8))
sFile.WriteLine ("set wtp max sta num 15")
sFile.WriteLine ("exit")

'配置radio

sFile.WriteLine ("config radio " & 4 * cre(0))

5: If crewlan(2) = 0 Then
If crewlan(0) <> 0 Then
sFile.WriteLine ("radio apply wlan " & crewlan(0))
If Sheet1.Range("e11") = "限速" Then
sFile.WriteLine ("wlan " & crewlan(0) & " traffic limit station average send value " & Sheet1.Range("e12"))
End If
End If
sFile.WriteLine (cre(10) & " " & cre(11))
If mac = "手抄MAC" Then
sFile.WriteLine ("channel " & ch(k))
End If
sFile.WriteLine ("exit")
sFile.Close
cre(0) = cre(0) + 1
k = k + 1

Set fso = Nothing
Set sFile = Nothing
55: Else
If crewlan(0) <> 0 Then
sFile.WriteLine ("radio apply wlan " & crewlan(0))
End If

If crewlan(2) <> 0 Then
sFile.WriteLine ("radio apply wlan " & crewlan(2))
End If

If crewlan(0) <> 0 Then
If Sheet1.Range("e11") = "限速" Then
sFile.WriteLine ("wlan " & crewlan(0) & " traffic limit station average send value " & Sheet1.Range("e12"))
End If
End If

If crewlan(2) <> 0 Then
If Sheet1.Range("e11") = "限速" Then
sFile.WriteLine ("wlan " & crewlan(2) & " traffic limit station average send value " & Sheet1.Range("e12"))
End If
End If

sFile.WriteLine (cre(10) & " " & cre(11))
If mac = "手抄MAC" Then
sFile.WriteLine (" channel " & ch(k))
End If
sFile.WriteLine ("exit")
sFile.Close
cre(0) = cre(0) + 1
k = k + 1
555: End If
Next n
k = 1
cre(0) = Range("sheet1!b7")


'intface radio 代码段
' For j = cre(0) To cre(12)
Set fso = CreateObject("Scripting.FileSystemObject")
Select Case crewlan(0)
Case 0
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
Case Else
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
End Select
' sFile.WriteLine ("interface radio" & j & "-0." & crewlan(0))
' sFile.WriteLine ("exit")
If crewlan(0) <> 0 Then
sFile.WriteLine ("batch-config <" & cre(0) & "-" & cre(12) & "> command int radio*-0." & crewlan(0) & " $ exit")
End If
If crewlan(2) = 0 Then
sFile.Close
Set fso = Nothing
Set sFile = Nothing
Else
' sFile.WriteLine ("interface radio" & j & "-0." & crewlan(2))
' sFile.WriteLine ("exit")
sFile.WriteLine ("batch-config <" & cre(0) & "-" & cre(12) & "> command int radio*-0." & crewlan(2) & " $ exit")
sFile.Close
Set fso = Nothing
Set sFile = Nothing
End If
' Next j


'ebr 配置段代码
Set fso = CreateObject("Scripting.FileSystemObject")
Select Case crewlan(0)
Case 0
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
Case Else
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
End Select
'sFile.WriteLine ("config ebr 1")
'For m = cre(0) To cre(12)
' sFile.WriteLine ("set ebr add interface radio" & m & "-0." & crewlan(0))
If crewlan(0) <> 0 Then
sFile.WriteLine ("batch-config <" & cre(0) & "-" & cre(12) & "> command conf ebr 1 $ set ebr add int radio*-0." & crewlan(0))
End If
If crewlan(2) <> 0 Then
' sFile.WriteLine ("set ebr add interface radio" & m & "-0." & crewlan(2))
sFile.WriteLine ("batch-config <" & cre(0) & "-" & cre(12) & "> command conf ebr 1 $ set ebr add int radio*-0." & crewlan(2))
End If
'Next m
'sFile.WriteLine ("service enable")
'sFile.WriteLine ("exit")
sFile.Close
Set fso = Nothing
Set sFile = Nothing



'wtp used 代码
'For i = cre(0) To cre(12)
Set fso = CreateObject("Scripting.FileSystemObject")
Select Case crewlan(0)
Case 0
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
Case Else
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
End Select
'sFile.WriteLine ("config wtp " & i)
'sFile.WriteLine ("wtp used")
'sFile.WriteLine ("exit")
sFile.WriteLine ("batch-config <" & cre(0) & "-" & cre(12) & "> command config wtp * $ wtp used $ exit")
sFile.Close
Set fso = Nothing
Set sFile = Nothing
'Next i


'wlan 启用
Set fso = CreateObject("Scripting.FileSystemObject")
Select Case crewlan(0)
Case 0
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ EDU" & crewlan(2) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
Case Else
Set sFile = fso.openTextFile(path(0) & "\" & path(1) & "-" & "AC" & Sheet1.Range("c3") & "-" & cre(8) & " [ Wlan" & crewlan(0) & " ] " & "( Ap" & na(0) & "-" & na(1) & " ).txt", ForAppending, TristateFalse)
End Select
If crewlan(0) <> 0 Then
sFile.WriteLine ("config wlan " & crewlan(0))
sFile.WriteLine ("service enable")
sFile.WriteLine ("exit")
End If
If crewlan(2) <> 0 Then
sFile.WriteLine ("config wlan " & crewlan(2))
sFile.WriteLine ("service enable")
sFile.WriteLine ("exit")
End If
sFile.Close
Set fso = Nothing
Set sFile = Nothing

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'将提取的mac保存到热点文件,如果 热点mac文件存在或是sheet2 A5没有mac,则不创建文件直接退出过程.
Dim path As String
path = ActiveWorkbook.path

Dim x1, x2, xx As Integer

x1 = WorksheetFunction.CountA(Sheet2.Range("A5:A504"))

x2 = WorksheetFunction.CountA(Sheet2.Range("e5:e504"))

If x1 = x2 Then
xx = 1
Else: xx = 0
End If

Const ForReading = 1, ForWriting = 2, ForAppending = 8, TristateFalse = 0
Set fso = CreateObject("Scripting.FileSystemObject")
If Dir(path & "\" & Sheet1.Range("d12") & "-AC" & Sheet1.Range("c3") & "MAC.txt") = "" Then
Select Case Sheet2.Range("A5")
Case Is <> ""
Set sFile = fso.createTextFile(path & "\" & Sheet1.Range("d12") & "-AC" & Sheet1.Range("c3") & "MAC.txt", ForAppending, TristateFalse)
Case ""
'Set sFile = fso.openTextFile(path & "\" & Sheet1.Range("d12") &"-AC"& sheet1.range("c3") & "MAC.txt", ForAppending, TristateFalse)
GoTo byebye
End Select
Else
GoTo byebye
End If
sFile.WriteLine ("提取的")
sFile.WriteLine ("")
sFile.WriteLine ("")
For i = 1 To 500
If Sheet2.Range("A" & i + 4) <> "" Then
Select Case xx
Case 1
sFile.WriteLine (Sheet2.Range("A" & (i + 4)) & "" & Sheet2.Range("E" & (i + 4)))
Case 0
sFile.WriteLine (Sheet2.Range("A" & (i + 4)))
End Select
End If
Sheet2.Range("A" & (i + 4)) = ""
Sheet2.Range("E" & (i + 4)) = ""
Next i
sFile.WriteLine ("")
sFile.WriteLine ("交换机上的")
sFile.WriteLine ("")
For i = 1 To 500
If Sheet2.Range("B" & i + 4) <> "" Then
sFile.WriteLine (Sheet2.Range("B" & (i + 4)))
Sheet2.Range("B" & (i + 4)) = ""
End If
Next i
Set fso = Nothing
Set sFile = Nothing

byebye:

End Sub