PDA

View Full Version : ADODB.Connection Error: Compile Error



YellowLabPro
10-04-2008, 05:18 AM
Hello group,
It has been awhile since I was here, and for the matter done any Excel/VBA work. I am working back in it now that things have settled down.
I had implemented a little bit of code to grab some data from a closed workbook. It had worked prior, but then somewhere down the line broke.

The error message is:
Compile Error: User-defined type not defined



Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
' this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
' this will return data from any worksheet in SourceFile
' SourceRange must include the range headers

<<<<<<<<<<<<<Error is in red>>>>>>>>>>>>>>>>>>>>>>>>
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset

Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close ' close the database connection
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub



Thanks,

Doug

Bob Phillips
10-04-2008, 06:52 AM
That would suggest that you dojn't have a reference to ADO in the VBIDE Doug.

YellowLabPro
10-04-2008, 08:00 AM
Thanks Bob,
That pointed me in the right direction. I was able to locate and select teh correct reference and make the connection.

Bob Phillips
10-04-2008, 09:23 AM
It's god to see you back Doug, I hope things are good, or as good as they can be.

YellowLabPro
10-04-2008, 09:38 AM
Thanks Bob....