06
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--
>>
<<
--
LATEST ENTRY
CATEGORY
ARCHIVE
PROFILE
SEARCH
RECENT COMMENT
  • 【情報】Excel で スクレイピング 【ぶっこ抜き】
    縫部尚登 (06/17)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    ganponfx (05/05)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    まこ (05/05)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    ganponfx (05/04)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    ganponfx (05/04)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    ganponfx (05/04)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    ganponfx (05/03)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    まこ (05/03)
  • 【QUICKFIX】 FX自動売買への道 18 【通貨ペアの取得(SecurityListRequest)】
    ganponfx (05/03)
  • エクセルファイルのパスワードを忘れたら・・・
    里奈 (09/09)
MOBILE
qrcode
OTHERS
<< 【puppyで】Asterisk + IAXmodem + Hylafax + ひかり電話 + Puppy 4.3.1 jp 【FAX送受信】 | top | SUN >> ORACLE :: Virtual Box >>
スポンサーサイト

一定期間更新がないため広告を表示しています

スポンサードリンク | - | | - | - |
【カンタン】Excel + MySQL マクロでSELECT編 【データベース接続】
Excel から MySQL へ直接接続して、任意の SQL を実行してデータを取り込む方法です
とりあえず使えるSQLは SELECT 系のみ
更新系はまたそのうちに

前回の続きなので、ODBC環境は構築済みの前提で書きます

前回のブログは コチラ です。
 
まずは Excel から MySQL に接続する設定を 「新しいマクロの記録」 で記録
操作をそのままマクロに記録してくれるので、この機能を利用してデータベースに接続するマクロの 「 ひな形 」 を作ります。

その後、自動生成されたマクロを改造して、任意のSQLを実行できるようにしてみます。


今回作ったExcelファイルをとりあえずコチラに置いておきます。
ファイル名は Book1.zip です
http://drop.io/o8pckmr




手順1 : マクロの自動生成

1−1: まずはExcelからMySQLに接続する設定をマクロにするため ツール → マクロ → 新しいマクロの記録 を実行



1−2: マクロの名前はそのままで [OK]ボタンを



1−3: データを MySQL から取り込むため データ → 外部データの取り込み → データの取り込み  を実行



1−4: 開いた画面で [ 新しいソース ] ボタンを押して



1−5: 開いたデータ接続ウィザードで ODBC DSN を選んで



1−6: 前回の手順で作成した MySQL の設定を選択



1−7: MySQL への接続設定が出てくるので、ユーザ名とパスワードなどを入力する。
その後、ホントに接続できるか [ TEST ] ボタンでちゃんと確認しておきます。



1−8: ちゃんと接続できればこの 「OK」画面が出てきます。
出ない場合は、ユーザやパスワードの設定が間違ってるか・・MySQLが起動しているか・・ この段階でちゃんと確認しといてください。



1−9: 接続設定の後はデータの選択画面が開きます。
とりあえずマクロの自動生成が目的なので、適当に選んで[次へ]ボタンを




1−10: 開いた画面で 「パスワードをファイルに保存」 のチェックを選択
安全性に・・・って警告が出ますが、とりあえず 「はい」 で





1−11: パスワードの保存にチェックがあることを確認して 「完了」 ボタンを



1−12: 次の画面ではそのまま[開く]ボタンを



1−13: インポート先は 「新規ワークシート」 を選んで[OK]ボタンを




1−14: データの流し込みに成功したら、マクロの記録を終了します。





手順2 : マクロの改造1 ( データの取り込み )

2−1: まずはマクロの表示 ツール → マクロ → Visual Basic Editor  を




2−2: 開いた画面の左側にあるツリーから自動記録したマクロを選んで開く
( Module1 ってやつのハズ・・ )




2−3: マクロ内容を以下のように編集 ・・ とりあえずコピーペーストでも
マクロの仕様としてはこんな感じ
1 : データの取り込み先はシート
2 : シートの名前はとりあえず 「 取り込みデータ 」 に固定
3 : 同じ名前のシートがあれば、シート内容を上書き
4 : 同じ名前のシートが無ければ、シートを追加
5 : ユーザ名やパスワード、接続先はコントロール用のシートから取得する
6 : データ取り込みの実行はボタンで開始

