Hello Rafael,
check the below sub and call the sub @ 'Need help here to do something with
the data collected from Sql Server
'
*****************************************************************************************************
Private Sub WriteDate(rs As Recordset)
On Error GoTo ErrorMessage
Dim objWorkBook As Excel.Workbook ' Initialize the workbook object
Dim objSheet As Excel.Worksheet ' Initialize the Sheet object
Set objWorkBook = ActiveWorkbook '.Application .Add ' Assign the
workbook object to excel application object
Set objSheet = objWorkBook.Sheets.Add ' Assign the Sheet object to
workbook object with Sheet1
objSheet.Activate ' Activate the Sheet object
colCount = rs.Fields.Count ' Get the reocrds field count
rowix = 1 ' Set the row count to 1
While Not rs.EOF ' Open the While Loop until the Record Set object has
records
If rowix = 1 Then ' Write the Column Name to the activated Workbook
sheet
For i = 0 To colCount - 1
objSheet.Cells(rowix, i + 1) = rs.Fields(i).Name
Next
rowix = rowix + 1
End If
For i = 0 To colCount - 1 ' Write the data to the activated
Workbook sheet
objSheet.Cells(rowix, i + 1) = rs.Fields(i).Value
Next
rowix = rowix + 1 ' increament the row index by 1
rs.MoveNext ' move the record set object to next value
Wend
Set objWorkBook = Nothing ' make workbook object to nothing
Set objSheet = Nothing ' make sheet object to nothing
Exit Sub ' exit the method
ErrorMessage:
'Resume
MsgBox Err.Description
End Sub
'
*****************************************************************************************************
Let me know if it helps?
Thanks and Regards
Kishore
On Wed, Mar 26, 2014 at 3:11 AM, Rafael DeLeon <jimmy.sab...@gmail.com>wrote:
>
> Hello all,
>
> I have a SQL Server Database with a store procedure that I would like to
> display in excel. I'm able to connect to the database through excel using
> VBA, but don't know how to display the information in a spreadsheet. Any
> help or idea will be appreciated.
>
> Thank you,
> Rafael
>
>
> Bellow the code that already works:
> Sub GetData()
>
> Dim con As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim rs As ADODB.Recordset
> Dim par As String
>
> Set con = New ADODB.Connection
> Set cmd = New ADODB.Command
> Set rs = New ADODB.Recordset
>
> Application.DisplayStatusBar = True
> Application.StatusBar = "Contacting to SQL Server..."
>
> ' Log into SQL Server, and run the Stored Procedure
> con.Open "Provider=SQLOLEDB.1;Password=*******;Persist Security
> Info=True;User ID=password;Initial Catalog=DataTest;Data
> Source=192.168.2.196\Test"
>
> cmd.ActiveConnection = con
>
> Dim prmCustomerID As ADODB.Parameter
>
> ' Set up the parameters
>
> cmd.Parameters.Append cmd.CreateParameter("@From", adDate, adParamInput,
> 10, Range("B4").Text)
> cmd.Parameters.Append cmd.CreateParameter("@To", adDate, adParamInput, 10,
> Range("B5").Text)
>
> Application.StatusBar = "Pulling data from Store Procedure"
> cmd.CommandText = "spParticipationRpt"
> Set rs = cmd.Execute(, , adCmdStoredProc)
>
> 'Need help here to do something with the data collected from Sql Server
>
> rs.Close
> Set rs = Nothing
> Set cmd = Nothing
> con.Close
> Set con = Nothing
>
> Application.StatusBar = "Process completed successfully."
>
> End Sub
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
