David Megnin
asked on
Excel ADO error: User-defined type not defined
I'm trying to just pull a single SQL 2005 table, one way, into Excel 2003.
Compile error:
User-defined type not defined
I have references set to both Microsoft ADO Ext. 2.8 for DDL and Security and Microsoft ActiveX Data Objects Recordset 2.8 Library
What am I doint wrong?
The below VBA is in the Workbook Open event in "ThisWorkbook". It's a newly created Excel worksheet with nothing but this code in it and the references I mentioned added from "Tools|References".
Compile error:
User-defined type not defined
I have references set to both Microsoft ADO Ext. 2.8 for DDL and Security and Microsoft ActiveX Data Objects Recordset 2.8 Library
What am I doint wrong?
The below VBA is in the Workbook Open event in "ThisWorkbook". It's a newly created Excel worksheet with nothing but this code in it and the references I mentioned added from "Tools|References".
Private Sub Workbook_Open()
'This was set up using Microsoft ActiveX Data Objects version 2.8 (In Microsoft Visual Basic, select Tools, References)
Worksheets("Sheet1").Range("B2:BZ65535").Clear
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI; " & _
"Persist Security Info=False; " & _
"Initial Catalog=CustomerComplaints; " & _
"Data Source=BETASERVE"
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
With wsSheet
Set rnStart = .Range("B2")
End With
stSQL = "SELECT * FROM Complaints ORDER BY Location, DateReceived"
Set cnt = New ADODB.Connection
With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
'Here we add the Recordset to the sheet from A1
rnStart.CopyFromRecordset rst
'Cleaning up.
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you that fixed it.
ASKER