Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Excel VBA connection to Exasol

Blackbeard87
Contributor

Hello,

I want to insert data in Exasol using Excel VBA. Does anyone have a connection template (how to setup the connection) for me?

Best regards

Semih

1 ACCEPTED SOLUTION

Accepted Solutions

Blackbeard87
Contributor

Hi @exa-Aleksandr ,
in the meantime, I have already solved it. As you suggested, I solved it via the odbc driver. Here is the VBA template to connect via ODBC:

Sub QueryEvents()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim wb As Excel.Workbook
    Dim sheet As Excel.Worksheet
    Dim row As Long
    Dim sql As String
    Dim dsn As String
    Dim uid As String
    Dim pwd As String
    Dim col As Long
    row = 0
    
    ' ==================================================================
    ' These must be tailored for your environment
    ' ==================================================================
    dsn = "dsn_name"     'Data source name
    uid = "user_id"      'User ID
    pwd = "password"     'password
    
    ' ==================================================================
    ' Open a connection to the EXASOL server and Insert Data
    ' ==================================================================
    With conn
        .ConnectionString = "DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd
        .ConnectionTimeout = 600
        .CommandTimeout = 600
        .Open
    End With
    
    ' ==================================================================
    ' SQL Query
    ' ==================================================================
    sql = "Your SQL Query"
    ' ==================================================================
    ' Run the query and get RecordSet
    ' ==================================================================
    
    rs.ActiveConnection = conn
    rs.Source = sql
    
    ' ==================================================================
    ' The following code loops through the result set and stores each
    ' row of data in the worksheet. Note that the output columns are
    ' those returned by the above SQL statement. You will need to tailor
    ' the code below such that  it corresponds with the output columns
    ' in your SQL statement.
    ' ==================================================================
    rs.Open
    Set wb = ThisWorkbook
    Set sheet = wb.Worksheets(1)
    Do Until rs.EOF
       row = row + 1
       sheet.Cells(row, 1).Value = rs!ColumnName1
       sheet.Cells(row, 2).Value = rs!ColumnName2
       rs.MoveNext
     Loop
    rs.Close
    
    ' ==================================================================
    ' Close connection
    ' ==================================================================
    conn.Close
End Sub



View solution in original post

3 REPLIES 3

exa-Aleksandr
Team Exasol
Team Exasol

Hi @Blackbeard87 ,

I don't have a ready example, but just an idea.

You can create an ODBC data source pointing to the Exasol DB and then push data there the way you do with other ODBC data sources in VBA.

Please also mind our Data Loading Best Practices https://docs.exasol.com/db/latest/loading_data/best_practice.htm,

avoiding small data inserts, in particular.

Blackbeard87
Contributor

Hi @exa-Aleksandr ,
in the meantime, I have already solved it. As you suggested, I solved it via the odbc driver. Here is the VBA template to connect via ODBC:

Sub QueryEvents()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim wb As Excel.Workbook
    Dim sheet As Excel.Worksheet
    Dim row As Long
    Dim sql As String
    Dim dsn As String
    Dim uid As String
    Dim pwd As String
    Dim col As Long
    row = 0
    
    ' ==================================================================
    ' These must be tailored for your environment
    ' ==================================================================
    dsn = "dsn_name"     'Data source name
    uid = "user_id"      'User ID
    pwd = "password"     'password
    
    ' ==================================================================
    ' Open a connection to the EXASOL server and Insert Data
    ' ==================================================================
    With conn
        .ConnectionString = "DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd
        .ConnectionTimeout = 600
        .CommandTimeout = 600
        .Open
    End With
    
    ' ==================================================================
    ' SQL Query
    ' ==================================================================
    sql = "Your SQL Query"
    ' ==================================================================
    ' Run the query and get RecordSet
    ' ==================================================================
    
    rs.ActiveConnection = conn
    rs.Source = sql
    
    ' ==================================================================
    ' The following code loops through the result set and stores each
    ' row of data in the worksheet. Note that the output columns are
    ' those returned by the above SQL statement. You will need to tailor
    ' the code below such that  it corresponds with the output columns
    ' in your SQL statement.
    ' ==================================================================
    rs.Open
    Set wb = ThisWorkbook
    Set sheet = wb.Worksheets(1)
    Do Until rs.EOF
       row = row + 1
       sheet.Cells(row, 1).Value = rs!ColumnName1
       sheet.Cells(row, 2).Value = rs!ColumnName2
       rs.MoveNext
     Loop
    rs.Close
    
    ' ==================================================================
    ' Close connection
    ' ==================================================================
    conn.Close
End Sub



exa-Aleksandr
Team Exasol
Team Exasol

Hi @Blackbeard87 

Thanks a lot for sharing a code example!