The following source code allows you to connect your DataSource to a BW system. The workbook must contain a DataSource (DS_1). At this moment you can't open a workbook like BEx 3.5 or 7.0. In Analysis for Office 2.3 you have now the option to open a workbook with the function SAPOpenWorkbook.
If you display the design area of Analysis for Office, you receive the following error:
The data source shows no field and you cannot select anything.
After you logon to the BW system, you have to refresh the DataSource first. The following source code login and refresh the DataSource:
These posts might also be interesting: