通过VBA将Excel连接到PostgreSQL

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

是否可以在Excel中进行选择这样的查询，这样我就可以从Excel中查询一个PostgreSQL DB ?

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

如果可能的话，请解释一下如何连接到数据库。我正在看谷歌，但没有找到结果。

3 个解决方案

#1

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

在PostgreSQL中创建描述所需数据的表或视图。

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.

使用VBA中的ODBC或ADO连接连接连接到PostgreSQL。如果使用ODBC，您需要通过odbcad32创建一个DSN。然后在VB中使用DSN，直接连接是不容易的。

See:

看到的:

Using ADO in VBA to connect to PostgreSQL
使用VBA中的ADO连接到PostgreSQL
PostgreSQL Query to Excel Sheet
对Excel表的PostgreSQL查询
http://jackdebear.blogspot.com.au/2011/11/connecting-to-postgres-from-excel.html
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
在远程postgres数据库和excel工作簿之间启用导入/导出流
Does ADO work with ODBC drivers or only OLE DB providers?
ADO使用ODBC驱动程序还是只使用OLE DB提供程序?
How to put query results into a datatable with Excel VBA and ADO?
如何使用Excel VBA和ADO将查询结果放入数据表中?

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

使用Oracle编写的eample更好，但原则是相同的——ODBC/ADO。

#2

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

#3

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

即使是64位Windows, Excel VBA也需要32位的ODBC驱动程序。

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.

通过%windir%\SysWOW64\odbcad32.exe创建DSN。事实上,odbcad32打字。exe指向64位版本，默认情况下无法找到合适的32位驱动程序。

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

来源:https://github.com/windweller/postgresql-excel-addIn

#1