I'm trying to populate an Excel 2003 worksheet from a SQL 2005 table using ADO.
I've got the VBA code below in the Workbook.Open event of "ThisWorkbook".
On my computer (Windows XP, Excel 2007, my DomainAdmin login) it works great. The worksheet populates with the table data.
On my test computer (Window 7, Excel 2010, my test common user login) it works great.
On the users computer that needs it (Windows XP, Excel 2003, her login) it loads three rows of the data and she gets a Run-time error '-2147467259 (80004005)':
Method 'CopyFromRecordset' of object 'Range' failed
Is there something different about Excel 2003 or am I doing something else wrong?
I don't mind rewriting the whole thing if there's a better way to do it.
Private Sub Workbook_Open()
'This was set up using Microsoft ActiveX Data Objects 2.8 Library (In Microsoft Visual Basic, select Tools, References)
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; " & _
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
Set rnStart = .Range("B2")
stSQL = "SELECT * FROM Complaints ORDER BY Location, DateReceived"
Set cnt = New ADODB.Connection
.CursorLocation = adUseClient
.CommandTimeout = 0
Set rst = .Execute(stSQL)
'Here we add the Recordset to the sheet from A1
Set rst = Nothing
Set cnt = Nothing