asked on

Excel VBA: export sql & postgresql tables

Hello experts,

The following procedure allows me to export various tables related to a ssms database.

Sub SQLQueryOut2(wsName As String, strSQL, strInitialRange, strServer)

Dim objRS
Dim SQL

Set wb = ActiveWorkbook
Set DestSh = Nothing
On Error Resume Next
Set DestSh = Sheets(wsName)
Set wsConfig = Worksheets("Config")
On Error GoTo 0

    If DestSh Is Nothing Then
        Set DestSh = wb.Sheets.Add(After:=Sheets(wb.Sheets.Count))
        DestSh.Name = wsName
        wsConfig.Select
    Else
        Sheets(wsName).Cells.ClearContents
        wsConfig.Select
    End If
    
Set objRS = CreateObject("ADODB.Recordset")


' ==> to modify
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=password;" & _
"Database=database;"


SQL = strSQL
objRS.Open SQL, objConn

On Error Resume Next
Set rs = objConn.Execute(SQL)
On Error GoTo 0
If rs Is Nothing Then
MsgBox "SQL query reported at row " & rw & "  is not properly set up unable to transfer  data."
Exit Sub
End If

 For Idx = 1 To rs.Fields.Count
        Sheets(wsName).Range(strInitialRange).Offset(0, Idx - 1) = rs.Fields(Idx - 1).Name
    Next

    Sheets(wsName).Range(strInitialRange).Offset(1).CopyFromRecordset rs

Set objRS = Nothing
Set objConn = Nothing

End Sub

Sub SQLQueryoutConfigSheet()

    Dim wsConfig As Worksheet, wsResult As Worksheet
    Set wb = ActiveWorkbook
    Set wsConfig = Worksheets("Config")
    Application.ScreenUpdating = False
    For rw = 2 To wsConfig.Range("A1").CurrentRegion.Rows.Count
        If Range("E" & rw) <> 1 Then
            SQLQueryOut2 Range("A" & rw), Range("B" & rw), Range("C" & rw), Range("D" & rw)
            Range("F" & rw).Value = "Last Run: " & Format(Date, "DD/MM/YY") _
             & "_" & "" & Format(Now, "HH-MM-SS")
        Else
       'MsgBox Range("B" & rw) & " is omitted du to flag reported"
        End If
    Next rw
    MsgBox "Tables reported in Config have been updated"
    wsConfig.Select
End Sub

Open in new window

I would like to adapt this to postgresql to export tables related to a database in postgresql.

Could you advice how should I modify:

' ==> to modify
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=password;" & _
"Database=database;

Open in new window

Here is the information popup displayed in pgadmin.

Thank you for your help.

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you for your comment.

Now open that file with a text editor. Then you can copy the complete connection string.

Could you please provide an example. I don't know the syntax to be used for reference file.

ste5an

D'oh? That's why I wrote that sentence before..

Create a empty text file, rename it's extension to .udl. Open that file and specify a working connection. Use the test button. Close it.

Now open that file with a text editor. Then you can copy the complete connection string.

Opening in the first sentence means the default: double-click.

btw, the primary resource for connection strings: https://connectionstrings.com

Luis Diaz

ASKER

Thank you by working connection what do you mean?

The objective is to know how to transform this part:

' ==> to modify
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=password;" & _
"Database=database;

Open in new window

in postgresql.

I tested by using odbc drive through queries & connections tools available in excel and it works.

but I don't know to adapat objConn

Luis Diaz

ASKER

My attempt without success:

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={PostgreSQL};" & _
"Server=" & strServer & ";" & _
"UID=test;" & _
"PWD=test;" & _
"Port=test;" & _
"Database=test;"

Open in new window

ste5an

Create a empty text file, rename it's extension to .udl. Open that file by double-clicking it..

..and specify a working connection. Use the test button.

Close it.

Now open that file with a text editor.

Then you can copy the complete connection string.

Luis Diaz

ASKER

Tested and I have the following message:

I don't have the option to report port information in any tab.
I also changed the provider to ODBC and I got the following error.

ste5an

Well, did you use the wizard? Did you start on the first tab by selecting the Postgres provider?

Luis Diaz

ASKER

Which driver should I select? I thought it was ODBC as I can access to the DB through it using excel connection feature.

ste5an

You have choosen ADODB in your code, thus using ODBC is not necessary.

Which driver should I select?

The Postgres driver - which you have hopefullly already installed.

Luis Diaz

ASKER

I have ODBC driver installed but I don't know which driver should I install for an ADOBD connection.

ste5an

Install the OLEDB driver..

https://www.postgresql.org/download/products/2-drivers-and-interfaces/

Luis Diaz

ASKER

Thank you. Unable to do it right now. I will keep you informed.

Luis Diaz

ASKER

Prior to installing the driver I want to make sure which should I install?

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you and which version should I installed?

ste5an

Well, I would use the SE version.

But I haven't noticed that this is a commercial driver. So you may use the ODBC driver using the "OLEDB for ODBC driver" provider. But as already said, this comes normally with performance penalty.

Luis Diaz

ASKER

What I don't understand is how can I report the port number when I select "OLEDB for ODBC driver".

Luis Diaz

ASKER

Ok, Now it works after installing the recommended driver.

Now how should I proceed to set up the VBA procedure?

Thank you for your help.

Luis Diaz

ASKER

I got the following information in .udl file:

Provider=PGNP.1;Password=XX;Persist Security Info=True;User ID=XX;Initial Catalog=XX;Data Source=XX;Extended Properties="PORT=XX;SSL=prefer;

Open in new window

Excel VBA: export sql &amp; postgresql tables

Excel VBA: export sql & postgresql tables