Connecting Excel to PostgreSQL via VBA

Question

Is it possible to make query like SELECT from VBA in Excel, so I can query a PostgreSQL DB from Excel?

If is possible please explain me how to connect to the database. I was looking in Google but found no results.

score 8 · Accepted Answer · edited May 23 '17 at 11:46

Create a table or view in PostgreSQL that describes the data you want.

Use an ODBC or ADO connection from VBA to connect to PostgreSQL. If using ODBC you'll need to create a DSN via odbcad32.exe then use the DSN in VB, it isn't easy to just connect directly.

See:

Better written eample that uses Oracle, but the principles are the same - ODBC/ADO.

subZero · Answer 2 · 2012-11-05T11:30:57.867

Here's some code can use as reference. Hope it helps.

Sub SelectBasic()

        Dim objDb_con
        Dim strSomeValue As String

        Set objDb_con = CreateObject("ADODB.Connection")
        Set Rsdatatype = CreateObject("ADODB.RecordSet")

        glbConnString = Trim(ActiveSheet.Range("B1").Value)
        //Connection string format:Driver={PostgreSQL Unicode};Database=MyDB;server=192.16*.*.**;UID=USERID;Pwd=pasword //comment it
        If glbConnString = "" Then
         MsgBox "Enter the Connection String"
        Else:

        objDb_con.Open glbConnString

        strSql = "select strSomeValue  from SOMETABLE where Something=1"
        Rsdatatype.Open strSql, objDb_con, adOpenKeyset, adLockpessimistic
        If Rsdatatype.EOF = False Then strSomeValue = Rsdatatype.Fields(0).Value
        Rsdatatype.Close

        End If
        objDb_con.Close
    End Sub

score 1 · Answer 3 · answered Jun 20 '17 at 09:51

1

Even for 64-bit Windows, Excel VBA needs the 32-bit ODBC driver.

Create a DSN via %windir%\SysWOW64\odbcad32.exe. Indeed, typing odbcad32.exe points towards the 64-bit version where you can't find the proper 32-bit drivers by default.

Source: https://github.com/windweller/postgresql-excel-addIn

answered Jun 20 '17 at 09:51

Candide

11
2

@Craig's answer (which is good and has more detail than yours) already points out the 32bit issue. Do you really add any value to it with this answer? – Alexander Jun 20 '17 at 10:11
Actually, I **couldn't** connect Excel to PostgreSQL via VBA with Craig's answer, nor with all the other answers on StackOverflow. None mentioned the potential problem with `odbcad32.exe`. It may be obvious to some but not for me and, by the number of unanswered threads on the web, I guess I am not alone. The Excel add-in on Github doesn't work due to a format problem but the wording is excellent to explain the procedure to follow. If you don't want to publish my answer, I will not be mad but several people will encounter the same problem just like me in the future. – Candide Jun 21 '17 at 11:43

Connecting Excel to PostgreSQL via VBA

3 Answers3

Linked