Excel: Prepare to Connect to the Database from VBA

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


  • Excel 2016
  • Microsoft ActiveX Data Objects 2.8 Library


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.


This is basic format to connect to the database.

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.

Write Connection Setting Directly

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