我想编写一个VB程序，每点击一次command1，自动将数据保存到excel表格中。程序如下：

Question

Private Sub Command1_Click()
Set sheet1 = x1book.Worksheets(1) '提示错误的语句
sheet1.Activate
sheet1.Cells(a, 1) = a
a = a + 1
End Sub

Private Sub Form_Load()
Dim a As Integer
a = 1
Dim x1app As Excel.Application
Dim x1book As Excel.Workbook
Dim sheet1 As Excel.Worksheet
Set x1app = CreateObject("excel.application")
x1app.Visible = True
Set x1book = x1app.Workbooks.Open("d:\temp\ceshi.xls") '打开excel工作薄
x1app.WindowState = xlMinimized
End Sub

程序可以自动打开一个excel表格，但是点击command1时，出现错误提示。
实时错误“91”
对象变量或with快变量未设置。
望高手指教！！
要求每点击一次command1，都自动保存数据。

Answer 1

'x1app、x1book、等等定义为全局变量，因为你不止在form_load中用到它们。废话不多说，看代码
Dim x1app As Excel.Application
Dim x1book As Excel.Workbook
Dim sheet1 As Excel.Worksheet
Dim a As Integer
Private Sub Command1_Click()
Set sheet1 = x1book.Worksheets(1) '提示错误的语句
sheet1.Activate
sheet1.Cells(a, 1) = a
a = a + 1
End Sub
Private Sub Form_Load()
a = 1
Set x1app = CreateObject("excel.application")
x1app.Visible = True
If Dir("d:\temp\ceshi.xls") = "" Then
Set x1book = x1app.Workbooks.Add
x1book.SaveAs "d:\temp\ceshi.xls"
Else
Set x1book = x1app.Workbooks.Open("d:\temp\ceshi.xls") '打开excel工作薄
End If
x1app.WindowState = xlMinimized
End Sub
Private Sub Form_Unload(Cancel As Integer)
x1book.Save
x1app.Quit
Set x1app = Nothing
End Sub

Answer 2

Set sheet1 = x1book.Worksheets(1)改成Set sheet1 = x1book.Worksheets(0)试试
……
Dim x1app As Excel.Application
Dim x1book As Excel.Workbook
Dim sheet1 As Excel.Worksheet
竟然还是局部变量……本回答被网友采纳

Answer 3

Sub 保存并退出Excel()
Application.SendKeys ("{ENTER}{ENTER}%fx")
ActiveWorkbook.Save
End Sub

Answer 4

excel本来就有这功能，还用VB做干嘛？要做还不如用OFFICE里自带的VBA来得快。