wademi
asked on
How do I populate an EXCEL spreadsheet with data from a SQL database using VBA.
Can someone send me an example of how to retrieve data from a SQL database and populate a Sheet in Excel.
I need an example of the database connection string and the code to populate the data from for example 5 fields from the database. I need the data to be copies starting from cell A1
I need an example of the database connection string and the code to populate the data from for example 5 fields from the database. I need the data to be copies starting from cell A1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
gbanik, if you're going to copy from another website I would reccommend giving the site credit.
http://www.ozgrid.com/forum/showthread.php?t=83016&page=1
http://www.ozgrid.com/forum/showthread.php?t=83016&page=1
I recommend you to first search your query in EE, rather than asking a question directly. You could get immediate resolution to most of your queries (rather than waiting for someone to answer you).
You would get tons of material in EE including example codes... and assured accepted solutions too :)
You would get tons of material in EE including example codes... and assured accepted solutions too :)
I didn't ask the question =)
ASKER
Why is it that when I compile my code I get a compilation error that says "User-defined type not defined"
at Dim cn As adodb.Connection.
I am using option explicit
at Dim cn As adodb.Connection.
I am using option explicit
Yes MWGainesJR.
First I thought of just giving a list of links as an answer. Then I thought.... maybe a piece of code would be the right thirst quencher for our query seeker.
First I thought of just giving a list of links as an answer. Then I thought.... maybe a piece of code would be the right thirst quencher for our query seeker.
Like I said in my post, you have to go to
Tools>References
and reference Microsoft Office ActiveX Data Objects 2.8
Tools>References
and reference Microsoft Office ActiveX Data Objects 2.8
Oh, of course, but mods don't think too kindly of pasting the code, without the link. =)
ASKER
Excellent answer. Thanks
........
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;Integ
"Persist Security Info=False;" & _
"Initial Catalog=BI;" & _
"Data Source=AURDWDEV01"
'where BI is SQL Database & AURDWDEV01 is SQL Server
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
With wsSheet
Set rnStart = .Range("A1")
End With
stSQL = "SELECT * FROM Settings"
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
.......