スポンサーリンク

050-VBA

EXCELのVBAを使ってDB【Oracle】に接続してSQLを叩く方法!

へっぽこヘタレシステム管理者の管理人です。

さて・・・

長年、【Oracle】等のDBに接続してSQLを叩く場合は・・・

EXCELでのやり方が分からず・・・

ず~っとMS-ACCESSを使っていました。

とくにこれまで、不便を感じたこともなく・・・

MS-ACCESSで十分だと思っていたのですが・・・

昨年ごろから、AI-OCRやRPAを職場でも使いだすようになると・・・

現場の職員に操作してもらう場面も結構出てきます。

場合によっては、DBからデータを抽出してもらう作業も出てくるのですが・・・

そういった場合に、ライセンスの関係でMS-ACCESSが無く、全てのPCに標準でインストールされているEXCELを使わざるをえない場合が出てきました。

そこで、一念発起して10年以上諦めていた、EXCELから【Oracle】に接続して、SQLを叩く方法がようやく分かったので忘備録として掲載します。

Visual Basicのコード

コードは次のとおりです。

マクロ1に設定した場合です。

なお【Oracle】の場合は、クライアントにOracleのクライアントがインストールされている必要がありますので注意してください。

また、エクセルの【Visual Basic Editor】の【ツール】・【参照設定】で・・・

【Microsoft ActiveX Data Object 2.8 Library】を追加しておく必要があります。

Sub Macro1()
'
' Macro1 Macro
'
    Dim SQL As String
    Dim i As Long
    Dim row As Long

    Const Provider = "OraOLEDB.Oracle" 'Provider
    Const DATA_SOURCE = "DBのソース名"           'Data Source
    Const USER_ID = "ユーザーID"               'userid
    Const Password = "パスワード"              'password

    SQL = SQL & " " & "select * from syainTB" 'SQL文
    
    Dim conn As New ADODB.Connection
        
    conn.ConnectionString = _
                    "Provider=" & Provider & ";" & _
                    "Data Source=" & DATA_SOURCE & ";" & _
                    "USER ID=" & USER_ID & ";" & _
                    "Password=" & Password & ";"
    conn.Open
    
    Dim rs As New ADODB.Recordset

    rs.Source = SQL
    rs.ActiveConnection = conn
    rs.Open
    
    With Worksheets("sheet1")
        .Cells.Clear
        
        '列名の表示
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1) = rs(i).Name
        Next i

        '値の表示
        row = 1
        Do Until rs.EOF
            For i = 0 To rs.Fields.Count - 1
                .Cells(row + 1, i + 1) = rs(i).Value
            Next i
            rs.MoveNext
            row = row + 1
        Loop
    End With
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    Application.Calculate
    
End Sub

スポンサーリンク

エクセルのシートからSQLに設定する値を参照できる

コード内のSQLに設定する値をシートから参照することもできます。

コードの書き方は次のとおりです。

    SQL = SQL & " " & "select * from syainTB" 'SQL文
    SQL = SQL & " " & "where"
    SQL = SQL & " " & "syainTB.カラム名 = '" & Worksheets!シート名.Cells(*,*) & "'" '条件設定

ブログランキングにご協力ください!
ブログランキング・にほんブログ村へ

エクセルの表から選択してデータ抽出も可能

エクセルの表を使って任意の行のデータを抽出することも可能です。

コードの書き方は次のとおりです。

    SQL = SQL & " " & "select * from syainTB" 'SQL文
    SQL = SQL & " " & "where"

    Dim rowcounta As Long '繰り返し回数の変数設定
    rowcounta = 2 '2行目から開始

    Do While rowcounta < 502
 '何回繰り返すか(行が空白であれば問題ない)
    
    SQL = SQL & " " & Worksheets!シート名.Cells(rowcounta, 列番号) 'シートに条件式を設定しておく
    rowcounta = rowcounta + 1 'カウントアップ
    
    Loop

なお・・・

参照するエクルの行には、SQL文に設定する文字列を関数を使って設定しておく。

二行目以降は先頭に【OR】を設定する。

エクセルの設定例
1行目     = "syainTB.name = '" & 【セル名】 & "'"
2行目以降  = "or syainTB.name = '" & 【セル名】 & "'"

以上です。

ブログ開設に必要なドメイン取得、サーバーレンタル、ASPの登録等は、こちらのサイトから!

スポンサーリンク
スポンサーリンク

-050-VBA

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


関連記事

board-pic1

RPA【WinActor】VSパンチャー!どっちの入力が早いか? PDF化AI-OCRによる電子化に要する時間を含めてもRPAの勝ち!

さて、特別定額給付金【10万円】の支給事務で、日々サビ残に明け暮れる管理人です。 サビ残も130時間越え・・・ 過労死ラインを軽く超えています。 毎日RPA【WinActor】を動かして、申請書をひた …

元祖国内RPAの【RocketMouse Pro】を試す!画像認識クリックはかなりの精度!

へっぽこヘタレ管理者の管理人です。 さて、高価な【WinActor】のライセンス問題があり・・・ 簡単な作業を安価に利用できるツールとして、いろいろと試してみていますが、 完全無料RPAの【マクロマン …

WinActorとマクロマンの画像マッチングでのクリック精度を比較してみた!やはり無料RPAでは難しいのか?

へっぽこヘタレシステム管理者の管理人です。 さて、前回に続き、無料RPA【マクロマン】を使ってみます。 完全無料RPA【マクロマン】についてこちらの記事を 今回は、画像マッチングでボタンを認識してクリ …

MVNOのSIMでスマホからテザリングでヤフーカーナビを使う!結構な頻度で通信が不安定でナビが動かなくなる!

へっぽこへたれ貧乏ライダーの管理人です。 以前使っていた、バイク用のポータブルナビが・・・ スペースの関係で【Z H2】には取付できなかったので・・・ 悩んだ挙句に、古いスマホをテザリングで接続してヤ …

Accessでユーザー定義関数を作る!基本はエクセルの場合と同じ方法でOK!

エクセル【EXCEL】でユーザー定義関数は、よく使いますが・・・ そういえば、アクセス【ACCESS】では、使った事がないなと思い・・・ 作り方を整理してみました。 基本はEXCELの場合と同じ 試し …

スポンサーリンク
スポンサーリンク
スポンサーリンク