-1

Part 1 - If I just need to extract data from information views of SAP hana in excel, I know we can do it manually with UI where we establish connection, select DB and view, then select measure and dimension and data is downloaded in excel as Pivot. Can we do the same with excel VBA so that i can automate the end to end process within excel from connecting to selecting calculation view to providing measure and dimension to download report as pivot in excel. Also will we have to write a MDX within VBA code to mention measure and dimension.

Any sample code or guide for the same will be quite helpful.

Above case is when calculation view are in SAP HANA DB.

Part 2 - If CDS is part of ABAP, as per my understanding we use SAP business objects Analysis for connecting to view from excel and downloading the report. This also involves some manual UI steps like logon, connect etc. If I again have to automate the end to end process with VBA in this case where on click of a button my VBA code connects with ABAP CDS and download the data in excel as pivot so that I can process it further.

Any sample code or guide for the same will be quite helpful.

I am bit confused in multiple options available in excel to connect with SAP, manually as well as automated with VBA –

1)With ODBC, For which we need HDBODBC

ODBC Image

2)SAP Hana MDX provider –

MDX Provider

How do we get MDX option and how is it different from ODBC one – both available with SAP client installation?

Is the difference that with ODBC, we will use SQL query and it will download data from SAP Hana tables and not views (which are multidimensional cubes)

Hana MDX provider will download views using MDX and what about tables; will it support tables as well?

Can we automate both of these with VBA end to end from connection to getting data in excel?

3)For ABAP CDS, is the only option - SAP business objects Analysis

Can this be automated end to end with VBA?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Mayank
  • 11
  • 4
  • Does this answer your question? [Connect to SAP HANA Database using VBA](https://stackoverflow.com/questions/43585653/connect-to-sap-hana-database-using-vba) – Suncatcher Jul 07 '20 at 07:27
  • The link - "https://stackoverflow.com/questions/43585653/connect-to-sap-hana-database-using-vba" answers only a very small part of question i.e. how to get SAP MDX connector. I am trying to gather all ways of working between SAP and Excel especially with VBA for the purpose of automation. I wasn't able to find concrete information, so i consolidated everything in one question so that it can be helpful to me and others in future. Anyways thanks for sharing the link as I got some information from it. Looking forward for rest of the answers. – Mayank Jul 08 '20 at 04:20
  • `I am trying to gather all ways of working between SAP and Excel` there are [dozens](https://stackoverflow.com/search?q=sap+excel) of such questions and yours brings nothing meaningful and useful. Always make (re)search on your topic before posting anything – Suncatcher Jul 08 '20 at 06:23

1 Answers1

1

Also posted on the SAP Community: https://answers.sap.com/questions/13084676/connecting-to-sap-hana-from-excel-vba-to-get-data.html

Denys van Kempen
  • 31
  • 4