on 11-14-2014 6:53 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
In Excel
In Excel/VBA
In Excel (Again) - Adding two buttons
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
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.