Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub CallStoredProcedure()
- ' Create Connection
- Dim conn As ADODB.Connection
- Set conn = New ADODB.Connection
- ' Create and set Connection settings
- Dim connectionString As String
- connectionString = "Provider=SQLNCLI11;" _
- & "Server=" & Worksheets("Input").Range("I5").Value & ";" _
- & "Database=SESG_General_DB;" _
- & "Uid=" & Worksheets("Input").Range("G5").Value & ";" _
- & "Pwd=" & Worksheets("Input").Range("H5").Value & ";" _
- & "DataTypeCompatibility=80;"
- conn.Open (connectionString)
- On Error Resume Next
- With Worksheets("DB_USD").ListObjects("DB_USD")
- .Range.AutoFilter
- .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
- .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
- End With
- ' Create command
- Dim cmd As ADODB.Command
- Set cmd = New ADODB.Command
- 'Set common parameters for each month
- Dim yearInput As Integer
- yearInput = Worksheets("Input").Range("D18").Value
- Dim yearParam As ADODB.Parameter
- Set yearParam = cmd.CreateParameter("@year", adInteger, adParamInput, , yearInput)
- 'JANUARY
- 'Set parameters for command january
- Dim monthInput As Integer
- monthInput = 1
- Dim monthParam As ADODB.Parameter
- Set monthParam = cmd.CreateParameter("@month", adInteger, adParamInput, , monthInput)
- Dim versionInput As String
- versionInput = Worksheets("Input").Range("D5").Value
- Dim versionParam As ADODB.Parameter
- Set versionParam = cmd.CreateParameter("@version", adVarChar, adParamInput, 12, versionInput)
- With cmd
- .ActiveConnection = conn
- .CommandType = adCmdStoredProc
- .CommandText = "[dbo].[BEX_SPQ_DATA_USD]"
- .NamedParameters = True
- .Parameters.Append monthParam
- .Parameters.Append yearParam
- .Parameters.Append versionParam
- End With
- ' Execute command for january
- Dim recordSet As ADODB.recordSet
- Set recordSet = cmd.Execute
- 'Copy headers
- Dim fieldCount As Integer
- fieldCount = recordSet.Fields.Count
- For iCol = 1 To fieldCount
- Worksheets("DB_USD").Cells(1, iCol).Value = recordSet.Fields(iCol - 1).Name
- Next iCol
- 'Copy values
- Worksheets("DB_USD").Range("A2").CopyFromRecordset recordSet
- recordSet.Close
- 'FEBRURAY
- 'Set parameters for command february
- monthInput = 2
- Set monthParam = cmd.CreateParameter("@month", adInteger, adParamInput, , monthInput)
- versionInput = Worksheets("Input").Range("D6").Value
- Set versionParam = cmd.CreateParameter("@version", adVarChar, adParamInput, 12, versionInput)
- Set yearParam = cmd.CreateParameter("@year", adInteger, adParamInput, , yearInput)
- 'Replace month and version parameters with new values for february
- With cmd
- .Parameters.Append monthParam
- .Parameters.Append versionParam
- .Parameters.Append yearParam
- End With
- ' Execute command for february
- Set recordSet = cmd.Execute
- 'Copy values below previous month
- Dim lastRow As Long
- lastRow = Worksheets("DB_USD").ListObjects("DB_USD").Range.Rows.Count + 1
- fieldCount = recordSet.Fields.Count
- Worksheets("DB_USD").Range("A" & lastRow).CopyFromRecordset recordSet
- recordSet.Close
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement