情况:当导入了一次EXCEL,然后关闭该EXCEL文档,整个程序未曾退出,再导入第二次EXCEL里头的内容无法显视,只有EXCEL的菜单、工具条,
On Error GoTo DealError
Dim i As Integer
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oExcel = Excel.Application
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Adodc3.RecordSource = "select * from Temp_OutPut_Excel"
Adodc3.Refresh
oSheet.Columns.ColumnWidth = 12
oSheet.Columns("c").NumberFormat = "yyyy-mm-dd"
oSheet.Columns("d").NumberFormat = "#,##0.00"
oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, DataGrid1.Columns.Count)).Borders.LineStyle = xlContinuous '设表格边框样式
oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, DataGrid1.Columns.Count)).Font.Bold = True '标题字体加粗
oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, DataGrid1.Columns.Count)).Font.Color = &H8000&
For i = 1 To 6
oSheet.Cells(1, i) = DataGrid1.Columns(i - 1).Caption
Next i
oSheet.Range("A2:a3").CopyFromRecordset Adodc3.Recordset
oExcel.Visible = True
Adodc3.Recordset.Close
'oBook.Save
oBook.Close
oExcel.Quit
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing '"交还控制给Excel
Exit Sub
DealError:
Err.Clear
On Error GoTo DealError
Dim i As Integer
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oExcel = Excel.Application
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Adodc3.RecordSource = "select * from Temp_OutPut_Excel"
Adodc3.Refresh
oSheet.Columns.ColumnWidth = 12
oSheet.Columns("c").NumberFormat = "yyyy-mm-dd"
oSheet.Columns("d").NumberFormat = "#,##0.00"
oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, DataGrid1.Columns.Count)).Borders.LineStyle = xlContinuous '设表格边框样式
oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, DataGrid1.Columns.Count)).Font.Bold = True '标题字体加粗
oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, DataGrid1.Columns.Count)).Font.Color = &H8000&
For i = 1 To 6
oSheet.Cells(1, i) = DataGrid1.Columns(i - 1).Caption
Next i
oSheet.Range("A2:a3").CopyFromRecordset Adodc3.Recordset
oExcel.Visible = True
Adodc3.Recordset.Close
'oBook.Save
oBook.Close
oExcel.Quit
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing '"交还控制给Excel
Exit Sub
DealError:
Err.Clear
xlBook.Close (False)
Set xlBook = Nothing
xlexcel.Quit
Set xlexcel = Nothing
Set xlSheet = Nothing
xlBook.Close (False)
Set xlBook = Nothing
xlexcel.Quit
Set xlexcel = Nothing
Set xlSheet = Nothing
xlBook.Close (False)
Set xlBook = Nothing
xlexcel.Quit
Set xlexcel = Nothing
Set xlSheet = Nothing
xlBook.Close (False)
Set xlBook = Nothing
xlexcel.Quit
Set xlexcel = Nothing
其实你多退几次就可以了,嘿嘿。前面加个有错继续运行就行了。
xlbook.Save '"保存"
Set xlbook = Nothing
xlapp.Quit '退出excel
Set xlapp = Nothing 'excel进程终止呵呵,看来我没有抄错代码,我的执行是没有问题的。