Try this
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Markkim
Sent: Thursday, July 21, 2011 10:44 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Struggling with ADO...!! connecting to SQL Server
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 = "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
--
----------------------------------------------------------------------------------
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
