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

hi
connection may be a reserved word like string.
set string as string????
try this
Dim Con as ADODB Connection 'no dot
also
dim rs as ADODB Recordset 'again no dot
also
Dim col as long

edit the rest of your code to reflect above.
everything else "looked" ok BUT i didn't not test.

Regards
FSt1

regards
FSt1


"Philosophaie" wrote:

> 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

Have you set a reference to the Microsoft ActiveX Data Objects Library?

--

HTH

Bob

"Philosophaie" <(E-Mail Removed)> wrote in message
news:9B33D52F-C520-4976-B1BE-(E-Mail Removed)...
> 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

Dim Con as ADODB Connection
with a space or without a space gives me an error and:

Dim Con as ADODB.Connection
is not working also.

I put in Tools>References

Added:
Microsoft Access 12.0 Object Library
Microsoft ActiveX Data Objects(Multi-dimensional) 6.0 Library

there are others but they conflict with each other. With these additions it
still does not work.


"Bob Phillips" wrote:

> Have you set a reference to the Microsoft ActiveX Data Objects Library?

Not the Multi-dimensional library, that is for OLAP cubes. You want
Microsoft ActiveX Data Objects 2.n Library as I stated.

--

HTH

Bob

"Philosophaie" <(E-Mail Removed)> wrote in message
news:7FB2ED89-85C9-45EF-A7F8-(E-Mail Removed)...
>I put in Tools>References
>
> Added:
> Microsoft Access 12.0 Object Library
> Microsoft ActiveX Data Objects(Multi-dimensional) 6.0 Library
>
> there are others but they conflict with each other. With these additions
> it
> still does not work.
>
>
> "Bob Phillips" wrote:
>
>> Have you set a reference to the Microsoft ActiveX Data Objects Library?
>