'//此VBA为Excel宏语言'

 

'Attribute VB_Name = "模块1"
Sub 制图表_NBR_G()
'Attribute 制图表_NBR_G.VB_ProcData.VB_Invoke_Func = " \n14"
'获取当前文件目录
Dim CurPath
CurPath = ActiveWorkbook.Path
' 制图表_NBR_G 宏

'忽略相关弹窗信息
Application.DisplayAlerts = False
' 获取今天的时间
Dim DateOfToday As String
DateOfToday = Format(Date, "yyyymmdd")
'DateOfToday = 20161105
'打开文本取数据
Const ForReading = 1, ForWriting = 2, ForAppending = 8
'格式:路由器IP 店铺编号 型号
Dim fso, file1, line, params, ip, number, mode
Set fso = CreateObject("Scripting.FileSystemObject")
Set file1 = fso.OpenTextFile(CurPath & "\NBR_G.txt", ForReading, False)
'循环写每一列数据
Do While file1.AtEndOfStream <> True
'读取一行数据
line = file1.ReadLine
'格式:路由器IP 店铺编号 型号
params = Split(line)
'获取IP地址
ip = params(0)
'店铺编号
number = params(1)
'获取设备型号
mode = params(2)

'判断同一型号设备添加数据结束,制图标
If number = "END" Then
'删除掉多余字符串
Cells.Replace What:="Number of active flows:", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

If mode = "1300G" Then
'调整数据格式
Range("B2:AI49").Select
Selection.NumberFormatLocal = "0"
'选择区域生成图表
Range("A1:AI49").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("data!A1:AI49")
End If

If mode = "1000G" Then
'调整数据格式
Range("B2:I49").Select
Selection.NumberFormatLocal = "0"
'选择区域生成图表
Range("A1:I49").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("data!A1:I49")
End If

If mode = "1500G" Then
'调整数据格式
Range("B2:B49").Select
Selection.NumberFormatLocal = "0"
'选择区域生成图表
Range("A1:B49").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("data!A1:B49")
End If

If mode = "2000G" Then
'调整数据格式
Range("B2:C49").Select
Selection.NumberFormatLocal = "0"
'选择区域生成图表
Range("A1:C49").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("data!A1:C49")
End If

ActiveChart.Axes(xlCategory).Select
'调整图表横坐标度量值
ActiveChart.Axes(xlCategory).MaximumScale = 1
ActiveChart.Axes(xlCategory).MajorUnit = 0.05
'调整图表纵坐标起始值
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 245
'修改图表title
ActiveChart.ChartTitle.Select
Selection.Format.TextFrame2.TextRange.Characters.Text = mode & "-" & DateOfToday & "-Report"
ActiveChart.ChartArea.Select
'移动到新的chart里
ActiveChart.Location Where:=xlLocationAsNewSheet
End If

If ip <> "IP" Then
'激活data sheet
Worksheets("data").Activate
'从文本读取数据写到B2

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & CurPath & "\temp\R_" & ip & "_" & DateOfToday & ".txt", Destination:= _
Range("B2"))
.Name = "R_" & ip & "_" & DateOfToday & ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'将店铺编号写到B1
Range("B1").Select
ActiveCell.FormulaR1C1 = number
End If

Loop
'将生成图标另存为本目录下的excel
ChDir CurPath
ActiveWorkbook.SaveAs Filename:=CurPath & "\NBR_G_Report_" & DateOfToday & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub