How to Create an Excel-Based App to send parameters to Stored Procedure in MS SQL Server via VBA

Purpose: We are going to build a tool to send the parameters back to Stored Procedures in MS SQL server to run the calculation and then retrieve result from the server to the User Interface built via MS Excel with VBA.

User Interface: Excel
Backend DB: MS SQL Server

Steps:

1. First of all, let’s set up the columns and buttons in the Sheet called Warehouse View. There are three main buttons, Reset, Get Data, and Refresh Data.

Reset Button: Clicking this button will clear content of table (start from A3)
Get Data Button: Clicking this button will grab data from SQL Server and paste into the table.
Refresh Data: Clicking this button will refresh the stored procedure by pushing parameters (J2/K2) to the Server.

Note: Make sure you check developer option in Excel so that you are able to leverage functionalities of VBA.