cancel
Showing results for 
Search instead for 
Did you mean: 

Connecting HANA with Excel through VBA

Former Member
0 Kudos

I been trying to figure this out by researching but with little luck.

I want to create an application with excel vba that lets the user connect to HANA and be able to insert data into a hana view from vba.

any hints would be appreciated!

so far Ive taken a look at the thread:

Dim CONN As Object 'ADODB.Connection

Dim rs As Object 'ADODB.RecordSet

Dim StrSql As String

StrSql = "Provider=SAPNewDBMDXProvider.1;Data Source=10.xxx.xxx.30:30015;Password=xxx;User ID=xxx;Location=;Integrated Security=;Persist Security Info=True;Impersonation Level=Anonymous;Mode=Read;Protection Level=None;Extended Properties='SFC_INSTANCE_NUM=00';Initial Catalog=COMMONTEMP.SAKAPUR;MDX Compatibility=1"

CONN.Open StrSql

with that I was able to create a connection, but I dont know how to insert data to the view I want after that

former_member689581
Discoverer
0 Kudos

Hi,

Just a quick question on the same topic, 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 mesaure and dimension and data is downloaded in excel as Pivot. Can we do the same with excel VBA so that i can automate the process within excel.

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

What kind of view do you want to insert data into exactly?

If you refer to information models (e.g. analytic or calculation views) you're in bad luck as these are read-only objects.

For general DB processing via SQL you should be able to use any ADODB programming primer that your favorite search engine digs up for you.

So, maybe try some basics first and come back with a more detailed and specific question later on?

- Lars

Former Member
0 Kudos

Thanks for responding.

I need to connect to table, not views from HANA from excel.

however, i havent found working vba code that would allow me to do so. do you have any idea?

EDIT:


i finally got the right code to work:


StrSql = "driver=HDBODBC32;servernode=10.xxx.xxx.30:30015;DATABASE=xxx;uid=xxx;pwd=xxx"


now i just gota figure out how to insert data to the table!! super excited right now


edit again:


so to insert data into the table, the code is:


Set rs = CONN.Execute("insert into xxx.xxx values('xxx','xxx')") 'two column table

lbreddemann
Active Contributor
0 Kudos

Nice work!

JuanCLazaro
Advisor
Advisor
0 Kudos

Hi Wayne, I am trying to achieve more or less the same thing. I would like to insert a button in Excel that takes the value of a cell and inserts that value in a Hana Table.

Could you please elaborate a bit further what you did? I tried the code you pasted in this thread but it doesn't work.

Thanks a lot in advance

Former Member
0 Kudos

Hi Juan Carlos,

Here is an example if you are still looking - the code is below. Not sure how to share the excel example itself so here are the instructions if you want to re-create. But I think the VBA code below shows everything you need if you are fairly familiar with working in Excel/VBA and interacting with databases.

In HANA

  1. Run the HANA script
    1. NB: I assume the objects are in schema EXCEL_DEMO

In Excel

  1. Create table in excel, Sheet1
    1. input fields (starting A3)
    2. select A3:E4
    3. on manu, click: insert/table
    4. check "My table has headers" in pop-up window
  2. Select any cell in the newly create table
    1. select "Design" tab on toolbar (Table Tools group)
    2. in name box (toolbar, upper left) type "TAB_EXCEL_SIMPLE_DEMO"
  3. Add a named cell for filtering
    1. Select cell H3
    2. in "Name Box" (left of formula bar) type in "VAR_CATEGORY_FILTER"

In Excel/VBA

  1. Open VBA in Excel (menu Developer/Visual Basic)
    1. If "Developer" is not visibile on menu, right click ribbon menu, select "Customize Ribbon" and check "Developer"
  2. Double-click on "Sheet1" (best you only have one EXCEL document open)
    1. this way, you can directly address tables inside "[ ]" (see code)
  3. paste in the VBA code
    1. Remember to change the constants at the top to the appropriate values for you HANA server

In Excel (Again) - Adding two buttons

  1. Add "LOAD" button above table
    1. on menu "Developer/Insert/button"
    2. select "LoadSimpleDemo" macro
    3. right-click on button and select "Edit Text" - type "LOAD"
    4. press button, data should be loaded
    5. Type in A, B or C in VAR_CATEGORY_FILTER and press the "LOAD" button again and see how the data is filtered
  2. Add "SAVE" button above table
    1. on menu "Developer/Insert/button"
    2. select "SaveSimpleDemo" macro
    3. right-click on button and select "Edit Text" - type "SAVE"
    4. Add values/line at the bottom of the table (just start typing right below the last line)
      1. NB: leave the ID field empty!
    5. press the SAVE button

This is how the EXCEL looks:

the columns marked in gray should not be manually populated when adding new data

