Question

VB 如何把窗体上每个控件上的值导出到excel

当点击“导出”按钮时，把窗上个四个控件的值导出到excel里面去，这样一直把所有学生的成绩统计到这个表格里去。
以上代码如何写，帮忙给个例子？
caizifengliua 请教个问题:当我第二次输入时，是将成绩接着输入到原来的excel中，而不是替换，这个怎么写





Answer 1

'测试通过
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim l As Integer
'写文件
Private Sub Command1_Click()
l = l + 1
For i = 1 To 4 '读取10列
xlSheet.Cells(l, i) = Text1(i - 1).Text
Next i
End Sub
'初始化
Private Sub Form_Load()
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add '新建EXCEL工件簿文件
Set xlSheet = xlBook.Worksheets(1)
l = 1
For i = 1 To 4 '第一行
xlSheet.Cells(l, i) = Label1(i - 1).Caption
Next i
End Sub
'关闭
Private Sub Form_Unload(Cancel As Integer)
xlSheet.SaveAs "d:\test.xls" '按指定文件名存盘
xlApp.Quit '结束EXCEL对象
Set xlApp = Nothing '释放xlApp对象
End Sub

追问

不行呀，第一行就出错，提示“用户定义类型未定义”

追答

哎，最基本的操作Excel常识：引用Microsoft Excel 11.0 Object Library
菜单-工程-引用，找到后勾选

Answer 2

先引用microsoft excel

Dim VBExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlbooks As Excel.Workbooks
Dim xlSheet As Excel.Worksheet

Private Sub Command1_Click() '写入按钮

For i = 1 To xlSheet.Cells.Count '获取当前表行数
If xlSheet.Cells(i, 1).Value <> "" And xlSheet.Cells(i + 1, 1).Value = "" Then
a = i
Exit For
End If
Next

xlSheet.Cells(a + 1, 1).NumberFormatLocal = "@" '写入数据变为字符型
xlSheet.Cells(a + 1, 1).Value = Text1.Text '写入text1数据
xlSheet.Cells(a + 1, 2).Value = Text2.Text '以此类推
xlSheet.Cells(a + 1, 3).Value = Text3.Text '以此类推
xlSheet.Cells(a + 1, 4).Value = Text4.Text '写入text1数据

End Sub

Private Sub Command2_Click() '保存按钮
VBExcel.DisplayAlerts = False
xlBook.SaveAs App.Path & "\1.xls" '保存到程序目录的1.xls里

xlBook.Close '关闭工作表
VBExcel.Quit '退出Excel
MsgBox "写入成功"
End Sub

Private Sub Form_Load()
Set VBExcel = CreateObject("Excel.Application") '设置对象
VBExcel.Visible = False '设置可见性

If Dir(App.Path & "\1.xls") = "" Then
Set xlbooks = VBExcel.Workbooks
Set xlBook = xlbooks.Add("")
xlSheet.Cells(1, 1).Value = "姓名" '设置第一栏标题
xlSheet.Cells(1, 2).Value = "语文" '同上
xlSheet.Cells(1, 3).Value = "数学" '同上
xlSheet.Cells(1, 3).Value = "政治" '同上
Else
Set xlBook = VBExcel.Workbooks.Open(App.Path & "\1.xls")

End If
Set xlSheet = xlBook.Worksheets("Sheet1") '设置要操作的表
xlSheet.Activate '把表变为活动状态
End Sub

Answer 3

楼上的正解，楼主，还要努力啊！