摘要:本文巧妙利用微软VB命令通过宏指令完成繁复的交换机计费验证工作,对广大华为交换机维护人员有所启迪,值得推广。
Summary: The text artful using Microsoft VB’s command complete work that heavy and complicated exchange charge is validated through Macroinstruction,It has edify for spacious exchange vindicator,be good for spread.
关键词:华为交换机、宏指令、EXCEL文件、VB。
Keyword:Exchange of The HuaWei、Macroinstruction、Excel file、VB
维护华为交换机,在割接用户、核查字冠或处理话费争议时经常要碰到做计费验证工作,我们常用的方法是:对主叫用户登记“计费申告=是”,然后启动话单跟踪,或者浏览申告话单,这样收集话单存盘后通过打开该文件来查看,如果通话记录过多,每张话单都要翻屏浏览很是不方便。能否将文本文件格式的话单转换成EXCEL文件,并且过滤掉不需要的内容?通过Microsoft Excel 宏命令可以实现。
实现方法是新建一个空白EXCEL文件,利用微软VB命令录制、编辑一个新宏,话单跟踪宏命令具体如下:
Sub 宏1( )
Dim FileName As String
FileName = GetFileName
FileNum = FreeFile( ) '* GET IDLE FILE NUMBER
Open FileName For Input As #FileNum '* OPEN FILE
Dim RowCnt As Integer
Dim Row As Integer
Dim Column As Integer
Dim Cnt As Integer
F_ALL = vbNullString
RowCnt = 0
Worksheets("ALL").Select
Range("A1:IV4000").Select
Selection.ClearContents
Do While Not EOF(FileNum)
Line Input #FileNum, Line '* Read next line
If InStr(Line, "免费标志") > 0 Then
RowCnt = RowCnt + 1
Worksheets("ALL").Cells(RowCnt, 1) = CStr(Mid(Line, 21, 50))
ElseIf InStr(Line, "应答时间") > 0 Then
Worksheets("ALL").Cells(RowCnt, 2) = CStr(Mid(Line, 21, 50))
ElseIf InStr(Line, "话终时间") > 0 Then
Worksheets("ALL").Cells(RowCnt, 3) = CStr(Mid(Line, 21, 50))
ElseIf InStr(Line, "通话时长") > 0 Then
Worksheets("ALL").Cells(RowCnt, 4) = CStr(Mid(Line, 21, 50))
ElseIf InStr(Line, "主叫号码") > 0 Then
Worksheets("ALL").Cells(RowCnt, 5) = CStr(Mid(Line, 21, 50))
ElseIf InStr(Line, "被叫号码") > 0 Then
Worksheets("ALL").Cells(RowCnt, 6) = CStr(Mid(Line, 21, 50))
ElseIf InStr(Line, "计费情况") > 0 Then
Worksheets("ALL").Cells(RowCnt, 7) = CStr(Mid(Line, 21, 50))
ElseIf InStr(Line, "计费脉冲") > 0 Then
Worksheets("ALL").Cells(RowCnt, 8) = CStr(Mid(Line, 21, 50))
End If
Loop
Close #FileNum
End Sub
Function GetFileName() As String
Choosing File
Dim sw As String
Dim MSG, Style, title, Help, Ctxt, Response, MyString
sw = "loop"
Do While sw = "loop"
GetFileName = vbNullString
MSG = "######## SELECT THE TRAFFIC DATA ##########"
GetFileName = Application.GetOpenFilename("All files(*.*),*.*.", , MSG)
If GetFileName = "False" Then
MSG = "!!!!!!!!!!DATA IS NOT SELECTED. DO YOU WANT TRY AGAIN????????"
Style = vbYesNo + vbCritical + vbDefaultButton2 '* button
title = Trafficanalize '* title"
Response = MsgBox(MSG, Style, title)
If Response <> vbYes Then
End
End If
Else
sw = "exit"
End If
Loop
End Function