VBA在Excel中的应用(四)
Public Sub SavedQuery()
Dim Field As ADODB.Field
Dim Recordset As ADODB.Recordset
Dim Offset As Long
Const ConnectionString As String = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydb.mdb;Persist Security Info=False'
Set Recordset = New ADODB.Recordset
Call Recordset.Open('[Sales By Category]', ConnectionString, _
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
CommandTypeEnum.adCmdTable)
If Not Recordset.EOF Then
With Sheet1.Range('A1')
For Each Field In Recordset.Fields
.Offset(0, Offset).Value = Field.Name
Offset = Offset + 1
Next Field
.Resize(1, Recordset.Fields.Count).Font.Bold = True
End With
Call Sheet1.Range('A2').CopyFromRecordset(Recordset)
Sheet1.UsedRange.EntireColumn.AutoFit
Else
Debug.Print 'Error: No records returned.'
End If
Recordset.Close
Set Recordset = Nothing
End Sub