VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-789650%22%20slang%3D%22en-US%22%3EVBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789650%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20some%20one%20help%20me%20on%20below%20use%20case%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20make%20a%20rest%20call%20%5Bpost%2Fget%5D%20from%20excel%20macro%20from%20background%20.%20The%20macro%20should%20execute%20silently%20for%20every%205sec.%26nbsp%3B%20If%20rest%20call%20gets%20valid%20response%2C%20show%20some%20message%20to%20user.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20When%20macro%20is%20running%20in%20the%20background%20asynch%20%2C%20it%20should%20not%20interrupt%20the%20user%20experience%20on%20excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-789650%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EShow%20and%20Tell%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-789907%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789907%22%20slang%3D%22en-US%22%3EWhat%20have%20you%20tried%20so%20far%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794457%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794457%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20call%20back%20method.%20But%20the%20problem%20is%20the%20excel%20is%20getting%20slow%20as%20the%20macro%20is%20running%20contentiously%20in%20the%20back%20ground.%20Below%20is%20the%20code%20I%20have%20used%20FYR%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20sub%20TestRestServiceStatus()%3C%2FP%3E%3CP%3ESet%20MyOnReadyStateWrapper%20%3D%20New%20MyReadyStateHandler%3CBR%20%2F%3ESet%20docx%20%3D%20New%20MSXML2.XMLHTTP60%3CBR%20%2F%3Edocx.OnReadyStateChange%20%3D%20MyOnReadyStateWrapper%3CBR%20%2F%3Edocx.Open%20%22POST%22%2C%20urlString%2C%20False%3C%2FP%3E%3CP%3Edocx.send%20jsonBody%3C%2FP%3E%3CP%3EresponceStatus%20%3D%20MyOnReadyStateWrapper.IsReadyState%3CBR%20%2F%3EIf%20(responceStatus%20%3D%20True)%20Then%3CBR%20%2F%3E%26nbsp%3B%20Dim%20notificationOutPut%20As%20String%3A%20notificationOutPut%20%3D%20MyOnReadyStateWrapper.outPutText%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20InStr(notificationOutPut%20%2C%20%22PROCESSING%22)%20%26gt%3B%200%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Call%20TestRestServiceStatus%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ElseIf%26nbsp%3B%20InStr(notificationOutPut%20%2C%20%22COMPLETE%22)%20%26gt%3B%200%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Notify%20User%20on%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20EndIF%3C%2FP%3E%3CP%3EEndIf%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----------------------Below%20is%20the%20MyOnReadyStateWrapper%20module-----------------%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20OnReadyStateChange()%3C%2FP%3E%3CP%3EDoEvents%3CBR%20%2F%3EIf%20Actions.docx.readyState%20%3D%204%20Then%3CBR%20%2F%3EIf%20Actions.docx.Status%20%3D%20200%20Then%3CBR%20%2F%3EIsReadyState%20%3D%20True%3CBR%20%2F%3EoutPutText%20%3D%20Actions.docx.responseText%3CBR%20%2F%3EActiveWorkbook.Worksheets(AmbassadorSheetName).Range(%22H50%22).Value%20%3D%20outPutText%20%26amp%3B%20DateTime.Now%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EElse%3CBR%20%2F%3EIsReadyState%20%3D%20False%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794636%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794636%22%20slang%3D%22en-US%22%3EHmm%2C%20I%20don't%20think%20I%20can%20help%20much%20based%20on%20this%20information.%20I%20would%20probably%20have%20to%20see%20the%20entire%20project.%20EVen%20then%2C%20I%20currently%20lack%20the%20time%20to%20do%20dedicated%20trouble-shooting%20on%20this%2C%20I'm%20sorry!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794667%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794667%22%20slang%3D%22en-US%22%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F20745%22%20target%3D%22_blank%22%3E%40Jan%3C%2FA%3EIt%20be%20would%20be%20helpful%20if%20any%20one%20can%20just%20suggest%20me%20the%20best%20way%20to%20do%20this%20usecase.%3C%2FLINGO-BODY%3E
Chetana
New Contributor

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

4 Replies
What have you tried so far?

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

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!
Thanks @Jan It be would be helpful if any one can just suggest me the best way to do this usecase.
Related Conversations