編集後のマクロ内容:
Sub Macro1(ctrl_sheet As String)
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: 定数の宣言
    Const USERCELL = "USER"
    Const PASSCELL = "PASS"
    Const SERVERCELL = "SERVER"
    Const DATACELL = "取り込みデータ"
    
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: 変数の宣言
    Dim user As String
    Dim pass As String
    Dim server As String
    
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: コントロールシートから接続情報取り込み
    On Error GoTo ERR_CTRLSHEET             ':: コントロールシートの存在確認
    With Worksheets(ctrl_sheet)
       
        On Error GoTo ERR_GETUSER           ':: 接続ユーザ名の取得
        user = .Range(USERCELL).Value2
       
        On Error GoTo ERR_GETPASS           ':: 接続パスワードの取得
        pass = .Range(PASSCELL).Value2
       
        On Error GoTo ERR_GETSERVER         ':: 接続サーバ名の取得
        server = .Range(SERVERCELL).Value2
   
    End With
    On Error Resume Next
    
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: 取込先シートの存在確認/追加
    Err.Clear
    Worksheets(DATACELL).Select
    If Err.Number <> 0 Then
        Worksheets.Add
        ActiveSheet.Name = DATACELL
    End If
    
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: MySQLからデータの取り込み
    On Error GoTo ERR_QUERY                 ':: エラー発生時処理
    With ActiveSheet.QueryTables.Add( _
        Connection:="ODBC;DSN=MySQL;" _
            & "SERVER=" & server & ";" _
            & "UID=" & user & ";" _
            & "PASS=" & pass & ";" _
            & "DATABASE=INFORMATION_SCHEMA;" _
            & "PORT=3306", _
        Destination:=Range("A1") _
        )
        .CommandText = Array("select * from INFORMATION_SCHEMA.TABLES")
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
'        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
'        .SavePassword = True
'        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh
    End With

Exit Sub

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':: エラー
ERR_CTRLSHEET:
    MsgBox ("シート [ " & ctrl_sheet & " ] が見つかりませんでした。")
    End

ERR_GETUSER:
    MsgBox ("シート [ " & ctrl_sheet & " ] に" _
          & "ユーザ名セル [ " & USERCELL & " ] が見つかりませんでした。")
    End

ERR_GETPASS:
    MsgBox ("シート [ " & ctrl_sheet & " ] に" _
          & "パスワードセル [ " & PASSCELL & " ] が見つかりませんでした。")
    End

ERR_GETSERVER:
    MsgBox ("シート [ " & ctrl_sheet & " ] に" _
          & "接続先サーバセル [ " & SERVERCELL & " ] が見つかりませんでした。")
    End

ERR_QUERY:
    MsgBox ("データベースの取り込み時にエラーが発生しました。" & vbCrLf _
            & "エラー番号:" & Err.Number & vbCrLf _
            & "エラー内容:" & Err.Description)
    End

End Sub
赤字 部分については、自動生成されたコードの通りにしておくと・・
・・・少し幸せになれると思います



2−4: マクロの編集はひとまずおいといて、エクセルの sheet1 シートをデータベースへの接続用ユーザ、パスワードなどを制御するコントロールシートにします
マクロの画面を閉じて、sheet1 を開いて、
ユーザ名用、パスワード用、接続サーバ名などのセルを登録
a) B2 セルを選択 → セルに名前を付ける [SERVER]
b) B3 セルを選択 → セルに名前を付ける [USER]
c) B4 セルを選択 → セルに名前を付ける [PASS]









2−5: 見やすいように加工 着色して罫線も んで、接続情報の入力




2−6: データベース取り込みのボタンを追加するために、VBAツールバーを表示
表示 → ツールバー → Visual Basic




2−7: 表示したツールバーの 「スパナアイコン」 でコントロールツールボックスを表示





2−8: コマンドボタン で ボタンを追加






2−9: ボタンを右クリックして プロパティ を表示




2−10: で ボタン名 を編集 とりあえず 「 取込実行 」




2−11: 追加したボタンを右クリックして、今度は コードの表示 を選択





2−12: 開いた画面で以下のコードを入力
 

  Call Macro1(ActiveSheet.Name) 
 





2−13: ここまでできたら、ひとまず開いた画面を全部閉じて、ファイルを保存




2−14: 保存したファイルを 「マクロを有効にする」 で開く



 ・ ・ ・ マクロの実行にはオプションの設定が必要でした 
上の画面が開かない、マクロが実行できない場合は ツール → オプション のオプション画面を開いて 「 セキュリティ 」 のタブで 「 マクロのセキュリティ 」 を



んで、セキュリティレベルを [ 中 ] にしてからエクセルを開き直してください。




2−15: マクロを有効にして開けたら、作った [ 取込実行 ] のボタンを押して動作確認を





※ うまく動けば、データが取り込めるはずです
※ データが取り込めない場合は・・ デバッグモードで1行ずつ見てみると良いかも
※ デバッグモードのやり方
1) ALT+F11 ボタンを押してソース画面を開いて、ボタンのソース部分を表示


2) Call の部分にカーソルを合わせて F8 ボタンを押すと、1行ずつ実行できます
どんどん実行して、データが取り込めない原因を見てみてください
・ マウスを当てると、変数の中身も表示できます






手順3 : マクロの改造2 (SQLでデータの取込)

3−1: 手順2までに作ったエクセルを開く




