How to release excel VBA connection to API

Posts about the simPRO API. Help/support/how-to etc.
Forum rules
  • Check the documentation first. Before posting questions, please check the documentation to ensure it doesn't already cover what you need.
  • No spam. All automated messages, advertisements, and links to competitor websites will be deleted immediately.
  • Post in relevant sub-forums only. Messages posted in the wrong topic area will be removed and placed in the correct sub-forum by moderators.
  • Respect other users. No flaming or abusing fellow forum members. Users who continue to post inflammatory, abusive comments will be deleted from the forum after two warnings are issued by moderators.
  • Harassment. No threats or harassment of other users will be tolerated. Any instance of threatening or harassing behavior is grounds for deletion from the forums.
  • Adult content. No profanity or pornography is allowed. Posts containing adult material will be deleted.
  • Bandwidth. All images and signatures must be 500 x 500 pixels or smaller. Posts containing over-sized images and signatures will be removed.
  • Illegal content. No re-posting of copyrighted materials or other illegal content is allowed. Any posts containing illegal content or copyrighted materials will be deleted.
Post Reply
dmpchch
Posts: 36
Joined: Mon Aug 26, 2019 2:52 am

How to release excel VBA connection to API

Post by dmpchch » Tue Aug 27, 2019 2:45 am

Hi, To reduce manual input after datafeed had created our work orders I am using Excel VBA to 'top up' our orders.
We get create 50+ jobs per day which require 1 to 5 cost centers most with pre builds.

So what I do is

- Find current section and cost center
-change custom field
-Add due time and adjust due date if required.
-Add required cost centers (deleting original at the same time)
- Add Prebuild to each cost center - This is where is comes unstuck
- add tags
- set status

So when I add the prebuilds they have to go against the new cost centers, but it maintains previous cost center id code and does not apply the prebuild unless I close all excel workbooks and reopen to refresh the connection. I have tried setting both results and httpObject to Nothing.

Below code is used twice, one at either end of the process but on 2nd run retains data from first run.

Any clues?

Thanks

Code: Select all

Sub fIND_new_cc()
Dim httpObject As Object

Set httpObject = CreateObject("MSXML2.XMLHTTP")
Dim OrDer
Dim TBC
Dim rw
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String
TBC = 2
rw = 2
Dim sEC
Sheets("Data").Select
Do Until ActiveSheet.Cells(TBC, 1).Value = ""

OrDer = ActiveSheet.Cells(TBC, 1).Value
sEC = ActiveSheet.Cells(TBC, 13).Value
sURL = "https://######.simprosuite.com/api/v1.0/companies/0/jobs/" & OrDer & "/sections/" & sEC & "/costCenters/"
sRequest = sURL
httpObject.Open "GET", sRequest, False
httpObject.setRequestHeader "Authorization", "Bearer key"
httpObject.setRequestHeader "Content-Type", "application/json"
httpObject.send
sgetresult = httpObject.responseText

openPos = InStr(sgetresult, ":")
closePos = InStr(sgetresult, ",")
midBit = Mid(sgetresult, openPos + 1, closePos - openPos - 1)
'MsgBox (sgetresult)

ActiveSheet.Cells(TBC, 31).Value = midBit



TBC = TBC + 1

Set sgetresult = Nothing
Loop
Set sgetresult = Nothing
Set httpObject = Nothing
Call Add_PB
End Sub
Top
Post Reply

Return to “General Discussion”