Dim xlApp, xlbook, xlsheet As Object ' 声明对象变量
'建立excel表格
Private Sub Command1_Click()
'定义对象
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Add
Set xlSheet1 = xlbook.Worksheets("sheet1")
N_I = Text1.TextxlSheet1.Name = "测试数据1" '工作簿的名称
xlApp.Visible = False 'Excel文件是不是显示
With xlSheet1
.Cells(1, 1).Value = "测试日期"
.Cells(1, 2).Value = "测试时间"
.Cells(1, 3).Value = "电阻值" '填入数据
End With
xlApp.Visible = False '这样写,Excel的操作就不显示了。
xlbook.SaveAs (App.Path & "\" & "测试记录\" & N_I & ".xls")
xlbook.Close '必须文件关闭,不然Excel.exe的进程会一直驻留内存。
Set xlsheet = Nothing
Set xlbook = Nothing '清空
Set xlApp = Nothing
End Sub
'往建立的excel表格中添加数据
Private Sub Command2_Click()Dim i As Integer
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim j As LongN_I = Text1.Text
Set xlApp = CreateObject("Excel.Application") ' 建立对象为Excel.Sheet
Set xlbook = xlApp.Workbooks().Open(App.Path & "\测试记录\" & N_I & ".xls")
Set xlSheet1 = xlbook.Worksheets("测试数据1")xlApp.Visible = False 'Excel文件是不是显示
With xlSheet1
j = .UsedRange.Rows.Count + 1
.Cells(j, 1) = FormatDateTime(Now, vbLongDate)
.Cells(j, 2) = FormatDateTime(Now, vbLongTime)
.Cells(j, 3).Value = Val(Text4.Text) '填入数据
End With
xlApp.Visible = False '这样写,Excel的操作就不显示了。
xlbook.Save
xlbook.Close '必须文件关闭,不然Excel.exe的进程会一直驻留内存。
Set xlsheet = Nothing
Set xlbook = Nothing '清空
Set xlApp = Nothing
End Sub
Private Sub Command3_Click()End SubCommand2过程中的数据测到设定个数,假如100个,触发Command3,在Command1过程建立的book中追加一个新的Sheet,新的sheet表头格式与前一个格式一致,请问怎么实现啊?
有高人告诉使用Set xlsheet = xlApp.Worksheet.Add,但不知道加到哪里?请高手不吝指教!!
'建立excel表格
Private Sub Command1_Click()
'定义对象
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Add
Set xlSheet1 = xlbook.Worksheets("sheet1")
N_I = Text1.TextxlSheet1.Name = "测试数据1" '工作簿的名称
xlApp.Visible = False 'Excel文件是不是显示
With xlSheet1
.Cells(1, 1).Value = "测试日期"
.Cells(1, 2).Value = "测试时间"
.Cells(1, 3).Value = "电阻值" '填入数据
End With
xlApp.Visible = False '这样写,Excel的操作就不显示了。
xlbook.SaveAs (App.Path & "\" & "测试记录\" & N_I & ".xls")
xlbook.Close '必须文件关闭,不然Excel.exe的进程会一直驻留内存。
Set xlsheet = Nothing
Set xlbook = Nothing '清空
Set xlApp = Nothing
End Sub
'往建立的excel表格中添加数据
Private Sub Command2_Click()Dim i As Integer
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Dim j As LongN_I = Text1.Text
Set xlApp = CreateObject("Excel.Application") ' 建立对象为Excel.Sheet
Set xlbook = xlApp.Workbooks().Open(App.Path & "\测试记录\" & N_I & ".xls")
Set xlSheet1 = xlbook.Worksheets("测试数据1")xlApp.Visible = False 'Excel文件是不是显示
With xlSheet1
j = .UsedRange.Rows.Count + 1
.Cells(j, 1) = FormatDateTime(Now, vbLongDate)
.Cells(j, 2) = FormatDateTime(Now, vbLongTime)
.Cells(j, 3).Value = Val(Text4.Text) '填入数据
End With
xlApp.Visible = False '这样写,Excel的操作就不显示了。
xlbook.Save
xlbook.Close '必须文件关闭,不然Excel.exe的进程会一直驻留内存。
Set xlsheet = Nothing
Set xlbook = Nothing '清空
Set xlApp = Nothing
End Sub
Private Sub Command3_Click()End SubCommand2过程中的数据测到设定个数,假如100个,触发Command3,在Command1过程建立的book中追加一个新的Sheet,新的sheet表头格式与前一个格式一致,请问怎么实现啊?
有高人告诉使用Set xlsheet = xlApp.Worksheet.Add,但不知道加到哪里?请高手不吝指教!!
就不需要Set xlsheet = xlApp.Worksheet.Add
直接把sheet2更名就可以了如果很多sheet添加,那样就应该有一个sheet计数,而不是触发,否则效率很慢
2 新表的位置当前活动表之前的
要想插入到最后
Set xlsheet = xlbook.Worksheets.Add
xlsheet.Move after:=xlbook.Worksheets(xlbook.Worksheets.Count)
Private Sub Command3_Click()
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application") ' 建立对象为Excel.Sheet
Set xlbook = xlApp.Workbooks().Open(App.Path & "\测试记录\" & N_I & ".xls")
Set xlSheet1 = xlbook.Worksheets("测试数据1")
xlApp.Visible = False 'Excel文件是不是显示
'''''''''''''''''''''''''''BY LDY ''''''''''''''''''''''''''''''''''''''''''''''''''
Dim newsht As Excel.Worksheet
xlSheet1.Copy , xlSheet1 '新表在 测试数据1之后 去掉逗号在测试数据1之前
Set newsht = xlbook.Sheets(xlbook.Sheets.Count) ' 取得复制的新表
newsht.Name = "测试数据1" '这一句可以不要,Excel自动给个名称 测试数据1 (1)
newshtt.Rows("4:10000").Delete ' 假定表头在 1 -3 行,从4开始删除数据,只留表头
'''''''''''''''''''''''''''BY LDY ''''''''''''''''''''''''''''''''''''''''''''''''''
xlApp.Visible = False '这样写,Excel的操作就不显示了。
xlbook.Save
xlbook.Close '必须文件关闭,不然Excel.exe的进程会一直驻留内存。
Set xlsheet = Nothing
Set xlbook = Nothing '清空
Set xlApp = Nothing
End Sub
newsht.Name = "测试数据2" '这一句可以不要,Excel自动给个名称 测试数据1 (1)
'定义对象
Dim xlApp As New Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet1 As Excel.WorksheetPrivate Sub Command1_Click() '建立excel表格
Set xlbook = xlApp.Workbooks.Add
Set xlSheet1 = xlbook.Worksheets("sheet1")
N_I = Text1.Text
xlSheet1.Name = "测试数据1" '工作簿的名称
xlApp.Visible = False 'Excel文件是不是显示
With xlSheet1
.Cells(1, 1).Value = "测试日期"
.Cells(1, 2).Value = "测试时间"
.Cells(1, 3).Value = "电阻值" '填入数据
End With
xlApp.Visible = False '这样写,Excel的操作就不显示了。
xlbook.SaveAs (App.Path & "\" & "测试记录\" & N_I & ".xls")
xlbook.Close '必须文件关闭,不然Excel.exe的进程会一直驻留内存。
xlApp.Quit
Set xlsheet = Nothing
Set xlbook = Nothing '清空
Set xlApp = Nothing
End Sub
'往建立的excel表格中添加数据
Private Sub Command2_Click()
Dim i As Integer, j As Long
Dim L As Long
L = 5
N_I = Text1.Text
xlApp.Visible = False 'Excel文件是不是显示
Set xlbook = xlApp.Workbooks().Open(App.Path & "\测试记录\" & N_I & ".xls")
j = xlbook.Worksheets(1).UsedRange.Rows.Count: i = 1
If j >= L Then
For i = 2 To xlbook.Worksheets.Count
If InStr(xlbook.Worksheets(i).Name, "测试数据") Then
If xlbook.Worksheets(i).UsedRange.Rows.Count < L Then
xlbook.Worksheets(i).Select
Exit For
End If
Else
addsheet
End If
Next
End If
j = xlbook.Worksheets(i).UsedRange.Rows.Count + 1
xlbook.Worksheets(i).Cells(j, 1) = FormatDateTime(Now, vbLongDate)
xlbook.Worksheets(i).Cells(j, 2) = FormatDateTime(Now, vbLongTime)
xlbook.Worksheets(i).Cells(j, 3).Value = Val(Text4.Text) '填入数据
xlApp.Visible = False '这样写,Excel的操作就不显示了。
xlbook.Save
xlbook.Close '必须文件关闭,不然Excel.exe的进程会一直驻留内存。
xlApp.Quit
Set xlsheet = Nothing
Set xlbook = Nothing '清空
Set xlApp = NothingEnd SubPrivate Sub addsheet()
Dim i As Integer
For i = 1 To xlbook.Worksheets.Count
If InStr(xlbook.Worksheets(i).Name, "测试数据") = 0 Then
Exit For
End If
Next
If i = 1 Then
xlbook.Worksheets(1).Name = "测试数据1"
Else
xlbook.Worksheets.Add After:=xlbook.Worksheets(i - 1)
xlbook.Worksheets(i).Name = "测试数据" & i
xlbook.Worksheets(i).Select
End If
With xlbook.Worksheets(i)
.Cells(1, 1).Value = "测试日期"
.Cells(1, 2).Value = "测试时间"
.Cells(1, 3).Value = "电阻值"
End With
End Sub