Read inputs from an excel file and and display the outputs by applying formulas

[Prachijain]

Hello, I'm a newbie in Excel VBA. I have a task assigned where I need to read the 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 math the results with an api. Any help would be greatly appreciated. Thank You.

 

[hotabae]

Let's Start with this:

I need to read the 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.

This is rather vague; do you have an example of what the input data looks like?

Could you define (formulas and all)?

If possible, please provide a sample of what the Output should look like as well or try to describe it at least.

 

[Prachijain]

I'm sorry , I was figuring how to share the image. Here are my inputs and outputs look like

Excel 2010
	C	D	E	F	G
1	Input Fields	Values		Output Fields	Values
2	input_BirthDate	6/5/1954		output_Range_Age1	70
3	input_HireDate	8/1/2013		output_Range_Age2
4	input_Gender	F		output_Range_Age3
5	input_AnnualPlanComp	50224		output_Range_Age4
6	input_EmployeeClass	Regular School District		output_Range_Age5
7	input_AnnualPayGrowth	1		output_Range_Custom	64
8	input_MarketPerformance	Average		output_Balance_Age1	$ -
9				output_Balance_Age2	$ -
10	input_PVD	70		output_Balance_Age3	$ -
11	input_NRA	62		output_Balance_Age4	$ -
12	input_MBAA	65		output_Balance_Age5	$ -
13	input_Custom_BCD	64		output_Balance_Custom	$ -
14	input_Custom_TermAge	64		output_Balance_LumpSum	$ -
15
16	input_RemainingElections	1		output_CurrentAge	63
17				output_MinAgeTerm	64
18	input_Pre2011ServiceYears	4		output_MaxAgeTerm	80
19	input_ProjectedServiceYears	11		output_MinAgeBCD	70
20	input_ProjectedBenefitAmount	7028.58		output_MaxAgeBCD	65
21	input_DROP_LumpSum	0
22				output_DROP_Question	FALSE
23	input_ProjBuyBackABO			output_DROP_Years
24	input_DateABO	2/28/2018		output_DROP_Start
25	input_ProjectedABO	103461.51		output_DROP_1stYear
26	input_ProjectedAAL	48696		output_DROP_Accumulation
27	input_DateAAL	3/31/2018		output_DROP_AccumulationAnnuity
28	input_InvestmentBalanceTBA	0		output_DROP_TotalAnnuity
29	input_CurrentABO	80772.6		output_DROP_COLA	1.09%
30
31				output_BuyBack_PP	$ 48,696.00
32				output_BuyBack_Payment	$ 48,696.00
33				output_BuyBack_IP_AddOn	$ 2,626.60
34				output_BuyBack_TotalAnnuity	$ 2,626.60
Inputs Outputs

Cell Formulas
Range	Formula
G2	=Model!B33
G3	=Model!B34
G4	=Model!B35
G5	=Model!B36
G6	=Model!B37
G7	=Model!B38
G8	=Model!B45
G9	=Model!B46
G10	=Model!B47
G11	=Model!B48
G12	=Model!B49
G13	=Model!B50
G14	=Model!B44
G16	=Model!B20
G17	=output_CurrentAge+1
G19	=IF(input_Custom_TermAge>input_MBAA,input_Custom_TermAge,MAX(output_MinAgeTerm,input_PVD))
G20	=MAX(input_Custom_TermAge,input_MBAA)
G22	=Model!G125
G23	=Model!G131
G24	=Model!G133
G25	=IF(output_DROP_Question,input_ProjectedBenefitAmount,"")
G26	=IF(output_DROP_Question,Model!F87,"")
G27	=IF(output_DROP_Question,Model!B86,"")
G28	=IF(output_DROP_Question,Model!B87,"")
G29	=ROUND(output_Global_DROP_COLA/100,4)
G31	=Model!E100
G32	=Model!E101
G33	=IF(Model!E103<0,0,Model!E103)
G34	=IF(output_BuyBack_IP_AddOn=0,Model!B50,Model!B51)

Named Ranges
Name	Refers To	Cells
input_Custom_TermAge	='Inputs Outputs'!$D$14
input_MBAA	='Inputs Outputs'!$D$12
input_ProjectedBenefitAmount	='Inputs Outputs'!$D$20
input_PVD	='Inputs Outputs'!$D$10
output_BuyBack_IP_AddOn	='Inputs Outputs'!$G$33
output_CurrentAge	='Inputs Outputs'!$G$16
output_DROP_Question	='Inputs Outputs'!$G$22
output_Global_DROP_COLA	='Inputs Outputs'!$J$5
output_MinAgeTerm	='Inputs Outputs'!$G$17
PVD	='Inputs Outputs'!$D$10

 

[hotabae]

