2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

VBA SQL接続してテーブル取得

Posted at

VBAエディタ→「ツール」→「参照設定」
Microsoft ActiveX Dta Objects X.X Library

' 変数の宣言を強制
Option Explicit

' SQL抜き出し
Sub SOL Extract()

    Dim Provider As String:Provider="SOLOLEDB"    ' 固定
    Dim Server As String:Server="サーバー名"    ' サーバー名
    Dim DataBase As String:DataBase="データベース名"    ' データベース名
    Dim DataTable As String:DataTable="テーブル名"    ' テーブル名
    Dim UserID As String:UserID="viewer(ユーザーID)"    ' ユーザーID
    Dim Pass As String:Pass="パスワード"    ' パスワード
    Dim strSOL As String    ' SQL文
    Dim cn As New ADODB.Connection    ' 接続
    Dim rs As New ADODB.Recordset    ' データ取得、貼り付け

    ' 接続
    cn.ConnectionString="Provider=" & Provider _
                        & ";Data Source=" & Server _
                        & ";Initial Catalog" & DataBase _
                        & ";UID=" & UserID _
                        & ";PWD=" & Pass                
    cn.Open

    ' SQL文を記載
    strSOL="select * from " & DataTable

    ' SQL実行
    rs.Open strSQL, cn

    ' データ先頭へ移動
    rs.MoveFirst

    ' データをA1へ貼り付け
    ActiveSheet.Range("A1").CopyFromRecordset rs

    ' 接続解除
    If Not rs Is Nothing Then
        If rs.State adStateOpen Then
            rs. Close 
        End If
        Set rs Nothing
    End If

    If Not cn Is Nothing Then
        If cn.State adStateOpen Then
            cn. Close
        End If 
        Set cn Nothing
    End If

End Sub

2
4
0

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
2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?