Private Sub Command1_Click() Dim filename As StringDim conn As ADODB.Connection Dim res As ADODB.Recordset Set conn = New ADODB.Connection Set res = New ADODB.Recordset With conn .CursorLocation = adUseClient .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=SERVER" .Open End With res.CursorLocation = adUseClient res.CursorType = adOpenDynamic res.LockType = adLockOptimistic res.ActiveConnection = conn res.Open "select * from authors ", conn, adOpenDynamic, adLockOptimistic Dim myexcel As New Excel.Application Dim mybook As New Excel.Workbook Dim mysheet As New Excel.Worksheet Set mybook = myexcel.Workbooks.Add '添加一个新的BOOK Set mysheet = mybook.Worksheets.Add '添加一个新的SHEET myexcel.Visible = True mysheet.Cells.CopyFromRecordset resres.Close Set res = Nothing End Sub
Dim filename As StringDim conn As ADODB.Connection
Dim res As ADODB.Recordset
Set conn = New ADODB.Connection
Set res = New ADODB.Recordset
With conn
.CursorLocation = adUseClient
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=SERVER"
.Open
End With
res.CursorLocation = adUseClient
res.CursorType = adOpenDynamic
res.LockType = adLockOptimistic
res.ActiveConnection = conn
res.Open "select * from authors ", conn, adOpenDynamic, adLockOptimistic
Dim myexcel As New Excel.Application
Dim mybook As New Excel.Workbook
Dim mysheet As New Excel.Worksheet
Set mybook = myexcel.Workbooks.Add '添加一个新的BOOK
Set mysheet = mybook.Worksheets.Add '添加一个新的SHEET
myexcel.Visible = True
mysheet.Cells.CopyFromRecordset resres.Close
Set res = Nothing
End Sub