'工程->引用Microsoft ActiveX Data Objects 2.x Library '工程->引用Microsoft Excel x.0 Object Library '窗体上放一个CommonDialog、CommandButton、TextBox Private Sub Command3_Click() Dim pubConn As New ADODB.Connection Dim rsTable As New ADODB.Recordset Dim strConn As String Dim strSQL As String Dim AppExcel As Excel.Application Dim BookExcel As Excel.Workbook Dim ExcelFileName As String On Error GoTo mErr With CommonDialog1 CommonDialog1.CancelError = True .Filter = "Excel|*.xls" .DialogTitle = "建立输出文件" .ShowSave If Err = cdlCancel Then Exit Sub ExcelFileName = .FileName End With strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb" & ";Persist Security Info=False" pubConn.Open strConn rsTable.CursorLocation = adUseClient strSQL = "select * from Table1" rsTable.Open strSQL, pubConn, adOpenStatic, adLockOptimistic Set AppExcel = CreateObject("Excel.Application") If Dir$(ExcelFileName) = "" Then Set AppExcel = New Excel.Application AppExcel.Visible = False Set BookExcel = AppExcel.Workbooks.Add AppExcel.Worksheets(1).Name = Text1.Text '在Text1中输入你要更改的Excel表名 AppExcel.Worksheets(1).Range("A1").CopyFromRecordset rsTable BookExcel.SaveAs (ExcelFileName) Else Set BookExcel = AppExcel.Workbooks.Open(ExcelFileName) AppExcel.Worksheets(1).Name = Text1.Text '在Text1中输入你要更改的Excel表名 AppExcel.Worksheets(1).Range("A1").CopyFromRecordset rsTable BookExcel.Save End If AppExcel.Quit Set BookExcel = Nothing Set AppExcel = Nothing rsTable.Close Set rsTable = Nothing pubConn.Close Set pubConn = Nothing MsgBox "保存完成"Exit Sub mErr: MsgBox Err.Number & "," & Err.Description, vbCritical + vbOKOnly, mTitle End End Sub
'工程->引用Microsoft Excel x.0 Object Library
'窗体上放一个CommonDialog、CommandButton、TextBox
Private Sub Command3_Click()
Dim pubConn As New ADODB.Connection
Dim rsTable As New ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim AppExcel As Excel.Application
Dim BookExcel As Excel.Workbook
Dim ExcelFileName As String On Error GoTo mErr With CommonDialog1
CommonDialog1.CancelError = True
.Filter = "Excel|*.xls"
.DialogTitle = "建立输出文件"
.ShowSave
If Err = cdlCancel Then Exit Sub
ExcelFileName = .FileName
End With strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb" & ";Persist Security Info=False"
pubConn.Open strConn
rsTable.CursorLocation = adUseClient
strSQL = "select * from Table1"
rsTable.Open strSQL, pubConn, adOpenStatic, adLockOptimistic Set AppExcel = CreateObject("Excel.Application")
If Dir$(ExcelFileName) = "" Then
Set AppExcel = New Excel.Application
AppExcel.Visible = False
Set BookExcel = AppExcel.Workbooks.Add
AppExcel.Worksheets(1).Name = Text1.Text '在Text1中输入你要更改的Excel表名
AppExcel.Worksheets(1).Range("A1").CopyFromRecordset rsTable
BookExcel.SaveAs (ExcelFileName)
Else
Set BookExcel = AppExcel.Workbooks.Open(ExcelFileName)
AppExcel.Worksheets(1).Name = Text1.Text '在Text1中输入你要更改的Excel表名
AppExcel.Worksheets(1).Range("A1").CopyFromRecordset rsTable
BookExcel.Save
End If AppExcel.Quit
Set BookExcel = Nothing
Set AppExcel = Nothing
rsTable.Close
Set rsTable = Nothing
pubConn.Close
Set pubConn = Nothing MsgBox "保存完成"Exit Sub
mErr:
MsgBox Err.Number & "," & Err.Description, vbCritical + vbOKOnly, mTitle
End
End Sub