Read multiple inputs from an excel file and and display the outputs by web service call

[Prachijain]

Hello, I'm a newbie in Excel VBA. I have a task assigned where I need to read the multiple testcases(inputs) from a excel file,like reading of different test cases and paste the outputs(after formulas and all applied) in different sheet everything through a VBA. I don't have a JSON data or a URL to read the data from, it's all from the excel filename where I need to parse the data and get the outputs for different test cases and later compare the results with an api. Any help would be greatly appreciated.
I used the below code to to read the inputs from my sheet and calling the web service call to get the output. But the problem I am facing here is its static and not dynamic where I can take multiple set of inputs.Below is my version of code. Please have a look into it

[/COLOR][COLOR=#333333]Sub Macro2()Dim http As Object[/COLOR]
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "POST", "https://bitlb02.poolt.hewitt.com/dsi0042/calculator", False
http.setRequestHeader "Content-type", "application/x-www-form-urlencoded"


Dim input_bd As String
Dim input_hd As String
Dim input_Gender As String
Dim input_apc As String
Dim input_ec As String
Dim input_apg As String
Dim input_mp As String
Dim input_PVD As String
Dim input_NRA As String
Dim input_MBAA As String
Dim input_Custom_BCD As String
Dim icta As String
Dim input_re As String
Dim input_11 As String
Dim input_psy As String
Dim input_pba As String
Dim input_DL As String
Dim input_ProjBuyBackABO As String
Dim input_DateABO As String
Dim input_PABO As String
Dim input_PAAL As String
Dim input_dAAL As String
Dim input_tba As String
Dim input_CurrentABO As String


 input_bd = Worksheets("datasource").Range("B2")
 input_hd = Worksheets("datasource").Range("B3")
 input_Gender = Worksheets("datasource").Range("B4")
 input_apc = Worksheets("datasource").Range("B5")
 input_ec = Worksheets("datasource").Range("B6")
 input_apg = Worksheets("datasource").Range("B7")
 input_mp = Worksheets("datasource").Range("B8")
 input_PVD = Worksheets("datasource").Range("B10")
 input_NRA = Worksheets("datasource").Range("B11")
 input_MBAA = Worksheets("datasource").Range("B12")
 input_Custom_BCD = Worksheets("datasource").Range("B13")
 icta = Worksheets("datasource").Range("B14")
 input_re = Worksheets("datasource").Range("B16")
 input_11 = Worksheets("datasource").Range("B18")
 input_psy = Worksheets("datasource").Range("B19")
 input_pba = Worksheets("datasource").Range("B20")
 input_DL = Worksheets("datasource").Range("B21")
 input_ProjBuyBackABO = Worksheets("datasource").Range("B23")
 input_DateABO = Worksheets("datasource").Range("B24")
 input_PABO = Worksheets("datasource").Range("B25")
 input_PAAL = Worksheets("datasource").Range("B26")
 input_dAAL = Worksheets("datasource").Range("B27")
 input_tba = Worksheets("datasource").Range("B28")
 input_CurrentABO = Worksheets("datasource").Range("B29")


 http.send "Site=SBA_Modeler_V30&Data={'input_BirthDate':'" + input_bd _
+ "','input_HireDate':'" + input_hd _
+ "','input_Gender':'" + input_Gender _
+ "','input_AnnualPlanComp':" + input_apc _
+ ",'input_EmployeeClass':'" + input_ec _
+ "','input_AnnualPayGrowth':" + input_apg _
+ ",'input_MarketPerformance':'" + input_mp _
+ "','input_InvestmentBalanceTBA':" + input_tba _
+ ",'input_RemainingElections':" + input_re _
+ ",'input_PVD':" + input_PVD _
+ ",'input_ProjBuyBackABO':" + input_ProjBuyBackABO _
+ ",'input_NRA':" + input_NRA _
+ ",'input_MBAA':" + input_MBAA _
+ ",'input_Custom_BCD':" + input_Custom_BCD _
+ ",'input_Custom_TermAge':" + icta _
+ ",'input_Pre2011ServiceYears':" + input_11 _
+ ",'input_ProjectedBenefitAmount':" + input_pba _
+ ",'input_CurrentABO':" + input_CurrentABO _
+ ",'input_DateABO':'" + input_DateABO _
+ "','input_ProjectedABO':" + input_PABO _
+ ",'input_ProjectedAAL':" + input_PAAL _
+ ",'input_DateAAL':'" + input_dAAL _
+ "','input_ProjectedServiceYears':" + input_psy _
+ ",'input_DROP_LumpSum':" + input_DL _
+ "}"


' MsgBox (http.responsetext)
' Now get the api output and insert into the spreadsheet
Dim api As Object
Dim scriptControl As Object


Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"
Set api = scriptControl.Eval("(" + http.responsetext + ")")
Worksheets("datasource").Range("G2") = api.output_MaxAgeBCD


Worksheets("datasource").Range("G2") = api.output_Range_Age1
Worksheets("datasource").Range("G3") = api.output_Range_Age2
Worksheets("datasource").Range("G4") = api.output_Range_Age3
Worksheets("datasource").Range("G5") = api.output_Range_Age4
Worksheets("datasource").Range("G6") = api.output_Range_Age5


Worksheets("datasource").Range("G7") = api.output_Range_Custom


Worksheets("datasource").Range("G8") = api.output_Balance_Age1
Worksheets("datasource").Range("G9") = api.output_Balance_Age2
Worksheets("datasource").Range("G10") = api.output_Balance_Age3
Worksheets("datasource").Range("G11") = api.output_Balance_Age4
Worksheets("datasource").Range("G12") = api.output_Balance_Age5


Worksheets("datasource").Range("G13") = api.output_Balance_Custom
Worksheets("datasource").Range("G14") = api.output_Balance_LumpSum


Worksheets("datasource").Range("G16") = api.output_CurrentAge
Worksheets("datasource").Range("G17") = api.output_MinAgeTerm
Worksheets("datasource").Range("G18") = api.output_MaxAgeTerm
Worksheets("datasource").Range("G19") = api.output_MinAgeBCD
Worksheets("datasource").Range("G20") = api.output_MaxAgeBCD


Worksheets("datasource").Range("G22") = api.output_DROP_Question
Worksheets("datasource").Range("G23") = api.output_DROP_Years
Worksheets("datasource").Range("G24") = api.output_DROP_Start
Worksheets("datasource").Range("G25") = api.output_DROP_1stYear
Worksheets("datasource").Range("G26") = api.output_DROP_Accumulation
Worksheets("datasource").Range("G27") = api.output_DROP_AccumulationAnnuity
Worksheets("datasource").Range("G28") = api.output_DROP_TotalAnnuity
Worksheets("datasource").Range("G29") = api.output_DROP_COLA


Worksheets("datasource").Range("G31") = api.output_BuyBack_PP
Worksheets("datasource").Range("G32") = api.output_BuyBack_Payment
Worksheets("datasource").Range("G33") = api.output_BuyBack_IP_AddOn
Worksheets("datasource").Range("G34") = api.output_BuyBack_TotalAnnuity



 [COLOR=#333333]End Sub[/COLOR][COLOR=#333333]