http://support.microsoft.com/kb/306125
http://support.microsoft.com/kb/321686 http://www.ozgrid.com/forum/showthread.php?t=83016&page=1 On Thu, Jul 21, 2011 at 10:44 AM, Markkim <mark....@gmail.com> wrote: > Hi all > > I'm trying to learn how to connect to SQL Server and extract information > from Excel. > > The code below is one I got it from one of site and modified a bit (After > reading a book about ADO) > > When I run the code, I get the following error > > Complie error: > User-defined type not defined > > Then it highlights the following line > > Dim objConn As ADODB.Connection > > > I am confused.. I read a book again and again but I'm not sure what I am > missing.. Do I need to create a class or something? > > Can you help me out please? I'm trying to get the basic structure working > first then continue to study from here.. > > Thanks a lot in advance > > > > > Sub Stats2() > > ActiveSheet.Range("A1").Select > > Dim objConn As ADODB.Connection > Dim rsData As ADODB.Recordset > Dim strSQL As String > > szconnect = "Provider=SQLOLEDB;" & _ > "Data Source=sgjapsql02;" & _ > "Initial catalog = RECProcess;" & _ > "Intergrated Security=SSPI" > > > ''#Create the Connection and Recordset objects. > Set objConn = New ADODB.Connection > Set rsData = New ADODB.Recordset > > On Error GoTo errHandler > > ''#Open the Connection and execute the stored procedure > objConn.Open szconnect > strSQL = "select * from employee" > objConn.CommandTimeout = 0 > Set rsData = objConn.Execute(strSQL) > > For iCols = 0 To rsData.Fields.Count - 1 > ActiveSheet.Range("A3").Select > ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + iCols).Value = > rsData.Fields(iCols).Name > ActiveSheet.Cells.Font.Name <http://activesheet.cells.font.name/> = > "Arial" > ActiveSheet.Cells.Font.Size = 8 > ActiveSheet.Cells.EntireColumn.AutoFit > Next > > ActiveSheet.Range(ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column), > ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + > rsData.Fields.Count)).Font.Bold = True > j = 2 > > If Not rsData.EOF Then > ''#Dump the contents of the recordset onto the worksheet > On Error GoTo errHandler > ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column).CopyFromRecordset > rsData > If Not rsData.EOF Then > MsgBox "Data set too large for a worksheet!" > > End If > rsData.Close > End If > > Unload frmSQLQueryADO > Exit Sub > > errHandler: > MsgBox Err.Description, vbCritical, "Error No: " & Err.Number > ''#Unload frmSQLQueryADO > End Sub > > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com <http://akoul.blogspot.com/> http://akoul.posterous.com/ *akoul*.wordpress.com <http://akoul.wordpress.com/> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