The HANA Script:

-----------------------------

drop table EXCEL_SIMPLE_DEMO;

create column table EXCEL_SIMPLE_DEMO

(

  "ID"        integer     not null GENERATED BY DEFAULT AS IDENTITY

, "CATEGORY"  CHAR(1)     not null

, "NAME"      VARCHAR(20) not null

, "VALUE"     integer     not null

, "CREATED"   timestamp   not null

, PRIMARY KEY ("ID")

, UNIQUE("NAME")

);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('A','ITEM A1',10,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('A','ITEM A2',100,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('A','ITEM A3',1000,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('B','ITEM B1',20,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('B','ITEM B2',200,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('B','ITEM B3',2000,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('C','ITEM C1',40,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('C','ITEM C2',400,current_timestamp);

insert into EXCEL_SIMPLE_DEMO ("CATEGORY","NAME","VALUE","CREATED") values('C','ITEM C3',4000,current_timestamp);

select * from EXCEL_SIMPLE_DEMO;

The VBA code

-----------------------------

Option Explicit

Const sDRIVER As String = "HDBODBC32"

Const sSERVERNODE As String = "10.xxx.xxx.xxx:30015"

Const sUID As String = "EXCEL_DEMO"

Const sPWD As String = "password"

Dim oConn As ADODB.Connection

Private Sub ConnectToDB()

    Dim connStr As String

 

    connStr = ""

    connStr = connStr & "Driver={" & sDRIVER & "};"

    connStr = connStr & "ServerNode=" & sSERVERNODE & ";"

    connStr = connStr & "UID=" & sUID & ";"

    connStr = connStr & "PWD=" & sPWD & ";"

    Set oConn = CreateObject("ADODB.Connection")

    oConn.Open connStr

End Sub

Private Sub DisconnectFromDB()

    oConn.Close

    Set oConn = Nothing

End Sub

Private Sub ExecuteSQL(SQLStr As String)

    oConn.Execute SQLStr

End Sub

Private Sub LoadDataFromTable(sTable As String, obj As ListObject, sFilter As String)

    Dim rs As ADODB.Recordset

    Dim i_ret As Integer

 

    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseClient

 

    If oConn Is Nothing Then

        MsgBox "No open connection!", vbExclamation

        Err.Raise 10001, "modDB.RunProcedureAsSQLStmt", "No open connection to DB"

    End If

    If oConn.State <> 1 Then

        MsgBox "No open connection!", vbExclamation

        Err.Raise 10001, "modDB.RunProcedureAsSQLStmt", "No open connection to DB"

    End If

 

    rs.Open "Select * from EXCEL_DEMO." & sTable & " where " & sFilter, oConn, adOpenStatic

    i_ret = rs.RecordCount

    If obj.ListRows.Count >= 1 Then

      obj.DataBodyRange.Delete

    End If

 

    If rs.RecordCount > 0 Then

        obj.Range(2, 1).CopyFromRecordset rs

    End If

 

    rs.Close

    Set rs = Nothing

End Sub

Public Sub LoadSimpleDemo()

    Dim obj As ListObject

    Dim sFilter As String

 

    sFilter = Range("VAR_CATEGORY_FILTER").Value

    If Len(Trim(sFilter)) = 0 Then

        sFilter = "1=1" 'filter that is always true for all records

    Else

        sFilter = "CATEGORY = '" & sFilter & "'"

    End If

 

    Call ConnectToDB

    Set obj = Sheets("Sheet1").ListObjects("TAB_EXCEL_SIMPLE_DEMO")

    Call LoadDataFromTable("EXCEL_SIMPLE_DEMO", obj, sFilter)

    Call DisconnectFromDB

End Sub

Public Sub SaveSimpleDemo()

    Dim valId As String

    Dim valCategory As String

    Dim valName As String

    Dim valValue As String

 

    Dim row As Range

    Dim SQLStr As String

 

    Call ConnectToDB

    For Each row In [TAB_EXCEL_SIMPLE_DEMO].Rows

        valId = row.Columns(1).Value

        valCategory = row.Columns(2).Value

        valName = row.Columns(3).Value

        valValue = row.Columns(4).Value

     

        If Not IsNumeric(valId) Then

            SQLStr = ""

            SQLStr = SQLStr & "insert into EXCEL_DEMO.EXCEL_SIMPLE_DEMO (""CATEGORY"", ""NAME"", ""VALUE"", ""CREATED"" )"

            SQLStr = SQLStr & " values ('" & valCategory & "', '" & valName & "', " & valValue & ",current_timestamp)"

            ExecuteSQL SQLStr

        End If

    Next

 

    Call DisconnectFromDB

 

    'Reload

    Call LoadSimpleDemo

End Sub