VBA Upload Example

Sending data to ODBC WriteNow is a simple matter of formatting the data you want to send to MYOB in a text-based format and posting the data to our ODBC WriteNow website backend.

The example below contains 3 base functions: `PostChange`, `encodeURL`, and `ServerRequest`, which are the core functions for this example. They can be used by any VBA program by simply copying the functions and adding the user’s ODBC WriteNow API Key where commented.

The final function, `custtfer`, is an example of how to correctly format data for import into the MYOB customer table as an individual.

The example below uses the following steps:

1. Format the data into an ODBC WriteNow-ready format and send the data to the `PostChange` function.
2. The `PostChange` function constructs the URL using the saved ODBC WriteNow API Key and sends the information to the `ServerRequest` function.
3. The `ServerRequest` function then encodes the information using the `encodeURL` function and sends the data to ODBC WriteNow.
4. The `ServerRequest` function then reads the response from ODBC WriteNow and displays a message informing the user of the result.

Public Function custtfer()
    DoCmd.SetWarnings True
    Dim SQL As String, vno As Integer
    
    SQL = "Style=Indivual" & vbCrLf
    SQL = SQL & "Lastname=Cogswell" & vbCrLf
    SQL = SQL & "Firstname=Matthew" & vbCrLf
    SQL = SQL & "Street=50 Mawson Lakes Blvd" & vbCrLf
    SQL = SQL & "City=Mawson Lakes" & vbCrLf
    SQL = SQL & "State=SA" & vbCrLf
    SQL = SQL & "Postcode=5095" & vbCrLf
    SQL = SQL & "Phone1=8363 5699" & vbCrLf
    SQL = SQL & "Phone2=" & vbCrLf
    
    res = PostChange(SQL)
    vno = MsgBox("Customer transfer " & res, vbOKOnly, "Transfer Complete")
End Function

Public Function PostChange(str As String)
    Server = "http://myobsync.accede.com.au/"
    apikey = "" 'You Company's API Key
    serverPost = ServerRequest("POST", Server & "sync/post" & "?apikey=" & apikey & "", str)
    Debug.Print serverPost
    PostChange = serverPost
End Function

Public Function encodeURL(str As String)
    Dim ScriptEngine As Object
    Dim encoded As String
    
    Set ScriptEngine = CreateObject("scriptcontrol")
    ScriptEngine.Language = "JScript"
    
    encoded = ScriptEngine.Run("encodeURIComponent", str)
    
    encodeURL = encoded
End Function

Public Function ServerRequest(method As String, url As String, data As String)
    Dim objHttp As Object
    Debug.Print "URL:" & url
    
    Set xmlhttp = CreateObject("Msxml2.XMLHTTP")
    xmlhttp.Open method, url, False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    Debug.Print data
    data = encodeURL(data)
    Debug.Print data
    
    xmlhttp.Send "&data=" & data
    
    ServerRequest = xmlhttp.responseText
    Set objHttp = Nothing
End Function