Connecting Excel to PostgreSQL via VBA

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:

• Using ADO in VBA to connect to PostgreSQL
• PostgreSQL Query to Excel Sheet
• http://jackdebear.blogspot.com.au/2011/11/connecting-to-postgres-from-excel.html
• Enabling import/export flows between a remote postgres database and excel workbooks
• Does ADO work with ODBC drivers or only OLE DB providers?
• How to put query results into a datatable with Excel VBA and ADO?

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

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

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