O
oscar.c.marin
I need to copy the result of a query from Access to Excel in a particular wks.
I haven't been able to declare "db" as a database and I get a prompt saying
that user type not defined.
I copied the code from a previous thread from 2005 that was like this:
Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim TargetRange As Range
Dim db As dataBase
Dim rs As Recordset
Dim intColIndex As Integer
DBFullName = "C:\Documents and Settings\Junior\My Documents\Consumer "
Loans.mdb " "
TableName = "tbl_CLC"
Set TargetRange = Excel.Application.Worksheets("CLC").Range("A1")
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = _
rs.Fields(intColIndex).Name
Next
' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing
Any suggestions on how to proceed?
Thank you in advance.
I haven't been able to declare "db" as a database and I get a prompt saying
that user type not defined.
I copied the code from a previous thread from 2005 that was like this:
Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim TargetRange As Range
Dim db As dataBase
Dim rs As Recordset
Dim intColIndex As Integer
DBFullName = "C:\Documents and Settings\Junior\My Documents\Consumer "
Loans.mdb " "
TableName = "tbl_CLC"
Set TargetRange = Excel.Application.Worksheets("CLC").Range("A1")
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = _
rs.Fields(intColIndex).Name
Next
' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing
Any suggestions on how to proceed?
Thank you in advance.