PDA

View Full Version : Import data from SQL



online
05-27-2011, 05:41 AM
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
05-27-2011, 06:30 AM
You will need two extra parameters to the GetReport stored procedure.

online
05-27-2011, 08:51 AM
Hi xld,

I have added two parameter sdate and edate. but this is giving runt time error. plz see below mention code
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


pname and strsql1 define public as string

Bob Phillips
05-27-2011, 11:05 AM
Without seeing the sp, we are in the dark.

online
05-27-2011, 08:56 PM
i did not understand sp?.

pjotter
05-30-2011, 06:57 AM
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
05-30-2011, 09:07 PM
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.

Pname = ActiveSheet.Range("B3").Value
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
05-30-2011, 10:46 PM
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.