DB Excel

データベースをエクセルで操作する-1.Mysql

14.11.07

前提:ローカルPCにXAMPPがインストールされ、ApacheとMySqlが、稼働していること
特徴:ローカルPCのみで使用可能
方法:「ADO」ODBCドライバーを使う

接続と抽出

A、エクセルとMySqlの接続を可能すため、「MySQL Connector/ODBC *.*」をダウンロードする。

 
 「MySQL Connector/ODBC 5.1」は、Webで表示した場合に、日本語の文字化け問題があります(エクセルでは、支障ありませんが・・・)。「MySQL Connector/ODBC 5.3」をダウンロードすることお薦めします。
  ダウンロード⇒Download Connector/ODBC

1 2 3 4 5 6 7

B、「phpMyAdmin」でデータベースとテーブルを作成
1、データベース:test、テーブル:goods を作る。エクセルが「sjis」仕様のため、照合順序は、「sjis」にする。
2、データを数件、入力しておきます。

1、テーブルの構造
1
2、テーブルの表示
2

C、Visual Basicの設定
1、エクセルを開き、開発タブのVisual Basicをクリック
2、ツールタブの参照設定を選択
3、「Microsoft ActiveX Data Objects *.* Library」の最新バージョンを選び、OK
4、挿入タブの標準モジュールを選択
5、標準モジュールが挿入される

1
1
2
2
3
3
4
4
5
5

D、標準モジュールに下記のコードを記述する
a.Option Explicit
b.接続、切断
 Mysqlに設定しているものをいれます。サンプルでは、
 ”SERVER=localhost;”   は、サーバー名「localhost」
 ”DATABASE=test;”     は、データベース名「test」
 ”UID=root; OPTION=3;”  は、ユーザーID「root」、OPTION=3は、そのまま
 ”PASSWORD=*****”     は、パスワード「*****」
c.全件抽出
 Call a02_InODBCは、接続コードを呼び出して実行します。
 Call a09_OutODBCは、切断コードを呼び出して実行します。

一旦、モジュールを閉じます。

a.Option Explicit/モジュールのTOP

b.接続、切断/モジュールの適当な場所

c.全件抽出/モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

E、実行ボタンの作成
1、開発タブ⇒挿入⇒「ボタンマーク」アイコン
2、シート上で左クリックしたまま広げると、2 のような画面がでてくる。
 「b1_mysql抽出goods」を選んでOKをクリック
3、出てきたボタンに適当な名前を付けて整える。
4、マクロの実行
 ボタンを押すと、「goods」テーブルの全件が、指定した場所に抽出されます。

1
1
2
2
4
4

category: 4.PC , DB Excel comment: (0)

データベースをエクセルで操作する-2.Access他

14.11.05

バージョンアップ後の接続コード

A、Access(アクセス)
a、拡張子が、「.mdb」の場合
 「cn.Open “Driver={Microsoft Access Driver (*.mdb)};” & _
  ”DBQ=フルパス;”」
b、拡張子が、「.mdb」「.accdb」の場合
 「cn.Open “Driver={Microsoft Access Driver (*.mdb, *.accdb)};” & _
  ”DBQ=フルパス;”」

b により「.accdb」ファイルが、接続できるようになります。
 ただし、「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」をダウンロードしないと接続できない場合がある。(私のPCはそうでした。)
   ダウンロード⇒[・・・再頒布可能コンポーネント ]

B、Excel(エクセル)
a、拡張子が、「.xls」の場合
 「cn.Open “Driver={Microsoft Excel Driver (*.xls)};” & _
  ”DBQ=フルパス;”」
b、拡張子が、「.xls」「.xlsm」「.xlsx」「.xlsb」の場合
 「cn.Open “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};” & _
  ”DBQ=フルパス;”」

 b により、マクロ有効book「.xlsm」ファイルが、接続できるようになります。
 ただし、A、Accessと同様に「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」をダウンロードしないと接続できない場合がある。

C、Mysql
a、「MySQL Connector/ODBC 5.1」の場合
 cn.Open “DRIVER={MySQL ODBC 5.1 Driver};” _

