Advertisement
Guest User

Untitled

a guest
Jan 16th, 2017
80
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.38 KB | None | 0 0
  1. Sub CallStoredProcedure()
  2. ' Create Connection
  3. Dim conn As ADODB.Connection
  4. Set conn = New ADODB.Connection
  5.  
  6. ' Create and set Connection settings
  7. Dim connectionString As String
  8. connectionString = "Provider=SQLNCLI11;" _
  9. & "Server=" & Worksheets("Input").Range("I5").Value & ";" _
  10. & "Database=SESG_General_DB;" _
  11. & "Uid=" & Worksheets("Input").Range("G5").Value & ";" _
  12. & "Pwd=" & Worksheets("Input").Range("H5").Value & ";" _
  13. & "DataTypeCompatibility=80;"
  14.  
  15. conn.Open (connectionString)
  16.  
  17. On Error Resume Next
  18. With Worksheets("DB_USD").ListObjects("DB_USD")
  19. .Range.AutoFilter
  20. .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
  21. .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
  22. End With
  23.  
  24. ' Create command
  25. Dim cmd As ADODB.Command
  26. Set cmd = New ADODB.Command
  27.  
  28. 'Set common parameters for each month
  29. Dim yearInput As Integer
  30. yearInput = Worksheets("Input").Range("D18").Value
  31. Dim yearParam As ADODB.Parameter
  32. Set yearParam = cmd.CreateParameter("@year", adInteger, adParamInput, , yearInput)
  33.  
  34. 'JANUARY
  35. 'Set parameters for command january
  36. Dim monthInput As Integer
  37. monthInput = 1
  38. Dim monthParam As ADODB.Parameter
  39. Set monthParam = cmd.CreateParameter("@month", adInteger, adParamInput, , monthInput)
  40.  
  41. Dim versionInput As String
  42. versionInput = Worksheets("Input").Range("D5").Value
  43. Dim versionParam As ADODB.Parameter
  44. Set versionParam = cmd.CreateParameter("@version", adVarChar, adParamInput, 12, versionInput)
  45.  
  46. With cmd
  47. .ActiveConnection = conn
  48. .CommandType = adCmdStoredProc
  49. .CommandText = "[dbo].[BEX_SPQ_DATA_USD]"
  50. .NamedParameters = True
  51. .Parameters.Append monthParam
  52. .Parameters.Append yearParam
  53. .Parameters.Append versionParam
  54. End With
  55.  
  56. ' Execute command for january
  57. Dim recordSet As ADODB.recordSet
  58. Set recordSet = cmd.Execute
  59.  
  60. 'Copy headers
  61. Dim fieldCount As Integer
  62. fieldCount = recordSet.Fields.Count
  63. For iCol = 1 To fieldCount
  64. Worksheets("DB_USD").Cells(1, iCol).Value = recordSet.Fields(iCol - 1).Name
  65. Next iCol
  66.  
  67. 'Copy values
  68. Worksheets("DB_USD").Range("A2").CopyFromRecordset recordSet
  69.  
  70. recordSet.Close
  71.  
  72. 'FEBRURAY
  73. 'Set parameters for command february
  74. monthInput = 2
  75. Set monthParam = cmd.CreateParameter("@month", adInteger, adParamInput, , monthInput)
  76.  
  77. versionInput = Worksheets("Input").Range("D6").Value
  78. Set versionParam = cmd.CreateParameter("@version", adVarChar, adParamInput, 12, versionInput)
  79.  
  80. Set yearParam = cmd.CreateParameter("@year", adInteger, adParamInput, , yearInput)
  81.  
  82. 'Replace month and version parameters with new values for february
  83. With cmd
  84. .Parameters.Append monthParam
  85. .Parameters.Append versionParam
  86. .Parameters.Append yearParam
  87.  
  88. End With
  89.  
  90. ' Execute command for february
  91. Set recordSet = cmd.Execute
  92.  
  93. 'Copy values below previous month
  94. Dim lastRow As Long
  95. lastRow = Worksheets("DB_USD").ListObjects("DB_USD").Range.Rows.Count + 1
  96.  
  97. fieldCount = recordSet.Fields.Count
  98. Worksheets("DB_USD").Range("A" & lastRow).CopyFromRecordset recordSet
  99.  
  100. recordSet.Close
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement