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:

  1. Format the data into a ODBC WriteNow ready format and send the data to the PostChange function.
  2. The PostChange function contructs the URL using the saved ODBC WriteNow API Key and the data and send 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 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