菜鸟提问：vb中access库导出成excel的问题~ ，请高手指点
小菜提问：
在vb中做了个“导出按钮”，把access库中的数据导出到excel中……
导出成功后打开C:\Excel\gift.xls文件，就闪了下无法打开excel文件
到任务管理器中，才看到有excel.exe进程（此时无excel文件运行），
关闭excel.exe进程后，才能正常打开C:\Excel\gift.xls文件……
请问这是什么问题？？该怎么解决呢？？
“导出“代码如下
Private Sub CmdOutput_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
cn.Open "provider=microsoft.jet.oledb.4.0;data source=d:\gift\gift.mdb "
sql = "select * from [ly_gift] "
rs.Source = sql
Set rs.ActiveConnection = cn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open sql, cn
If rs.RecordCount < 1 Then
MsgBox "没有数据导出 ", vbOKOnly + vbCritical, "错误提示 "
Else
If Dir( "C:\Excel ", vbDirectory) = " " Then
MkDir ( "C:\Excel ")
End If
If Dir( "C:\Excel ") <> " " Then
Kill "C:\Excel\gift.xls "
End If
End If
Dim i As Integer
Dim j As Integer
Dim xlExcel As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Set xlBook = xlExcel.Workbooks.Add
Set xlSheet = xlExcel.Worksheets.Add
xlSheet.Cells.Columns(5).ColumnWidth = 20
xlSheet.Cells(1, 1) = "联名卡号 "
xlSheet.Cells(1, 2) = "领用 "
xlSheet.Cells(1, 3) = "日期 "
xlSheet.Cells(1, 4) = "时间 "
xlSheet.Cells(1, 5) = "操作员 "
For i = 2 To rs.RecordCount + 1
For j = 1 To rs.Fields.Count
xlSheet.Cells(i, j) = rs.Fields.Item(j - 1).Value
Next j
rs.MoveNext
Next i
xlBook.SaveAs FileFormat:=xlExcel9795
xlBook.SaveAs FileName:= "C:\Excel\gift.xls "
rs.Close
cn.Close
End Sub
[解决办法]
工作表存盘后加入以下二行就行了
xlBook.Close ' 关闭工作表。
xlExcel.Quit '用 Quit 方法关闭 Microsoft Excel '释放对象
[解决办法]
Private Sub CmdOutput_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
cn.Open "provider=microsoft.jet.oledb.4.0;data source=d:\gift\gift.mdb "
sql = "select * from [ly_gift] "
rs.Source = sql
Set rs.ActiveConnection = cn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open sql, cn
If rs.RecordCount < 1 Then
MsgBox "没有数据导出 ", vbOKOnly + vbCritical, "错误提示 "
Else
If Dir( "C:\Excel ", vbDirectory) = " " Then
MkDir ( "C:\Excel ")
End If
If Dir( "C:\Excel\gift.xls ") <> " " Then
Kill "C:\Excel\gift.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(5).ColumnWidth = 20
xlSheet.Cells(1, 1) = "联名卡号 "
xlSheet.Cells(1, 2) = "领用 "
xlSheet.Cells(1, 3) = "日期 "
xlSheet.Cells(1, 4) = "时间 "
xlSheet.Cells(1, 5) = "操作员 "
For i = 2 To rs.RecordCount + 1
For j = 1 To rs.Fields.Count
xlSheet.Cells(i, j) = rs.Fields.Item(j - 1).Value
Next j
rs.MoveNext
Next i
xlBook.SaveAs FileFormat:=xlExcel9795
xlBook.SaveAs FileName:= "C:\Excel\gift.xls "
rs.Close
cn.Close
Set xlSheet = nothing
set xlBook=nothing
xlExcel.quit
set xlExcel=nothing
End Sub
[解决办法]
晕，忘了关闭workbook了
Set xlSheet = nothing
xlBook.close
set xlBook=nothing
xlExcel.quit
set xlExcel=nothing
[解决办法]
还有
Dim xlExcel As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Workbook和Worksheet不要用New，而直接用对象集的add方法
