form6.frm

VB平滑Excel表格数据

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   3945
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   5220
   LinkTopic       =   "Form1"
   ScaleHeight     =   3945
   ScaleWidth      =   5220
   StartUpPosition =   3  'Windows Default
   Begin VB.TextBox file_path_name3 
      Height          =   375
      Left            =   240
      TabIndex        =   12
      Top             =   1800
      Width           =   4695
   End
   Begin VB.TextBox file_path_name2 
      Height          =   375
      Left            =   240
      TabIndex        =   11
      Top             =   1200
      Width           =   4695
   End
   Begin VB.TextBox file_path_name 
      Height          =   375
      Left            =   240
      TabIndex        =   5
      Text            =   "E:\work\VIP\power_tool\data_050705\0705_static02\2005Jul05155410_10A_200ms_full_after 40A fast discharge.xls"
      Top             =   600
      Width           =   4695
   End
   Begin VB.CommandButton Smooth_data 
      Caption         =   "Smooth_data"
      Height          =   495
      Left            =   1200
      TabIndex        =   4
      Top             =   3360
      Width           =   1455
   End
   Begin VB.TextBox Text1 
      Height          =   375
      Left            =   480
      TabIndex        =   3
      Text            =   "2"
      Top             =   2760
      Width           =   495
   End
   Begin VB.TextBox Text2 
      Height          =   375
      Left            =   1320
      TabIndex        =   2
      Text            =   "2000"
      Top             =   2760
      Width           =   735
   End
   Begin VB.TextBox Text3 
      Height          =   375
      Left            =   2640
      TabIndex        =   1
      Text            =   "15"
      Top             =   2760
      Width           =   495
   End
   Begin VB.TextBox smooth_number 
      Height          =   375
      Left            =   3600
      TabIndex        =   0
      Text            =   "40"
      Top             =   2760
      Width           =   975
   End
   Begin VB.Label Label1 
      Caption         =   "start"
      Height          =   255
      Left            =   480
      TabIndex        =   10
      Top             =   2400
      Width           =   615
   End
   Begin VB.Label Label2 
      Caption         =   "end"
      Height          =   375
      Left            =   1320
      TabIndex        =   9
      Top             =   2400
      Width           =   735
   End
   Begin VB.Label Label3 
      Caption         =   "column"
      Height          =   375
      Left            =   2640
      TabIndex        =   8
      Top             =   2400
      Width           =   615
   End
   Begin VB.Label Label4 
      Caption         =   "file path and name"
      Height          =   255
      Left            =   360
      TabIndex        =   7
      Top             =   240
      Width           =   1695
   End
   Begin VB.Label Label5 
      Caption         =   "smooth number"
      Height          =   255
      Left            =   3600
      TabIndex        =   6
      Top             =   2400
      Width           =   1095
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False



Private Sub Smooth_data_Click()
           
Dim data_from_excel(1 To 7000) As Double

Dim temp_data
Dim smooth_number_value
Dim file_name As String

Dim result(1 To 7000, 1 To 15)  As Double

Dim wkbObj As Object ' 声明一个对象变量。
Dim oleExcel As Object
Dim oleWorkbook As Object
Dim objRng As Object
Dim objSheet As Object

Set oleExcel = CreateObject("Excel.Application")        'create file
Set oleWorkbook = oleExcel.Workbooks.Add()
Set objSheet = oleWorkbook.Worksheets(1)

'    Set objExcel = CreateObject("Excel.Application")
'    Set objBook = objExcel.Workbooks.Add()
'    Set objSheet = objBook.Worksheets(1)
'    Set objRng = objSheet.Range("A" & 1 & ":" & "J" & 20000)

oleExcel.Visible = False
smooth_number_value = CInt(smooth_number.Text)
Set objRng = objSheet.Range("A" & 2 & ":" & "O" & (CInt(Text2.Text) - smooth_number_value))

'For i = 1 To 5000
'    For j = 1 To 15
'        result(i, j) = 0
'    Next j
'Next i

Set wkbObj = GetObject(file_path_name)              'read file
oleWorkbook.SaveAs file_path_name.Text + "_smoothed.xls"

For h = 1 To CInt(Text3.Text)
    
    For i = CInt(Text1.Text) To CInt(Text2.Text)
        data_from_excel(i) = wkbObj.Worksheets(1).Range(Chr(64 + h) & i).Value
    Next i
    
    For i = CInt(Text1.Text) To (CInt(Text2.Text) - smooth_number_value)
    
        temp_data = 0
        For j = i To (i + smooth_number_value - 1)
            temp_data = temp_data + data_from_excel(j)
        Next j
        
        result(i - 1, h) = temp_data / smooth_number_value
'        oleExcel.Worksheets(1).Range(Chr(64 + h) & i).Value = temp_data / smooth_number_value

    Next i

Next h

objRng.Value = result

oleWorkbook.Save

If (file_path_name2.Text <> "") Then

    Set wkbObj = GetObject(file_path_name2)              'read file
    oleWorkbook.SaveAs file_path_name2.Text + "_smoothed.xls"
    
    For h = 1 To CInt(Text3.Text)
        
        For i = CInt(Text1.Text) To CInt(Text2.Text)
            data_from_excel(i) = wkbObj.Worksheets(1).Range(Chr(64 + h) & i).Value
        Next i
        
        For i = CInt(Text1.Text) To (CInt(Text2.Text) - smooth_number_value)
        
            temp_data = 0
            For j = i To (i + smooth_number_value - 1)
                temp_data = temp_data + data_from_excel(j)
            Next j
            
            result(i - 1, h) = temp_data / smooth_number_value
    '        oleExcel.Worksheets(1).Range(Chr(64 + h) & i).Value = temp_data / smooth_number_value
    
        Next i
    
    Next h
    
    objRng.Value = result
    
    oleWorkbook.Save
    
End If

If (file_path_name3.Text <> "") Then

    Set wkbObj = GetObject(file_path_name3)              'read file
    oleWorkbook.SaveAs file_path_name3.Text + "_smoothed.xls"
    
    For h = 1 To CInt(Text3.Text)
        
        For i = CInt(Text1.Text) To CInt(Text2.Text)
            data_from_excel(i) = wkbObj.Worksheets(1).Range(Chr(64 + h) & i).Value
        Next i
        
        For i = CInt(Text1.Text) To (CInt(Text2.Text) - smooth_number_value)
        
            temp_data = 0
            For j = i To (i + smooth_number_value - 1)
                temp_data = temp_data + data_from_excel(j)
            Next j
            
            result(i - 1, h) = temp_data / smooth_number_value
    '        oleExcel.Worksheets(1).Range(Chr(64 + h) & i).Value = temp_data / smooth_number_value
    
        Next i
    
    Next h
    
    objRng.Value = result
    
    oleWorkbook.Save
    
End If


Set objRng = Nothing
Set wkbObj = Nothing
oleWorkbook.Close
Set oleWorkbook = Nothing
Set oleExcel = Nothing

MsgBox "OK"

End Sub