1

In my excel sheet I've written an excel macro. In Visual Studio I have a piece of code in VB.net to parse a Json response from a REST API using Newtonsoft.

I'm using a user form to get comments in an excel cell (my VBA macro). Is it possible to use Newtonsoft library with this VBA project itself?

Is there any other way to parse a json in my excel macro?

My macro

Private Sub CommandButton2_Click()
Selection.ClearComments
Selection.AddComment Text:=TextBox1.Text
Unload Me
End Sub

My VB.net code

Imports System.IO
Imports System.Net
Imports Newtonsoft.Json

'Classes for Json response of my service

'Sample Json from DIS:
'{"A": {"B":1}, "A1": {"B1":"1"}}
Public Class JSON_Response
    Public a As A
    Public a1 As A1
End Class

Public Class A
    Public b As Integer
End Class

Public Class A1
    Public b1 As String
End Class

Public Class MyClass

Private Sub Call_Service() Handles Me.Startup
    'Create Http Request and Response objects
    Dim request As HttpWebRequest = HttpWebRequest.Create("http://something.com/key/123")
    Dim response As HttpWebResponse

    'Make Http Request
    request.Method = WebRequestMethods.Http.Get

    'Get Http Response
    response = request.GetResponse()

    'Convert Response to Json String
    Dim dataStream As Stream
    dataStream = response.GetResponseStream()
    Dim reader As New StreamReader(dataStream)
    Dim jsonResponseFromServer As String = reader.ReadToEnd()

    'Map Json Response to objects
    Dim jsonResponse As JSON_Response = JsonConvert.DeserializeObject(Of JSON_Response)(jsonResponseFromServer)
End Sub
End Class

Basically I want to use the VB.net code inside of the excel macro. Is this possible?

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Anusha
  • 337
  • 2
  • 4
  • 21

0 Answers0