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?