Downloading data from Excel VBA on Windows using Invantive App Online

Go to English version

Invantive App Online makes it possible to offer your own apps. These apps can, for example, produce JSON or an Excel file as a result, containing data from the linked data containers.

In this example, an existing Invantive Cloud app that accesses Exact Online sales figures is approached directly from Excel in order to retrieve the data and save it to the file system. See also App Online module om alle Exact Online verkoopfactuurregels te downloaden.

This approach is purely illustrative of the use of VBA with App Online; it is a roundabout way of doing things, since the data can also be retrieved directly using curl or directly via Power Query (see also Hoe gebruik ik curl om een Invantive Cloud OData4-verzoek uit te voeren?).

This functionality is only available in the Windows version of Excel, since Microsoft Excel for Mac lacks large parts of the functionality found in the Windows version. 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. The advice is to request passwords anew each time.

Excel download from Exact Online via Invantive App Online

The first step is to create a new Excel file with the following content:

Here, cells C2, C3, C4, C6 and C7 can be filled in. The data in the grey cells is calculated or fixed:

  • C10: copied from the App Online link returned by “Copy Link”
  • C11: copied from the App Online link returned by “Copy Link”
  • C12: copied from the App Online link returned by “Copy Link”
  • C13: copied from the App Online link returned by “Copy Link”
  • C14: fixed
  • C15: =JAAR(C6) & "-" & MAAND(C6) & "-" & DAG(C6)
  • C16: =JAAR(C7) & "-" & MAAND(C7) & "-" & DAG(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 for 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, this looks as follows:

Usage

The user can now open the Excel sheet and select the download button. The Invantive App Online module retrieves the figures from Exact Online as an Excel sheet and saves them in the c:\temp folder as DATA.xlsx.

This Exact Online sales data can, for example, be processed further or used in a pivot table as described in Draaitabel of rapport baseren op gegevens in een ander (extern) Excel-bestand.