Excel: Prepare to Connect to the Database from VBA

Table of Contents

Here’s the way to connect to the database from Excel VBA. (Excel から VBA を使わずにデータを取得・表示する方法もあります。例: Windows, Excel: データベースから直接値を取得して表示する)

Environment

Excel 2016
Microsoft ActiveX Data Objects 2.8 Library

ADO

First, configure ADO. ADO, ActiveX Data Object, is component to connect to the database. There are many kinds of databases but ADO can connect to every databases in the same way.

Add ADODB at Preferences dialog in Visual Basic Editor.

Open Excel Visual Basic Editor.

In Visual Basic for Applications editor, click “Tool” and “Preferences”.

ダイアログが出るので、 choose latest Microsoft ActiveX Data Objects Library. I’ve chosen version “2.8”.

Click “OK” and back to VBA, you can use ADODB.

Basic

This is basic format to connect to the database.

Sub TestConnection
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "DSN=XXXX;"
cn.Open
cn.Close
Set cn = Nothing
End Sub

Sub TestConnection

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.ConnectionString = "DSN=XXXX;"

cn.Open

cn.Close

Set cn = Nothing

End Sub

The code connects at cn.Open. If you get an error at the line, it doesn’t connect.

You have to write appropriate connection settings according to the database which you want to connect to.

Connection Setting

Write connection setting to connect to the database through ADODB.

When You Have ODBC Configuration

If you have ODBC setting, you can write connection setting easily. The way to create ODBC connection setting in the article “Windows: Setup PostgreSQL ODBC Data Source“.

You can write connection string with the ODBC name, "DSN=odbc_name;". It is not the connection name you created in Excel, it is ODBC name.

cn.ConnectionString = "DSN=odbc_name;"

1	cn.ConnectionString = "DSN=odbc_name;"

Write Connection Setting Directly

Write everything into ConnectionString. Here’s a sample of ConnectionString. For more detail, look at MSDN.

PostgreSQL

Driver={PostgreSQL};Server=hosts;Port=5432;Database=db_name;
Uid=user_name;Pwd=password;

1 2	Driver={PostgreSQL};Server=hosts;Port=5432;Database=db_name; Uid=user_name;Pwd=password;

MySQL

DRIVER={MySQL ODBC 5.3 Driver};SERVER=host;DATABASE=db_name;USER=user_name;PASSWORD=password;OPTION=3;

1	DRIVER={MySQL ODBC 5.3 Driver};SERVER=host;DATABASE=db_name;USER=user_name;PASSWORD=password;OPTION=3;

The Life