Posting from Excel to FreeAgent

1

I would like to post invoice data from Excel to FreeAgent. Anyone have some useful code to do this?

2

Oh! I am looking t=for the Same!

3

I managed it. Code below. Please bear in mind that I am not a programmer so the code is probably not the most tidy or efficient. Works a dream though. Have since written code to add projects and contacts too.

Sub InvoicePost()

''''''Macro by Ed Dablin''''''

''''''''Authentication''''''''''

Dim TargetURL As String
TargetURL = "https://api.freeagent.com/v2/token_endpoint"

Dim getTokenBody As String
getTokenBody = "client_id=##############&refresh_token=###################&grant_type=refresh_token"

Dim AccessToken

Dim authUser As String
authUser = "#############"

Dim authPass As String
authPass = "##################"

Set HTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
HTTPReq.Option(4) = 13056 '
HTTPReq.Open "POST", TargetURL, False
HTTPReq.SetCredentials authUser, authPass, 0
HTTPReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
HTTPReq.send (getTokenBody)

AccessToken = Right(Left(HTTPReq.responseText, 58), 41)

''''''''Post Invoice''''''''''

Dim Reference As String
Reference = ActiveSheet.Range("A14").Value

Dim AmountDue As String
AmountDue = ActiveSheet.Range("F14").Value / 1.2

Dim InvoiceDate As String
InvoiceDate = Format(ActiveSheet.Range("B14").Value, "yyyy-mm-dd")

Dim PaymentDays As String
PaymentDays = ActiveSheet.Range("C14").Value

Dim Client As String
Client = Worksheets("INV01").Range("K6").Value

Dim ProjectRef As String
ProjectRef = Worksheets("INV01").Range("K7").Value

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    Url = "https://api.freeagent.com/v2/invoices"
    objHTTP.Open "POST", Url, False
    objHTTP.setRequestHeader "Content-Type", "application/json"
    objHTTP.setRequestHeader "Authorization", "Bearer " & AccessToken
    body = "{""invoice"":{""contact"":""" & Client & """,""project"":""" & ProjectRef & """,""dated_on"":""" & InvoiceDate & """,""reference"":""" & Reference & """,""payment_terms_in_days"":" & PaymentDays & ",""invoice_items"":[{""description"":""See Manual Invoice"",""item_type"":""Services"",""price"":""" & AmountDue & """,""quantity"":""1""}]}}"
    objHTTP.send body

    replyTXT = objHTTP.responseText
        
''''''''Load FreeAgent to display invoice or project invoices''''''''''
         
Dim InvoicePage As String
InvoicePage = "https://#######.freeagent.com/invoices/" & Right(Left(replyTXT, 65), 8) & ""
       
    If objHTTP.Status = "422" Then
        MsgBox "Reference already exists"
        ThisWorkbook.FollowHyperlink ("https://######.freeagent.com/projects/" & Right(ProjectRef, 7) & "/invoices")
    Else
        ThisWorkbook.FollowHyperlink (InvoicePage)
    End If
    
End Sub