'文本文件的数据导入到excel中
'oracle db的数据导入到excel中
Sub clear1()
'
' Macro1 Macro
' 宏由 SkyUN.Org 录制，时间: 2011/4/4
'chlear
'
Range("C2:D8").Select
Selection.ClearContents
Selection.Interior.ColorIndex = 2
End Sub
Sub clear2()
'
' Macro1 Macro
' 宏由 SkyUN.Org 录制，时间: 2011/4/4
'chlear
'
Range("L1:Z30").Select
Selection.ClearContents
End Sub
Sub getdata()
'
' Macro2 Macro
' 宏由 SkyUN.Org 录制，时间: 2011/4/4
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\work\tsmon\20110402\20110402071114__IOmon_k_.txt", Destination:=Range _
("M2"))
.Name = "2011_sum_k_.txt_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 35
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=6
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub
Sub copy()
Dim t As Double
'temp_row,temp_col the row,col what to copy
Dim Temp_row As Integer
Dim Temp_col As Integer
'row col ,that what copy to
Dim row As Integer
Dim col As Integer
Dim Temp_col_fg As Integer
'get data to M2
'first row
Temp_row = 5
Temp_col = 13
row = 2
col = 3
Temp_col_fg = Temp_col + 6
Do While Temp_col <= Temp_col_fg
t = Cells(Temp_row, Temp_col)
Cells(row, col) = t
If (t > 10 And t <= 30) Then
Cells(row, col).Interior.ColorIndex = 6
End If
If (t > 30) Then
Cells(row, col).Interior.ColorIndex = 3
End If
Temp_col = Temp_col + 1
row = row + 1
Loop
'second row
Temp_row = 12
Temp_col = 13
row = 2
col = 4
'Temp_col_fg = Temp_col + 6
'the same with up
Do While Temp_col <= Temp_col_fg
t = Cells(Temp_row, Temp_col)
Cells(row, col) = t
If (t > 10 And t <= 30) Then
Cells(row, col).Interior.ColorIndex = 6
End If
If (t > 30) Then
Cells(row, col).Interior.ColorIndex = 3
End If
Temp_col = Temp_col + 1
row = row + 1
Loop
End Sub
Sub getdbfdb()
Dim row As Integer
Dim col As Integer
Dim fg As Integer
' Use OO4O
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDatabase = objSession.OpenDatabase("zmbc4", "mon/pwd", 0&)
'first select
Sql = "select max(t.latency_csa),max(t.latency_csb), max(t.latency_csc),max(t.latency_zwa),max(t.latency_zwb),max(t.latency_zwc), Max (t.latency_kf) from disk_latency t where t.time1 >= trunc(sysdate - 1) and t.time1 < trunc(sysdate)"
Set oraDynaSet = objDatabase.DBCreateDynaset(Sql, 0&)
row = 2
col = 3
If oraDynaSet.RecordCount = 0 Then
MsgBox ("no datafound!")
Exit Sub
End If
'because just one row,6 cols
If oraDynaSet.RecordCount = 1 Then
oraDynaSet.MoveFirst
For fg = 1 To oraDynaSet.fields.Count
t = oraDynaSet.fields(fg).Value
Cells(row, col) = t
If (t > 10 And t <= 30) Then
Cells(row, col).Interior.ColorIndex = 6
End If
If (t > 30) Then
Cells(row, col).Interior.ColorIndex = 3
End If
row = row + 1
Next
Else
MsgBox "没有检索到任何数据，请点击‘配置’按钮,检查时间范围、阈值大小..."
Exit Sub
End If
'Set objSession = Nothing
'Set objDatabase = Nothing
'second select
Sql2 = "select round(sum(t.latency_csa)/count(latency_csa), 1),round(sum(t.latency_csb)/count(latency_csb), 1),round(sum(t.latency_csc)/count(latency_csc), 1), round(sum(t.latency_zwa)/count(latency_zwa), 1),round(sum(t.latency_zwb)/count(latency_zwb), 1),round(sum(t.latency_zwc)/count(latency_zwc), 1),round(sum(t.latency_kf)/count(latency_kf), 1) from disk_latency t where t.time1 >= trunc(sysdate - 1) and t.time1 < trunc(sysdate) "
Set oraDynaSet2 = objDatabase.DBCreateDynaset(Sql2, 0&)
row = 2
col = 4
If oraDynaSet2.RecordCount = 0 Then
MsgBox ("no datafound!")
Exit Sub
End If
'because just one row,6 cols
If oraDynaSet2.RecordCount = 1 Then
oraDynaSet2.MoveFirst
For fg = 1 To oraDynaSet2.fields.Count
t = oraDynaSet2.fields(fg).Value
Cells(row, col) = t
If (t > 10 And t <= 30) Then
Cells(row, col).Interior.ColorIndex = 6
End If
If (t > 30) Then
Cells(row, col).Interior.ColorIndex = 3
End If
row = row + 1
Next
Else
MsgBox "没有检索到任何数据，请点击‘配置’按钮,检查时间范围、阈值大小..."
Exit Sub
End If
Set objSession = Nothing
Set objDatabase = Nothing
End Sub
Sub main()
clear1
getdata
copy
clear2
Range("A1").Select
End Sub
|
