LoginSignup
8

More than 5 years have passed since last update.

EXCELでpostgresを接続サンプル

Posted at

ODBC設定
追加ボタンを押す
image

postgresql Unicode選択

image

入力して、保存
image
※postgresql Unicodeがない場合、
http://www.cyber-funnel.com/postgres/index1156.html
上記URLを参照する

ソース:
Const SV = "127.0.0.1"
Const DB = "postgres"
Const PW = "postgres"
Dim CNN As Object
Dim RS As Variant
Set CNN = CreateObject("ADODB.Connection")
CNN.Open "Provider=MSDASQL;Driver=PostgreSQL Unicode;UID=postgres;Port=5432" & ";Server=" & SV & ";Database=" & DB & ";PWD=" & PW
'データ取得
Set RS = CNN.Execute("SELECT * from operation_log")
Dim CN As ADODB.Connection
Dim SQL As String
'接続
'レコードセットを取得
Set RS = New ADODB.Recordset
SQL = "SELECT * from operation_log"
RS.Open SQL, CNN, adOpenKeyset, adLockOptimistic, adCmdText

’テーブルのヘッダーとデータを出力する。
With Worksheets("Sheet1")
.Cells.Clear
RS.MoveFirst
i = 1
.Cells.Clear
Do Until RS.EOF
For j = 0 To RS.Fields.Count - 1
If i = 1 Then .Cells(i, j + 1) = RS(j).Name
If j <> 8 Then
.Cells(i + 1, j + 1) = RS(j).Value
End If
Next j
RS.MoveNext
i = i + 1
Loop
.Columns("A:H").AutoFit
End With
RS.Close
' レコードセット、データベースを閉じる
CNN.Close
Set CNN = Nothing
End Sub

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
8