1

I connect excel to pgsql

1.install driver at:

https://www.postgresql.org/ftp/odbc/versions/msi/

2.Add a System DSN:

enter image description here

This is my code get database:

Public Function setDBConnectionPgsql() As Object
    
    Set setDBConnectionPgsql = CreateObject("ADODB.Connection")
    setDBConnectionPgsql.Open "DSN=PostgreSQL;Server=192.168.1.10;Port=5434;UserId=postgres;Password=123456;Database=test;"
End Function

Private Sub CommandButton1_Click()
    Dim adoCn As Object
    Dim dbRes As Object
    
    Set adoCn = setDBConnectionPgsql()
    Set dbRes = CreateObject("ADODB.Recordset")
   
    dbRes.Open "SELECT * FROM mst_user", adoCn, 1, 2
    Dim iRow As Integer
    iRow = 1
    Do While dbRes.EOF = False
      ActiveSheet.Rows(iRow).Cells(1).Value = dbRes("user_id")
      ActiveSheet.Rows(iRow).Cells(2).Value = dbRes("user_name")
      iRow = iRow + 1
      dbRes.moveNext
    Loop
End Sub

Database is: "理宏"

But Result Excel display :逅・ｮ・

If Text is English, it is ok.

How can get String UTF8 when connect from Excel to Postgresql?

Improve this question
14
  • First, VBA always used Unicode for strings, just like VB6. If you have problems either the data isn't stored using the UTF8 encoding or/and the driver fails to translate the data to Unicode. Commented May 8, 2018 at 7:43
  • Second, why are you using such code at all? Excel already allows you to load data from the Data tab and transform it. There's even a menu choice to load from PostgresSQL, at least in Excel 2016 Commented May 8, 2018 at 7:44
  • refer stackoverflow.com/questions/13230456/… Commented May 8, 2018 at 7:46
  • The last comment doesn't explain anything. Again, why write any code when you can go Data > New Query > From Database > From PostgresSQL Database ? You don't need VBA to read data from a database. You can't even use VBA in an xlsx file, while you can use a data connection just fine Commented May 8, 2018 at 7:47
  • Check Connect to a PostgreSQL database in Excel's documentation. As the docs explain, you need to install Npgsql first Commented May 8, 2018 at 7:51

1 Answer 1

Reset to default
1

The cause: i had install psqlodbc_07. i had fixed by install psqlodbc_10. it is ok.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.