Would something like this work? (Saves Everything from columns F and G to a CSV file - In the same folder as the original file)

To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window​

To Run This Code:
From the VBA Window, Click anywhere inside the code
Press F5 // OR // Navigate to "Run" in the toolbar and select "Run Sub/UserForm"

Sub ExportRangeCSV()

On Error Resume Next

'I ASSIGNED THE OUTPUT RANGES HERE 
'(IF YOU MOVE THEM YOU WILL NEED TO ADJUST THE SELECT CASES ACCORDINGLY- F=6 and G = 7)
Dim outputRng As Range
Set outputRng = Sheets("Inputs Outputs").Range("F2:G34")

Dim dataCell As Range
Dim tempString As String

inputName:

Dim filename As String
filename = InputBox("Please enter a filename")

Open ThisWorkbook.path & "\" & filename & ".csv" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 

If Err.Number = 52 Or Err.Number = 1004 Then

    GoTo inputName
    
End If
    
    For Each dataCell In outputRng
    
        Select Case dataCell.Column
        
            Case 6
            
                If InStr(1, dataCell.Value, ",") Then

                    tempString = Replace(dataCell.Value, ",", "")

                Else

                    tempString = dataCell.Value

                End If
                
            Case 7
    
                If InStr(1, dataCell.Value, ",") Then

                    tempString = tempString & "," & Replace(dataCell.Value, ",", "")

                Else

                    tempString = tempString & "," & dataCell.Value

                End If
    
                Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , tempString
                
        End Select
        
    Next
    
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub

 

[Prachijain]

Hey hotabae, well that's worked. But this is something completely different from what I need. Let me give you a scenario where I need to read number of test cases with number of inputs and display the outputs through a button macro in developer ribbon. Below is the example of my inputs

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	TestTest Cases	input_BirthDate	input_HireDate	input_Gender	input_AnnualPlanComp	input_EmployeeClass	input_AnnualPayGrowth	input_MarketPerformance		input_PVD	input_NRA	input_MBAA	input_Custom_BCD	input_Custom_TermAge		input_RemainingElections		input_Pre2011ServiceYears	input_ProjectedServiceYears	input_ProjectedBenefitAmount	input_DROP_LumpSum		input_ProjBuyBackABO	input_DateABO	input_ProjectedABO	input_ProjectedAAL	input_DateAAL	input_InvestmentBalanceTBA	input_CurrentABO
2
3	1	1/4/1983	10/1/2012	F	36179.3	Regular School District	1	Average		38	63	66	35	90		1		0	6.33	0	4461.18		0	1/31/2018	4461.18	0	1/1/1900	0	3340.03
4	2
ip

This is how my code looks like

