Excel 2007 VBA code to automate extracting & storing number

[shadow]

I have a string which is in A1 of Sheet1 and it refreshes regularly. it looks like this -
{"rows":[{"advances":637,"declines":836,"unchanged":76,"total":1549}],"success":"true","results":1}


I want to extract numeric values 637 and 836 and 76 and store it in separate columns. The values keeps on changing while auto refreshing , it may be 1/2/3/4 digits. I want a VBA code for my excel sheet, to automatically extract the numeric data and store it every time the value refreshes.and this will copy the values to another sheet. Many thanks. warm regards.
I am attaching the sheet in the attachment.

[Sisyphus]

Hi,
Have a look at this solution, copied from Module1 in the attached workbook:

Code: Select all

Option Base 0

Public Sub ExtractAndWrite(ByRef Target As Range)

    Const FirstCol As Long = 1
   
    Dim S() As String
    Dim i As Integer
    Dim R As Long
    Dim C As Long
   
    S = Split(Target.Value, ":")
    With ThisWorkbook.Sheets(1)
        R = .Cells(65536, 1).End(xlUp).Row + 1
        i = 2
        For C = FirstCol To FirstCol + 2
            .Cells(R, C).Value = Val(S(i))
            i = i + 1
        Next C
    End With
End Sub


This code will write the three numbers you are interested in in 3 adjecent columns starting with Column A. "A" is defined in the Const FirstCol at the top of the code where 1 = A, 2 = B etc. You can change it as you wish.

The feed is in A1 of the same worksheet as the result. If you want it in another sheet it would be a small change. I couldn't look at your workbook because of its format. When you transfer this code to a workbook in your own format, use copy / paste and make sure you place it in a normal code module, by default Module1.

The above code is triggered by this little procedure which you must place in the code sheet of the worksheet that has the feed. Use Copy / Paste to put it there:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    Const Feed As String = "$A$1"
    If Target.Address = Feed Then ExtractAndWrite Target
End Sub


You can see that the Const Feed defines A1 as the range where the feed is. Use the format $A$1 (absolute referencing) and you can change it to any other address (in the same workbook). If there is a change in that cell the ExtractAnd Write procedure is called. You can test this by manually making a change in the string in A1 on the attached workbook. Once you get whatever program to change the contents of A1 the same thing will happen and your list will be prepared.

I hope this does the job!