Excelで業務の自動化 その10 SQL処理2

■以前に掲載したものですが修正して再掲載です。　※アドイン版はではありません。

■現役時代に生産管理システムのデータベースからEUC（エンドユーザーコンピューティング）として使うために作った物です。

これでデータ抽出の確認を行い、その抽出マクロを含んだExcelを自動作成し業務の自動化を行っていました。

対象データベースは、SqlServer・Olacle・Access・Excel・CSV・TXTなどが接続可能です。

SQLの検証や勉強などにも使えます、まあこれは、別のフリーのツールを使った方がが便利かも？

＜概要＞

・接続先を指定してSQLを実行し結果を、Excelシートへ取り込む

＜機能＞

・指定したDBのテーブル名、項目名を表示可能、「SQL貼付け」で実行SQL欄へSQL文の作成が出来る

・DBはSqlServer、Oracle、Access、Excel、カンマ区切りファイル、タブ区切りファイルなど

・タブ区切りファイルは取り込み用のschema.iniを作成可能

・SQL文の保存、読み込みが可能

・SQL実行の結果を、 Excelシートをクリアするか、既存への追加か選択可能

・SQL実行の結果の取り込み時に、行数・列数を指定できる。

・SQL実行用のマクロ付のBookの作成可能

　※新たなBook作り、そこにマクロを作成する。※セキュリティ設定が必要

＜SQL処理の画面＞　Excelを立ち上げた時に表示される画面　　　　

＜画面説明＞

＜操作概要＞

　・Excelを開くとすると、次のフォームが表示されます。

    ・テーブル名、項目名

　　＞Accessの場合「権限がない」となる場合あります。

　　　　msysobjects に読み取り権限を与えるて下さい。

　　　　　　※方法はどこかで見つけて下さい。 　

　　＞Excelの場合は、テーブル名にはシート名、項目名はシートの1行目の内容を表示します。

　　＞CSV、TXTは、テーブル名には対象ファイルのフォルダ内のファイル名、 

　　　項目名はシートの1行目の内容を表示します。 

　　＞タブ区切りの場合は、schema.iniファイルが必要となります。 

＜DB接続登録処理＞　　

＜システムの選択内容＞
＞ORACLE：ORACLE製 OraOLEDB.Oracle
＞ MS_ORA：Microsoft製 MSDAORA
＞ MSSQL ：Microsft SQLServer (SQLOLEDB)
＞ ACCESS：MDBファイル　（Microsoft.Jet.OLEDB.4.0）
＞ Access：ACCDBファイル（Microsoft.ACE.OLEDB.12.0）
＞ EXCEL2007：xlsxファイル Excel2007以降 （Microsoft.ACE.OLEDB.12.0/Excel 12.0）
＞ EXCEL2007_U：xlsxファイル　Excel2007以降更新処理（Microsoft.ACE.OLEDB.12.0/Excel 12.0）
＞ EXCEL：xlsファイルExcel2003以前（Microsoft.Jet.OLEDB.4.0/Excel 8.0）
＞ EXCEL_U：xlsファイルExcel2003以前更新処理（Microsoft.Jet.OLEDB.4.0/Excel 8.0）　
＞ CSV：CSVファイル（Microsoft Text Driver (*.txt; *.csv)）
※タブ区切りの場合は、Schema.iniを作成する。
＞ TXT：TXTファイル（Microsoft Text Driver (*.txt; *.csv)）
※タブ区切りの場合は、Schema.iniを作成する。
＞ ORA_INST：Oracle in instantclientのドライバ名を指定
＞ ODBC：ODCBのドライバ名を指定
 ＞ DSN：DSNサーバ名を指定

＜SQL作成方法＞

　　・実行SQLのテキスト画面にSQL文を作成して下さい。

　　・「SQL貼付」のボタンを押して元となる文を作成することが出来ます。

　　・テーブル名・項目名を複数件選択して作成することが出来ます。

　　・テキスト画面が空欄の場合は、「SELECT　・・　FROM　・・」が作られます。

　　・テキスト画面が空欄でない場合は、項目名、テーブル名のみがカーソルの位置に作成されます。

＜テキストの操作＞

　・作成したSQL文は保存、読み込みできます。

＜SQL実行＞

　　・SQLを作成して、「SQL実行」ボタンを押すと処理結果をExcelシートに取り込みます。

　　・別のBookに保存したい時は、新たにExcelを立ち上げて、このSQLフォームの下に表示して置いて下さい。

　