Sub xp()

    Dim http As Object
    Dim st As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    st = "Site=SBA_Modeler_V22&Data={'input_BirthDate':'1983-01-04','input_HireDate':'2012-10-01','input_Gender':'F','input_AnnualPlanComp':36179.33,'input_EmployeeClass':'Regular School District','input_AnnualPayGrowth':1,'input_MarketPerformance':'Average','input_PVD':38,'input_NRA':63,'input_MBAA':66,'input_Custom_BCD':35,'input_Custom_TermAge':90,'input_RemainingElections':1,'input_Pre2011ServiceYears':0,'input_ProjectedServiceYears':6.33,'input_ProjectedBenefitAmount':0,'input_DROP_LumpSum':4461.18,'input_ProjBuyBackABO':0,'input_DateABO':2018-01-31,'input_ProjectedABO':4461.18,'input_ProjectedAAL':0,'input_DateAAL':1900-01-01,'input_InvestmentBalanceTBA':0,'input_CurrentABO':3340.03}"
   
 
    http.Open "POST", "https://beqlb02.poolt.hewitt.com/dsi0042/calculator", False
    http.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    http.send (st)


    MsgBox (http.responsetext)
    Set JSON = ParseJson("[" + http.responsetext + "]")
    i = 1
    For Each Item In JSON
    Sheets(4).Cells(3, 2).Value = JSON(1).Item("output_Range_Age1")
    Sheets(4).Cells(3, 3).Value = JSON(1).Item("output_Range_Age2")
    Sheets(4).Cells(3, 4).Value = JSON(1).Item("output_Range_Age3")
    Sheets(4).Cells(3, 5).Value = JSON(1).Item("output_Range_Age4")
    Sheets(4).Cells(3, 6).Value = JSON(1).Item("output_Range_Age5")
    Sheets(4).Cells(2, 7).Value = JSON(1).Item("output_Range_Custom")
    Sheets(4).Cells(3, 8).Value = JSON(1).Item("output_Balance_Age1")
    Sheets(4).Cells(3, 9).Value = JSON(1).Item("output_Balance_Age2")
    Sheets(4).Cells(3, 10).Value = JSON(1).Item("output_Balance_Age3")
    Sheets(4).Cells(3, 11).Value = JSON(1).Item("output_Balance_Age4")
    Sheets(4).Cells(3, 12).Value = JSON(1).Item("output_Balance_Age5")
    Sheets(4).Cells(3, 13).Value = JSON(1).Item("output_Balance_Custom")
    Sheets(4).Cells(3, 14).Value = JSON(1).Item("output_Balance_LumpSum")
    Sheets(4).Cells(2, 16).Value = JSON(1).Item("output_CurrentAge")
    Sheets(4).Cells(3, 17).Value = JSON(1).Item("output_MinAgeTerm")
    Sheets(4).Cells(3, 18).Value = JSON(1).Item("output_MaxAgeTerm")
    Sheets(4).Cells(3, 19).Value = JSON(1).Item("output_MinAgeBCD")
    Sheets(4).Cells(3, 20).Value = JSON(1).Item("output_MaxAgeBCD")
    Sheets(4).Cells(3, 22).Value = JSON(1).Item("output_DROP_Question")
    Sheets(4).Cells(3, 23).Value = JSON(1).Item("output_DROP_Years")
    Sheets(4).Cells(3, 24).Value = JSON(1).Item("output_DROP_Start")
    Sheets(4).Cells(3, 25).Value = JSON(1).Item("output_DROP_1stYear")
    Sheets(4).Cells(3, 26).Value = JSON(1).Item("output_DROP_Acumulation")
    Sheets(4).Cells(3, 27).Value = JSON(1).Item("output_DROP_AccumulationAnnuity")
    Sheets(4).Cells(3, 28).Value = JSON(1).Item("output_DROP_TotalAnnuity")
    Sheets(4).Cells(3, 29).Value = JSON(1).Item("output_DROP_COLA")
    Sheets(4).Cells(3, 31).Value = JSON(1).Item("output_BuyBack_Payment")
    Sheets(4).Cells(3, 32).Value = JSON(1).Item("output_BuyBack_IP_AddOn")
    Sheets(4).Cells(3, 33).Value = JSON(1).Item("output_DROP_BuyBack_TotalAnnuity")
    
    i = i + 1
    Next
    
    MsgBox ("complete")
 
End Sub

I have also included JSON converter. I need help with looping of inputs from each cell.

 

[hotabae]

I have never used JSON before, so I left that part alone for the most part. But here is how I would structure the code.

Beware... I just typed this up freehand in notepad, so there are probably errors.

Hope this helps!

First, a "Main" sub

Sub mainCode()

Dim i as Integer

Dim inputSheet As Worksheet
Set inputSheet = Sheets("ip")

Dim outputSheet As Worksheet
Set outputSheet = Sheets("op")

From the first input line(3) to the last row where info exists (hopefully also the last row of input)
For i = 3 to inputSheet.UsedRange.Rows.Count

    'Will skip any rows where "testCases" is blank
    If inputSheet.Cells(i,1).value <> "" Then

        'Calls grabInput Function to write "ST" for any given row
        st = grabInput(i)
        
        'Calls convertAndWrite based on current row
        convertAndWrite(st, i)
        
    End If

Next i

End Sub

Then a function to write the "ST" string

Public function grabInput(rowNum As Integer)

Dim j As Integer

Dim inputArray(1 To 30) As String
    
For j = 1 to Ubound(inputArray)

    If inputSheet.Cells(rowNum,j).value <> "" Then
        
        inputArray(j) = inputSheet.Cells(rowNum,j).value
    
    Else
    
        inputArray(j) = 0
        
    End If
    
Next j

grabInput = "Site=SBA_Modeler_V22&Data={'input_BirthDate':'" & inputArray(1) & _
"','input_HireDate':'" & inputArray(2) & "','input_Gender':'" & inputArray(3) & _
"','input_AnnualPlanComp':" & inputArray(4) & ",'input_EmployeeClass':'" & inputArray(5) & _
"','input_AnnualPayGrowth':" & inputArray(6) & ",'input_MarketPerformance':'" & inputArray(7) & _
"','input_PVD':" & inputArray(8) & ",'input_NRA':" & inputArray(9) & ",'input_MBAA':" & inputArray(10) & _
",'input_Custom_BCD':" & inputArray(11) & ",'input_Custom_TermAge':" & inputArray(12) & _
",'input_RemainingElections':" & inputArray(13) & ",'input_Pre2011ServiceYears':" & inputArray(14) & _
",'input_ProjectedServiceYears':" & inputArray(15) & ",'input_ProjectedBenefitAmount':" & inputArray(16) & _
",'input_DROP_LumpSum':" & inputArray(17) & ",'input_ProjBuyBackABO':" & inputArray(18) & _
",'input_DateABO':" & inputArray(19) & ",'input_ProjectedABO':" & inputArray(20) & _
",'input_ProjectedAAL':" & inputArray(21) & ",'input_DateAAL':" & inputArray(22) & _
",'input_InvestmentBalanceTBA':" & inputArray(23) & ",'input_CurrentABO':" & inputArray(23) & "}"