b、「MySQL Connector/ODBC 5.3」の場合
 cn.Open “DRIVER={MySQL ODBC 5.3 Unicode Driver};” _

 DRIVER の記載に少し変更がある。

接続と切断(Mysql、Acccess、CSV、Excel)

 「.accdb」、「.xlsm」「.xlsx」「.xlsb」でも通用するようにコードを整理しました。最低これだけあれば、事足りるようです。もちろん「.mdb」、「.xls」もOKです。

Option Explicit/モジュールのTOP(既に記述している場合は不要)

接続と切断/モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

Visual Basicの設定

1、エクセルを開き、開発タブのVisual Basicをクリック
2、ツールタブの参照設定を選択
3、「Microsoft ActiveX Data Objects *.* Library」の最新バージョンを選び、OK
4、挿入タブの標準モジュールを選択
5、標準モジュールが挿入される

1
1
2
2
3
3
4
4
5
5

抽出、読込み

A、Access(アクセス)の場合
1、データベース「pl2.accdb」にテーブル「PL」を作成し、
2、2014.10月~2015.3月のデータを登録しています。
3、モジュールに下記のコードを記述。
4、ボタンに関連付け、4 のように整える。
5、ボタンを押すと、6ケ月分の合計が抽出される。

1、構造
1
2、6ケ月分登録しました
2
4、上記のように整える
4
5、2014.5 から 2015.3 を集計
5

3、抽出/モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

B、Excelファイルのデータを読み込む
1、「会計ソフト.xlsm」に某会計ソフトからデータを読込ませていると仮定します。
2、モジュールに下記のコードを記述。
3、ボタンに関連付け、整えた後、ボタンを押すとデータが読込まれます。

1
1
3
3

2、読込み/モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

C、CSVファイルのデータを読み込む
1、「Numbers3.csv」からデータを読込ませます。
2、モジュールに下記のコードを記述。
3、ボタンに関連付け、整えた後、ボタンを押すとデータが読込まれます。

1
1
2
3

2、読込み/モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

category: 4.PC , DB Excel comment: (0)

データベースをエクセルで操作する-3.登録

14.11.04

 データベースの「接続」「切断」以外は、各データベース共通のコードとなります。具体的には、

Mysqlであれば、
 Call a02_InODBC
 Call a09_OutODBC
Accesであれば、
 Call a01_InODBC
 Call a09_OutODBC
udlファイルを使うのでであれば、
 Call a05_InODBC
 Call a09_OutODBC   で、コードを呼び出し、接続と切断を行います。

以下、Mysqlに接続するものとして、説明します。

登録 (エクセルシートの2箇所から登録)

1、データベース「test」に、テーブル「uriage2」を作成、
2、データは、何も入っていません。
3、モジュールに下記のコードを記述
4、ボタンを作成し、「d2_mysql登録uriage2」に関連付ける。(ボタンの作成方法は、「1.Mysql」参照)
  さらに、シートを4 のように整える。
5、ボタンを押して登録
6、「phpMyAdmin」で調べると、データが登録されている。

1、「key1」には、主キーを設定
1
2、表示
2
4、上記のような配置で整える
4
5、ボタンを押して登録
5
6、表示
6

3、登録/モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

category: 4.PC , DB Excel comment: (0)

データベースをエクセルで操作する-4.更新、削除

14.11.04

更新

A、方法1(一旦削除し、再登録する。)

1、「3、登録」で作成した、データベース「test」テーブル「uriage2」を利用します。
2、モジュールに下記のコードを記述、
3、ngp=20141001、kikai=2 を抽出した状態 (抽出、更新ともに、ボタンに関連付けている)
4、imgのように修正し、更新ボタンを押す
5、「phpMyAdmin」で調べると、データが更新されている。

3
3
4
4
5
5

2、モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

更新

B、方法2(セルごとに更新する。)

1、データベース:test、テーブル:goods を使用
2、データの内容
3、抽出用のコードを記述し、3 のようにマクロを実行(確認だけならこれでOK)
 ちなみに、SUBプロージャー内で「F8」キーを押すと、マクロが実行される過程が確認できます。
4、エクセルのデータを修正
5、更新用のコードを記述し、5 のようにマクロを実行
6、phpMyAdminで確認すると更新されています。

