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 three core functions for this example and can be used by any VBA program by simply copying the 3 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:
- Format the data into a ODBC WriteNow ready format and send the data to the PostChange function.
- The PostChange function contructs the URL using the saved ODBC WriteNow API Key and the data and send the information to the ServerRequest function.
- The ServerRequest function then encodes the information using the encodeURL function and sends the data to ODBC WriteNow.
- The ServerRequest function then reads the response from ODBC WriteNow and displays a message information 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