＜SQL実効　例＞

　　SQL例) SELECT UM.商品コード,商品名,数量,納品日,顧客名

　　　　　　FROM T商品マスター SM,T売上伝票明細 UM,T売上伝票 UD

　　　　　　　　　,T顧客マスター KM

　　　   　    WHERE SM.商品コード=UM.商品コード 

                           AND UM.伝票番号=UD.伝票番号

　　　　　　　  AND UD.顧客コード=KM.顧客コード

   >ＳＱＬ貼付けの操作 例

   ・例1　Ｔ品目マスター選択　→　テーブルの　「ＳＱＬ貼付」

   

    ・例2　品目コード,品目名称,取引先コード,単位,Ｔ品目マスター選択　→　項目の「ＳＱＬ貼付」

       

   ・例3　実行ＳＱＬ欄に文字が存在する場合　

　　　 >項目名の備考を選択　→　項目名の　「ＳＱＬ貼付」

　　 　 　※カーソルの位置に「項目」を挿入

                ※実行ＳＱＬ欄に文字が存在する場合は、選択されたテーブルや

                   項目名がカーソルの位置に挿入される。　

 

　＞「SQL貼付」名称使用の例　　

         ※名称を表示させるには、「DB定義」シートのテーブル名、項目名の

            設定する必要があります。

　　　　１、テーブルの名称使用のチェックを付ける

　　　　２、顧客マスタ－をダブルクリックし項目名を表示する

　　　　３、T顧客マスターとT商品マスターを選択する

　　　　４、項目名の顧客コード、顧客名、フリガナを選択

　　　　５、項目名の「SQL貼付」を押す

　　　　　　SQL例）SELECT KM.顧客コード,KM.顧客名,KM.フリガナ

　　　　　　　　　　FROM T顧客マスター KM,T商品マスター SM

　　　　　　※このままでは使えるSQLではないので、WHERE文等の編集を加えて使用する。

＜タブ区切りファイル（TVSファイル）の場合処置＞

　　・TVSファイル）の場合は、対象ファイルと同じ場所に「schema.ini」項目の区切り情報の記録したファイルを配置すとそれに従って読み込みます。

　　・ schema.iniがないと、EXCELのセルに1行が入ります。

　>情報例 

　　CharacterSet= 932 　はShift-JIS です。 UTF-8は65001

　　　※詳細の書式はどこかで確認して下さい。

       [顧客リスト.TXT]　　

      ColNameHeader=True

      CharacterSet=932

      Format=TabDelimited

      Col1=顧客コード Char

      Col2=顧客名 Char

      Col3=フリガナ Char

      Col4=担当部署 Char

      Col5=担当者名 Char

      Col6=郵便番号 Char

      Col7=住所 Char

      Col8=TEL Char

＜schema.iniの作り方＞

　　※必要に応じて修正して下さい。

＜マクロ付のBook作成行う時の前処理（ Excel2010）＞

　　※「VBAプロジェクト　オブジェクト　モデルへのアクセスを信頼する」　にチェックを付ける。

＜作成したマクロを実行（Excel２０１０の場合）＞

　・開発タブ　→　マクロ　→　「SQL実行」を実行する。

　　　　

＜マクロ付Bookの作成　例＞

    ･SQLを作成し稼働可能か確認する。

    ･問題がなければ､｢マクロ作成｣のボタンを押す。

　・新たなExcelが開き、マクロ・標準モジュールを作成します。

　　SQL例＞　SELECT * FROM T顧客マスター WHERE 顧客コード <= '1010' 

　               ※ T顧客マスターより顧客コードが ‘1010’以下のレコードの

                       全項目を表示する。

　    

       

＜作成したマクロの編集例＞

　・マクロ→SQL実行でSQLが実行されデータの抽出が行われるます。

　・作成された標準モジュールのプログラムは変更できます。

　　例）　SQLを選択条件を入力できるようにする。　

　　　　開発タブ　→　VisualBacik の標準モジュール

　

　　・標準モジュールの「＊＊＊＊＊　実行SQL　＊＊＊＊＊」の中を修正する。　　

＜編集したマクロの実行例＞

＜テーブル名、項目名の別名　使用方法＞

　　・「DB定義」シートのテーブル名、項目名の設定する。

　　・名称使用にチェックを付ける

　　　　「ＳＱＬ貼付」ボタンを押すと別名付きのＳＱＬを作成します。

■ダウンロードは 　こちらのページ　をご覧ください。