Invantive App Online allows you to offer your own apps. For example, these apps can have as a result JSON or an Excel file with data from the linked data containers.
In this example, an existing Invantive Cloud app that accesses Exact Online sales figures directly from Excel to retrieve the data and store it in the file system. See also App Online module om alle Exact Online verkoopfactuurregels te downloaden (Dutch).
This approach is purely illustrative of the use of VBA with App Online; it is a mile-by-mile approach, as the data can also be retrieved directly with curl or directly via Power Query.
This functionality is only available under the Windows version of Excel, as Microsoft Excel for Mac lacks large portions of the functionality on Windows. The use of curl is possible on the Mac version, but Power Query is not yet available.
Security.
In this example, (confidential) password information is stored unencrypted. This is purely for demonstration purposes. Advice is to retrieve passwords every time.
Excel download from Exact Online via Invantive App Online
The first step is to create a new Excel file with the following contents:
Here, cells C2, C3, C4, C6 and C7 are to be filled. The data in the gray cells are calculated or fixed:
- C10: copied from App Online link returned by “Copy Link”
- C11: copied from App Online link returned by “Copy Link”
- C12: copied from App Online link returned by “Copy Link”
- C13: copied from App Online link returned by “Copy Link”
- C14: fixed
- C15:
=YEAR(C6) & "-" & MONTH(C6) & "-" & DAY(C6) - C16:
=YEAR(C7) & "-" & MONTH(C7) & "-" & DAY(C7) - C18:
=C10 & "/apps/"&C11&"/databases/"&C12&"/modules/"&C13&"/?"&B14&"="&C14&"&"&B15&"="&C15&"&"&B16&"="&C16
VBA
The second step is to draw the Download button and register the following code in VBA on the click event:
'
' Handle button click for download.
'
Private Sub DownloadButton_Click()
Dim url As String
Dim username As String
Dim password As String
Dim targetFilename As String
url = Range("C18").Value
username = Range("C2").Value
password = Range("C3").Value
targetFilename = Range("C4").Value
Dim objXmlHttpReq As Object
Dim objStream As Object
Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")
objXmlHttpReq.Open "GET", url, False, username, password
objXmlHttpReq.send
If objXmlHttpReq.Status = 200 Then
Set objStream = CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = 1
objStream.Write objXmlHttpReq.responseBody
objStream.SaveToFile targetFilename, 2
objStream.Close
End If
End Sub
In the Microsoft Visual Basic for Applications Editor, it looks like this:
Usage
The user can now open the Excel sheet and select the download button. The Invantive App Online module will retrieve the figures from Exact Online as an Excel sheet and save them in the folder c:\temp as DATA.xlsx.
This Exact Online sales data can be further processed, for example, or used in a pivot table as described in Draaitabel of rapport baseren op gegevens in een ander (extern) Excel-bestand (Dutch).