Skip to Content
avatar image
Former Member

Connecting HANA with Excel through VBA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 16, 2014 at 12:11 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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