Forum Discussion

Chetana's avatar
Chetana
Copper Contributor
Aug 06, 2019

VBA

Hi

 

Can some one help me on below use case:

 

I want to make a rest call [post/get] from excel macro from background . The macro should execute silently for every 5sec.  If rest call gets valid response, show some message to user.

 

Note: When macro is running in the background asynch , it should not interrupt the user experience on excel

Excel for web
Need Help
Requests
Show and Tell
tips and tricks

4 Replies

    • Chetana's avatar
      Chetana
      Copper Contributor
      Aug 08, 2019

      I have tried call back method. But the problem is the excel is getting slow as the macro is running contentiously in the back ground. Below is the code I have used FYR

       

      Public sub TestRestServiceStatus()

      Set MyOnReadyStateWrapper = New MyReadyStateHandler
      Set docx = New MSXML2.XMLHTTP60
      docx.OnReadyStateChange = MyOnReadyStateWrapper
      docx.Open "POST", urlString, False

      docx.send jsonBody

      responceStatus = MyOnReadyStateWrapper.IsReadyState
      If (responceStatus = True) Then
        Dim notificationOutPut As String: notificationOutPut = MyOnReadyStateWrapper.outPutText

            If InStr(notificationOutPut , "PROCESSING") > 0 Then
                       Call TestRestServiceStatus     

          ElseIf  InStr(notificationOutPut , "COMPLETE") > 0 Then

               'Notify User on Excel

            EndIF

      EndIf

       

      -----------------------Below is the MyOnReadyStateWrapper module-----------------

       

      Sub OnReadyStateChange()

      DoEvents
      If Actions.docx.readyState = 4 Then
      If Actions.docx.Status = 200 Then
      IsReadyState = True
      outPutText = Actions.docx.responseText
      ActiveWorkbook.Worksheets(AmbassadorSheetName).Range("H50").Value = outPutText & DateTime.Now
      Exit Sub
      Else
      IsReadyState = False
      Exit Sub
      End If
      End If

      End Sub

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Aug 08, 2019
        Hmm, I don't think I can help much based on this information. I would probably have to see the entire project. EVen then, I currently lack the time to do dedicated trouble-shooting on this, I'm sorry!

Resources