1
1
2
2
3
3
4
4
5
5
6
6

モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

削除

1、更新の「B、方法2」で使ったテーブルとシートを利用します。
2、エクセルのシートをを整える。
3、コードを記述し、マクロを実行
4、phpMyAdminで確認すると削除されています。

2
2
3
3
4
4

モジュールの適当な場所

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

category: 4.PC , DB Excel comment: (0)

データベースをエクセルで操作する-5.udlファイル

14.11.04

 下記は、Access(アクセス)だけで通用する登録、更新の方法です。登録と更新が、同時に行えます。
 「Microsoft Access Driver」で接続しても通用しないので、「.udl」ファイルを使って、接続します。
 「.mdb」であれば、「Jet 4.0 OLE DB Provider」
 「.accdb」であれば、「Microsoft 15.0 Access Database Engine OLE DB Provider」を使います。
 めんどうなコードを書かなくても、結果オーライで接続可能。 ちなみに、Mysqlでも、「.udl」ファイルで「Microsoft OLE DB Provider for ODBC Drivers」を設定することにより、接続、前述の抽出、登録その他できます。しかし、下記の方法ついては不可能でした。(1-2 インデックスの設定他、Mysqlでは、サポートされていない記述があるようです。) 

更新と新規登録

C、方法3(更新と登録を同時に行う)
 「.mdb」でも、手順は同じ。「.accdb」で説明します。
a、Accessの設定
1、とにかく、データベース「pl2.accdb」にテーブル「PL」を作り、データを入れてます。
2、デザインタブのインデックスをクリック、インデックス名をメモしておきます。

1
1
2、インデックス名は「primarykey」
2

b、「.udl」ファイルの作成
1、テキストエディタなどを開き、空のまま「pl2.txt」と名前を付けて保存
2、保存した「pl2.txt」の拡張子を「pl2.udl」に変更
3、「pl2.udl」を開き、プロバイダータブの「Microsoft 15.0 Access Database Engine OLE DB Provider」を選択し、「次へ」ボタンを押す
  (「.mdb」であれば、「Jet 4.0 OLE DB Provider」)
4、データソースに「pl2.accdb」までのフルパスを入力し、「接続のテスト」ボタンを押す
5、接続に成功したら、OK→OK で終了

2、かまわず「はい」で変更
2
3
3
4
4
5
5

c、登録と変更
1、モジュールに、下記のコードを記述
  インデックスの設定には、a で調べた「primarykey」を入れる。
2、エクセルにデータを抽出
3、3 のようにデータに変更を加え、登録
4、「phpMyAdmin」で調べると、データが登録および変更されている。

2
2
3、色付き部分を修正
3
4
4

1-1、抽出

1-2、登録と更新

※モジュールにコピペすると「半角スペース」が、「?」に文字化けする場合がある、修正してご使用ください。

Mysqlの「.udl」ファイルの設定

1、「・・・・.udl」を開き、プロバイダータブの「Microsoft OLE DB Provider for ODBC Drivers」を選択し、「次へ」ボタンを押す
2、2 のようにデータベース名「test」を入力し、「ビルド」ボタンを押す
3、「test dsn」を選択し、OK
4、4 のように入力し、「test」ボタンを押す
5、接続に成功したら、OK→OK
6、6 、7 のように「2.サーバーへの・・・・」に入力、パスワードを保存するにチェック→接続のテスト
7、接続に成功したら、OK→OKで終了

1
1
2
2
3
3
4
4
5
5
6、7
6、7

参考文献

 上記の登録、更新の処理が載っている。その他面白いサンプルがたくさんあり、特にデータベースのSectionは、役にたった。

1

category: 4.PC , DB Excel comment: (0)

データベースをエクセルで操作する-6.参考文献

14.11.04

 データベースをエクセルやWebで操作するには、SQL文を使う必要があります。以下、特に役にたった本です。

9784774139937 .

データベースとSQL文の基礎をわかりやすく説明

9784774138350 .

やってみたいテクニックを多く記載

2958819 .

使いやすい。ExcelVBAを使うには、辞典のようなものが1冊必要。

 

category: 4.PC , DB Excel comment: (0)