January 12, 2012 at 5:15 pm
I'm trying to populate data in Excel using a macro to call a SQL Stored Procedure. I am using the following code in VBA but it gives me the error "Compile Error: User-defined type not defined" on the statement "Dim Conn as ADODB.Connection". Help indicates I need to check the "Data Access Object" in the References dialog box, but it is not there for me to check.
Any help appreciated. Thanks, Cindy
Sub GetDataFromSQL()
MsgBox ("Voyage Number " & Sheets(1).Cells(1, 4))
success = insertStoredProcedureData("usp_Tote_Report", Sheets(1).Cells(1, 4))
End Sub
Function insertStoredProcedureData(spName As String, strParameter As String)
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim i As Integer
Dim sConnect As String
'--DEFINE CONNECTION STRING HERE---------------------------------------------------------
sConnect = "driver={sql server}; server=CTS-15; Database=Carlile; UID=; PWD=;"
'----------------------------------------------------------------------------------------
'Establish connection
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
'Open recordset
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn
Cmd.CommandText = spName
Cmd.CommandType = adCmdStoredProc
Cmd.Parameters.Refresh
Cmd.Parameters(1).Value = strParameter
Set rs = Cmd.Execute()
'Loop through recordset and place values
rs.MoveFirst
Do While rs.EOF = False
For i = 0 To rs.Fields.Count - 1
Sheet1.Cells(5, 0).Value = rs.Fields(i).Value 'insert value into cell
Sheet1.Cells(5, 0).Offset(0, 1).Activate 'move to next cell for next value
Next i
Sheet1.Cells(5, 0).Offset(1, -i).Activate 'move to next row for next record
rs.MoveNext
Loop
'Clean up
rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing
End Function
January 12, 2012 at 5:42 pm
I figured it out. I had to add a Reference (under Tools) to "Microsoft ActiveX Data Objects". Now I'm troubleshooting the connection string. But I'm sure this will not beat me. I'm determined to win.
January 13, 2012 at 7:53 am
Good luck!
Glad you sorted it out.
Another way could have been declaring Conn without type declaration (it becomes a variant that way) and then assign the object:
Dim Conn
Set Conn = createObject("ADODB.Connection")
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply