P
Philosophaie
Trying to use ADO to move some data to Excel
Excel shows an error when:
Dim Connection As ADODB.Connection is executed.
This is the program:
Sub AccessToExcel()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Cells.Clear
' Database information
DBFullName = "E:\db.accdb"
' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
Filter
Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'"
Src = Src & "and Year = '2001'"
..Open Source:=Src, ActiveConnection:=Connection
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range(“A1â€).Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next
'Write the recordset
Range(“A1â€).Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
Excel shows an error when:
Dim Connection As ADODB.Connection is executed.
This is the program:
Sub AccessToExcel()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Cells.Clear
' Database information
DBFullName = "E:\db.accdb"
' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
Filter
Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'"
Src = Src & "and Year = '2001'"
..Open Source:=Src, ActiveConnection:=Connection
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range(“A1â€).Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next
'Write the recordset
Range(“A1â€).Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub