Import data from SQL

[online]

Hi Expert,

I have macro worksheet which import data from SQL based on active sheet Range("B3"). It's working fine. But i want to add two more condition start date Range("C3") and end date Range("D3").
My English is poor but i hope you guys will understand.

Sub GetSMSSalesdata()
Dim sdate, edate As Variant

Application.ScreenUpdating = False
connectDB

Sheets("SMSSalesdata").Select
ActiveSheet.Unprotect Password:="12345"
Range("B6:H10000").ClearContents
Range("B6:H10000").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
Selection.Font.Bold = False
End With
Sheets("SMSSalesdata").Select
Pname = ActiveSheet.Range("B3").Value
sdate = ActiveSheet.Range("C3").Value
edate = ActiveSheet.Range("D3").Value
Range("B6").Select

Set rs = New ADODB.Recordset

strsql = "exec GetReport '" & Pname & "' ," & "'dnvsls'"
rs.Open strsql, conn, adOpenKeyset, adLockBatchOptimistic

'If rs.RecordCount > 0 Then
ActiveCell.CopyFromRecordset rs
'End If


MsgBox ("Report Refreshed Successfully")

ActiveSheet.Protect Password:="12345"
End Sub

[Bob Phillips]

You will need two extra parameters to the GetReport stored procedure.

[online]

Hi xld,

I have added two parameter sdate and edate. but this is giving runt time error. plz see below mention code
[vba]Sub GetSMSSalesdata()
Dim sdate, edate As Variant

Application.ScreenUpdating = False
connectDB

Sheets("SMSSalesdata").Select
ActiveSheet.Unprotect Password:="12345"
Range("B6:H10000").ClearContents
Range("B6:H10000").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
Selection.Font.Bold = False
End With
Sheets("SMSSalesdata").Select
Pname = ActiveSheet.Range("B3").Value
sdate = ActiveSheet.Range("C3").Value
edate = ActiveSheet.Range("D3").Value
Range("B6").Select

Set rs = New ADODB.Recordset

strsql = "exec GetReport '" & Pname & "" & sdate & "" & edate & "' ," & "'dnvsls'"
rs.Open strsql, conn, adOpenKeyset, adLockBatchOptimistic

'If rs.RecordCount > 0 Then
ActiveCell.CopyFromRecordset rs
'End If


MsgBox ("Report Refreshed Successfully")

ActiveSheet.Protect Password:="12345"
End Sub
[/vba]

pname and strsql1 define public as string

[Bob Phillips]

Without seeing the sp, we are in the dark.

[online]

i did not understand sp?.

[pjotter]

The stored procedure...
We need to know what is in it, to help you.
Might need to change that to get it to work.

[online]

Hi Pjotter,

I have DB on SQL server and i need to extract data from SQL DB to excel. Given above first code is working fine but in which only one condition is define Pname as a string value.

[VBA]Pname = ActiveSheet.Range("B3").Value[/VBA]
Pname is string value. But i want to add two more criteria start date and end date so that data only extract between given date.

[pjotter]

Well, to add 2 more parameters, you cannot just add them when you call the stored procedure...

You have to change the stored procedure so it requires 2 more variables when calling it. I can help you with that, but for that I need to see what the stored procedure looks like.

If you do not know how to substract the code from the stored procedure out of the db, just search for it on the internets and you'll find out.