VBA Download Example
The following is a fully functional VBA coding example for downloading MYOB Data and importing it into a new table in MS Access.
This function has been designed to work with minimal changes but requires an MS Access Import specification to be created for each MYOB Table first. A short guide on how to create this Import Specification is included below.
MS Access Import Specification
The basic steps for creating and using a saved import in MS Access are as follows:
- Download a sample of the data you wish to import. In this case, that would be one page of records from ODBC WriteNow for the MYOB Table you wish to import.
- In MS Access, from the External Data tab, select the "Import from Text File" option and choose the file from ODBC WriteNow.
- Follow through on the Import wizard in MS Access, adjusting field names and data types as needed, e.g., setting a field as datetime.
- In the final step of the Import wizard, select "Save Import steps" and name the specification.
If you have followed the above steps, you should now have a saved import specification that you can use to correctly import any future ODBC WriteNow data for that MYOB table.
For a more detailed guide and general information about MS Access saved imports, please visit the link below:
Save the details of an import or export operation as a specificationDownload and Import Function
The example below of a Download and Import function uses the following steps:
- Download only the table headers from ODBC WriteNow and enter the information into a new temp CSV file.
- Loop through sending page requests into ODBC WriteNow and update the received records into the CSV file until ODBC WriteNow returns a "no records" error.
- Import the CSV file into MS Access using a premade Access Import Specification.
- Clean up and remove the temporary CSV files, ready for the next import.
Public Function DownloadAndImport(what As String, tablename As String, Secification As String) As String
Dim data As String
Dim page As Integer
Dim fs As Object ''FileSystemObject
Dim tsOut As Object ''TextStream
'Your Company's API Key goes below
apikey = ""
server = "http://myobsync.accede.com.au/"
page = 0
lastPage = False
'download the complete file first
Set fs = CreateObject("Scripting.FileSystemObject")
tempFileName = "C:windows empmyobsynctemp_" & tablename & ".csv"
Set tsOut = fs.CreateTextFile(tempFileName, True)
'Get Header Row
data = ServerRequest("GET", server & "download/" & what & "/" & page & "?onlyheader=1&apikey=" & apikey, "")
If InStr(data, "MYOBSync Error") <> 0 Then
lastPage = True
Else
tsOut.WriteLine data
End If
'Get csv data
While lastPage = False
SysCmd acSysCmdSetStatus, "Downloading To file " & what & ", Page " & page
data = ServerRequest("GET", server & "download/" & what & "/" & page & "?onlyheader=0&apikey=" & apikey, "")
'Check for a MYOBSync Error, meaning it has returned no records
If InStr(data, "MYOBSync Error") <> 0 Then
lastPage = True
Else
tsOut.WriteLine data
page = page + 1
End If
DoEvents
Wend
tsOut.Close
'Import CSV Data into table
SysCmd acSysCmdSetStatus, "Importing File " & what & " to table " & tablename & ", Page " & page
DoCmd.TransferText acImportDelim, Secification, tablename, tempFileName, True
SetAttr tempFileName, vbNormal
SysCmd acSysCmdClearStatus
End Function
Public Function encodeURL(str As String)
Dim ScriptEngine As Object
Dim encoded As String
Set ScriptEngine = CreateObject("scriptcontrol")
ScriptEngine.Language = "JScript"
encodeURL = ScriptEngine.Run("encodeURIComponent", str)
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