End Function

And last, the sub to process the input and then write output to the new sheet

Public Sub convertAndWrite(input As String, rowNum As Integer)


Dim http As Object
Dim st As String

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "POST", "https://beqlb02.poolt.hewitt.com/dsi0042/calculator",False
http.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
http.send (input)

MsgBox(http.responsetext)
Set JSON = ParseJson("[" + http.responsetext + "]")

For Each Item in JSON

    outputSheet.Cells(rowNum, 2).Value = JSON(1).Item("output_Range_Age1")
    outputSheet.Cells(rowNum, 3).Value = JSON(1).Item("output_Range_Age2")
    outputSheet.Cells(rowNum, 4).Value = JSON(1).Item("output_Range_Age3")
    outputSheet.Cells(rowNum, 5).Value = JSON(1).Item("output_Range_Age4")
    outputSheet.Cells(rowNum, 6).Value = JSON(1).Item("output_Range_Age5")
    outputSheet.Cells(rowNum, 7).Value = JSON(1).Item("output_Range_Custom")
    outputSheet.Cells(rowNum, 8).Value = JSON(1).Item("output_Balance_Age1")
    outputSheet.Cells(rowNum, 9).Value = JSON(1).Item("output_Balance_Age2")
    outputSheet.Cells(rowNum, 10).Value = JSON(1).Item("output_Balance_Age3")
    outputSheet.Cells(rowNum, 11).Value = JSON(1).Item("output_Balance_Age4")
    outputSheet.Cells(rowNum, 12).Value = JSON(1).Item("output_Balance_Age5")
    outputSheet.Cells(rowNum, 13).Value = JSON(1).Item("output_Balance_Custom")
    outputSheet.Cells(rowNum, 14).Value = JSON(1).Item("output_Balance_LumpSum")
    outputSheet.Cells(rowNum, 16).Value = JSON(1).Item("output_CurrentAge")
    outputSheet.Cells(rowNum, 17).Value = JSON(1).Item("output_MinAgeTerm")
    outputSheet.Cells(rowNum, 18).Value = JSON(1).Item("output_MaxAgeTerm")
    outputSheet.Cells(rowNum, 19).Value = JSON(1).Item("output_MinAgeBCD")
    outputSheet.Cells(rowNum, 20).Value = JSON(1).Item("output_MaxAgeBCD")
    outputSheet.Cells(rowNum, 22).Value = JSON(1).Item("output_DROP_Question")
    outputSheet.Cells(rowNum, 23).Value = JSON(1).Item("output_DROP_Years")
    outputSheet.Cells(rowNum, 24).Value = JSON(1).Item("output_DROP_Start")
    outputSheet.Cells(rowNum, 25).Value = JSON(1).Item("output_DROP_1stYear")
    outputSheet.Cells(rowNum, 26).Value = JSON(1).Item("output_DROP_Acumulation")
    outputSheet.Cells(rowNum, 27).Value = JSON(1).Item("output_DROP_AccumulationAnnuity")
    outputSheet.Cells(rowNum, 28).Value = JSON(1).Item("output_DROP_TotalAnnuity")
    outputSheet.Cells(rowNum, 29).Value = JSON(1).Item("output_DROP_COLA")
    outputSheet.Cells(rowNum, 31).Value = JSON(1).Item("output_BuyBack_Payment")
    outputSheet.Cells(rowNum, 32).Value = JSON(1).Item("output_BuyBack_IP_AddOn")
    outputSheet.Cells(rowNum, 33).Value = JSON(1).Item("output_DROP_BuyBack_TotalAnnuity")

Next
    
End Sub

 

[Prachijain]

Hey hotabae, Thanks for the effort you made in answering my query .But the code you sent me as so many errors and I can hardly resolve them. Can you just send me a error free code please.

 

[Prachijain]

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

Sub Macro2()Dim http As Object
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




End Sub

 

[hotabae]

Sorry, I didn't expect my last post to work really. I was just presenting a general idea/structure for you to use as a springboard. As I said, I haven't ever used JSON before, so I used a lot of your existing work to construct my code.

Unfortunately I don't think I will be able to help you further.

I would recommend starting a new thread to get another perspective than mine. A new post will attract more eyes.

 

[Prachijain]

Thank you hotabae for your help and time. Appreciate it.