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
Highlighted
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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies