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

Prachijain

New Member
Joined
May 8, 2018
Messages
12
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
I'm sorry , I was figuring how to share the image. Here are my inputs and outputs look like
Excel 2010
CDEFG
1Input FieldsValuesOutput FieldsValues
2input_BirthDate6/5/1954output_Range_Age170
3input_HireDate8/1/2013output_Range_Age2 
4input_GenderFoutput_Range_Age3 
5input_AnnualPlanComp50224output_Range_Age4 
6input_EmployeeClassRegular School Districtoutput_Range_Age5 
7input_AnnualPayGrowth1output_Range_Custom64
8input_MarketPerformanceAverageoutput_Balance_Age1$ -
9output_Balance_Age2$ -
10input_PVD70output_Balance_Age3$ -
11input_NRA62output_Balance_Age4$ -
12input_MBAA65output_Balance_Age5$ -
13input_Custom_BCD64output_Balance_Custom$ -
14input_Custom_TermAge64output_Balance_LumpSum$ -
15
16input_RemainingElections1output_CurrentAge63
17output_MinAgeTerm64
18input_Pre2011ServiceYears4output_MaxAgeTerm80
19input_ProjectedServiceYears11output_MinAgeBCD70
20input_ProjectedBenefitAmount7028.58output_MaxAgeBCD65
21input_DROP_LumpSum0
22output_DROP_QuestionFALSE
23input_ProjBuyBackABOoutput_DROP_Years 
24input_DateABO2/28/2018output_DROP_Start 
25input_ProjectedABO103461.51output_DROP_1stYear 
26input_ProjectedAAL48696output_DROP_Accumulation 
27input_DateAAL3/31/2018output_DROP_AccumulationAnnuity 
28input_InvestmentBalanceTBA0output_DROP_TotalAnnuity 
29input_CurrentABO80772.6output_DROP_COLA1.09%
30
31output_BuyBack_PP$ 48,696.00
32output_BuyBack_Payment$ 48,696.00
33output_BuyBack_IP_AddOn$ 2,626.60
34output_BuyBack_TotalAnnuity$ 2,626.60
Inputs Outputs
Cell Formulas
RangeFormula
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
NameRefers ToCells
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
 
Upvote 0
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"

Code:
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
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1TestTest Casesinput_BirthDateinput_HireDateinput_Genderinput_AnnualPlanCompinput_EmployeeClassinput_AnnualPayGrowthinput_MarketPerformanceinput_PVDinput_NRAinput_MBAAinput_Custom_BCDinput_Custom_TermAgeinput_RemainingElectionsinput_Pre2011ServiceYearsinput_ProjectedServiceYearsinput_ProjectedBenefitAmountinput_DROP_LumpSuminput_ProjBuyBackABOinput_DateABOinput_ProjectedABOinput_ProjectedAALinput_DateAALinput_InvestmentBalanceTBAinput_CurrentABO
2
311/4/198310/1/2012F36179.3Regular School District1Average3863663590106.3304461.1801/31/20184461.1801/1/190003340.03
42
ip



This is how my code looks like
Code:
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.
 
Upvote 0
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

Code:
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

Code:
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

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top