Skip to Content

SAP HANA from excel VBA to get data, different options to work with SAP using excel for data

Hi,

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

2)SAP Hana MDX provider –

untitled1.png

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?

untitled.png (62.3 kB)
untitled1.png (31.2 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Jul 08 at 04:12 AM

    @Denys van Kempen - Thanks for your response. Yes I posted the same question at both places hoping to get an answer for the same. I still haven't got any answer to it. I have tried to consolidate all information at one point in my question thinking that if I get a detailed answer to it, will be lot helpful to other as well. Before posting my question, I searched different things on google but struggled to find a concrete solution with good information.

    If you have the solution or you know someone who can solve it, kindly help me with the same. It will be really very helpful to me.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.