3−2: 任意のSQLを編集できるようにコントロールシートに項目を追加する
追加するのは以下の3つ
1)接続先のデータベースの指定欄
・ B5 セルを選択 → セルに名前を付ける [DB]




2)取り込んだデータを流し込むシート名の指定欄
・ B7 セルを選択 → セルに名前を付ける [SHEET]




3)実行するSQL文を指定する欄
・ C7 セルを選択 → セルに名前を付ける [SQL]




3−3: 追加した項目に設定
接続先データベース、取り込んだデータを流し込むシート名、実行するSQL文を入力
接続先データベースは接続できる実在する名前を
実行するSQLはエラーのない単純なものでまずはテストするのが良いかと思います




3−4: マクロをさらに改造。 以下のように編集してください 
追加した指定欄を読み込むようにして、ついでに冗長な所を関数化しました

編集後のマクロ内容:
Sub Macro1(ctrl_sheet As String)
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: 変数の宣言
    Dim data As Object                                  ':: 連想配列
    Dim v As Variant                                    ':: ループ用
   
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: コントロールシートから接続情報取り込み
    Set data = CreateObject("Scripting.Dictionary")     ':: 連想配列の定義
    For Each v In Array( _
          "USER" _
        , "PASS" _
        , "SERVER" _
        , "DB" _
        , "SHEET" _
        , "SQL" _
    )
        data.Add v, GetCtrlValue(ctrl_sheet, v)
        Debug.Print v & " = " & data(v)
    Next v
   
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: 取込先シートの存在確認/追加
    On Error Resume Next
    Err.Clear
    Worksheets(data("SHEET")).Select
    If Err.Number <> 0 Then
        Worksheets.Add
        ActiveSheet.Name = data("SHEET")
    End If
   
    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: MySQLからデータの取り込み
    On Error GoTo ERR_QUERY                         ':: エラー発生時処理
    With ActiveSheet.QueryTables.Add( _
        Connection:="ODBC;DSN=MySQL;" _
            & "SERVER=" & data("SERVER") & ";" _
            & "UID=" & data("USER") & ";" _
            & "PASS=" & data("PASS") & ";" _
            & "DATABASE=" & data("DB") & ";" _
            & "PORT=3306", _
        Destination:=Range("A1") _
        )
        .CommandText = data("SQL")
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
'        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
'        .SavePassword = True
'        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh                                    ':: データ取得 実行!
    End With

Exit Sub

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':: エラー
ERR_QUERY:
    MsgBox ("データベースの取り込み時にエラーが発生しました。" & vbCrLf _
            & "エラー番号:" & Err.Number & vbCrLf _
            & "エラー内容:" & Err.Description)
    Worksheets(ctrl_sheet).Activate
    End

End Sub


Function GetCtrlValue(ctrl_sheet As String, cell As Variant) As String

    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    ':: コントロールシートから、指定セルの内容を取得
    On Error GoTo ERR_CTRLSHEET             ':: コントロールシートの存在確認
    With Worksheets(ctrl_sheet)
        On Error GoTo ERR_GET               ':: セルの取得エラー処理
        GetCtrlValue = .Range(cell).Value2  ':: セルの取得
    End With
   
    Exit Function
   
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':: エラー
ERR_CTRLSHEET:
    MsgBox ("シート [ " & ctrl_sheet & " ] が見つかりませんでした。")
    End

ERR_GET:
    MsgBox ("シート [ " & ctrl_sheet & " ] に" _
          & "セル [ " & cell & " ] が見つかりませんでした。")
    Workbooks(ctrl_sheet).Activate
    End
   
End Function


3−5: とりあえず保存
マクロを実行すると・・そのままExcelがお亡くなりになることもあるので、とりあえず保存しといてください。



3−6: 「 取込開始 」 ボタンを押して、ちゃーんとデータが取り込めることを確認









うまーく動かなかった場合は手順2の最後と同じように デバッグ してみてください。
あと、SQL文が正しいことを phpmyadmin とかで確認しておくと、SQLが悪いのか、マクロが悪いのか、原因の切り分けになると思います。


 

以上

任意の SQL でMySQLからデータを取り出すマクロの完成です。

ボタンを押すだけで、カンタンにデータの取込ができるようになりました。


SQLの勉強にも良いかと思いますので
いろいろ実験してみてください。



SQLの基本:
http://www.accessclub.jp/sql/02.html

パラメータ:
http://okwave.jp/qa/q4372015.html

 
まこ | 開発 | 16:19 | comments(1) | trackbacks(0) |
スポンサーサイト
スポンサードリンク | - | 16:19 | - | - |
Comment
見えんけどある。

ちゃーんとあるよ。

この世には目に見えんもんがたくさんあるけん。

posted by ,2010/09/04 1:53 AM










Trackback
URL: