Question

将sql中的数据通过vb导入excel 时要求对象出错

Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=飞机大修管理信息系统;data source=WEIWEI-PC\SQLEXPRESS"
sql = "select * from 工卡 where 工卡号='" & Trim(Text1.Text) & "'"
rst.Source = sql
Set rst.ActiveConnection = con
rst.LockType = adLockOptimistic
rst.CursorLocation = adUseClient
rst.Open sql, con
Set DataGrid1.DataSource = rst
End Sub

Private Sub Command2_Click()
If rst.RecordCount < 1 Then
MsgBox "没有数据导出", vbOKOnly + vbCritical, "错误提示"
Else
If Dir("d:\Excel", vbDirectory) = "" Then
MkDir ("d:\Excel")
End If
If Dir("d:\Excel\销售人员信息.xls") <> "" Then
Kill "d:\Excel\销售人员信息.xls"
End If
End If
Dim i As Integer
Dim j As Integer
Dim xlExcel As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlBook = xlExcel.Workbooks.Add
Set xlSheet = xlExcel.Worksheets.Add
xlSheet.Cells.Columns(12).ColumnWidth = 20
xlSheet.Cells(1, 1) = "工卡号"
xlSheet.Cells(1, 2) = "标题"
xlSheet.Cells(1, 3) = "任务号"
xlSheet.Cells(1, 4) = "适用机型"
xlSheet.Cells(1, 5) = "参考资料"
xlSheet.Cells(1, 6) = "周期"
xlSheet.Cells(1, 7) = "工种"
xlSheet.Cells(1, 8) = "检验级别"
xlSheet.Cells(1, 9) = "额定工时"
xlSheet.Cells(1, 10) = "工作区域"
xlSheet.Cells(1, 11) = "工具信息"
xlSheet.Cells(1, 12) = "材料信息"
xlSheet.Cells(1, 13) = "任务"
xlSheet.Cells(1, 14) = "目的"
xlSheet.Cells(1, 15) = "警告"
xlSheet.Cells(1, 16) = "编写人"
xlSheet.Cells(1, 17) = "审核人"
xlSheet.Cells(1, 18) = "批准人"
xlSheet.Cells(1, 19) = "编写日期"
xlSheet.Cells(1, 20) = "审核日期"
xlSheet.Cells(1, 21) = "批准日期"
For i = 2 To rst.RecordCount + 1
For j = 1 To rst.Fields.Count
xlSheet.Cells(i, j) = rst.Fields.Item(j - 1).value
Next j
rst.MoveNext
Next i
xlBook.SaveAs FileFormat:=xlExcel9795
xlBook.SaveAs FileName:="d:\Excel\销售人员信息.xls"
rst.Close
con.Close
Set xlSheet = Nothing
Set xlBook = Nothing
xlExcel.Quit
Set xlExcel = Nothing
MsgBox "工卡.xls保存路径为d:\Excel\工卡.xls", 0 + 48, "信息提示！"
Unload Me
End Sub
其中If rst.RecordCount < 1 Then要求对象 出错了
如何解决？

Answer 1

你的rst和con变量设置的都是私有变量，在用command2_click调用的时候，rst变量并没有进行赋值，所以相当于此刻rst变量是nothing,当然会出错

解决的方法之一是在将rst和con设置为全局变量，可以在两段程序之外这样定义
public con As New ADODB.Connection
public rst As New ADODB.Recordset

追问

出现“发现二定义的名称：Private Sub Command2_Click”

追答

那是因为你自己程序里面对command2设定了两个click事件，请仔细检查你的代码，看是不是有两段Private Sub Command2_Click的代码。

追问

嗯 是我弄错 但是出现了 对象关闭时不允许操作 还是同样的位置 毕设让人愁啊

追答

你要先执行command1_click事件打开数据库，然后才能执行command2_click。否则，你的con是处于关闭状态的，实际上你两段代码整个的作用是打开数据库，然后对数据库进行操作。我建议你在command2_click执行前先调用command1_click，这样就可以防止数据库没有打开。

追问

我是先执行command1_click进行查询，再将查询结果导入excel的