You can write an ABAP program for exporting the data from SAP and then use a remote function call which can be invoked my macro to fetch the data. Also, you can go for creating the file itself if that fits the requirement.
You can also opt for SAP GUI Scripting as an alternative. The scripting framework allows you to automate the interface for Windows and Java. It can let you perform a lot of activities.
Scripting API provides you with the good number of scriptable objects which wraps the SAP GUI objects. You can use these objects from macros. SAP GUI comes with a handy macro recording tool. This tool can be used to record macros in VBScript (which is similar to VBA) and then be used directly with an Excel macro. Just copy and paste the VBScript and it works.