khmito3-blog

前提：ローカル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

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

1

2

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

1

2

3

4

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

2

4

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

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

2

4

5

6

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

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

更新

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

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

3

4

5

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

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

更新

B、方法2（セルごとに更新する。）

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

1

2

3

4

5

6

モジュールの適当な場所

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

削除

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

2

3

4

モジュールの適当な場所

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

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

.

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

.

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

.

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

　

　データソース（ODBC）をセットアップすると、コードが、簡単になります。

Mysqlの場合

前提：ローカルPCにXAMPPがインストールされ、Apache、Mysqlが、稼働していること
特徴：ローカルPCをWebサーバーとすることにより、公開できる。
方法：PHP、ODBCを経由　

Ⅰ、Ⅱ は、「MySQL Connector/ODBC 5.1」をダウンロードした状態での説明になります。

Ⅱ、ODBCの設定
0、Mysqlで、データベース「lesson」を作成している状態
1、Windowsの「スタートメニュー」の検索で、「ODBC」と入力
2、「データソース（ODBC）のセットアップ」を選択
3、「システムDSN」タブを選択→追加ボタン
4、「MySQL ODBC 5.1 Driver」を選び完了
5、のように入力→TESTボタン
6、接続成功→OK→OK
7、システムデータソースに追加されていることを確認→OK

3

4

5

6

7

Ⅲ、文字化け対策
　データベース「lesson」、テーブル「data」、コラム「biko」に日本語を入力して抽出してみると、Mysqlで「sjis」を指定し、「.php」でも「sjis」を指定しているにもかかわらず、抽出される日本語が「????」で表示されてしまう。（エクセルとの整合性を保つため、あえて「sjis」にしてます。）
　いろいろ調べて試しましたが、結局「MySQL Connector/ODBC 5.1」では、文字セットを設定するところがないため、不可能であることがわかりました。
　「･･･････5.3」には、設定場所が存在するらしいので、仕方なくダウンロードして設定してみると、文字化けが、無事解消されました。以下、設定方法。

1、「MySQL Connector/ODBC 5.3」をダウンロードする。
　　ダウンロードサイト⇒Download Connector/ODBC

2、もう一度、Ⅱ の設定をしています。上記4 では、「MySQL ODBC 5.3 Unicode Driver」を選んでください。
3、Windowsの「スタートメニュー」の検索で、「ODBC」と入力
4、「データソース（ODBC）のセットアップ」を選択
5、「システムDSN」タブを選択→「MySQL ODBC 5.3 Unicode Driver」を選ぶ→構成ボタン
6、「Details」ボタンを押す
7、「cp932」を選択→OK
8、もう一度OKを押して、終了

5

6

7

Ⅳ、接続と抽出
1、データベース「lesson」、テーブル「data」を作成し、
2、データを登録しています。
3、PHPファイルに下記のコードを記述し、XAMPPの「htdocs」フォルダーの適当な場所に保存します。
4、Webで表示すると抽出されます。
　　　　⇒[実行]　　（※1日に数時間だけ稼動）

1

2

4

Accessの場合

前提：ローカルPCにXAMPPがインストールされ、Apacheが、稼働していること
特徴：ローカルPCをWebサーバーとすることにより、公開できる。
方法：PHP、ODBCを経由　

Ⅰ、ODBCの設定
0、Accsessで、データベース「test1.accdb」を作成している状態
1、Windowsの「スタートメニュー」の検索で、「ODBC」と入力
2、「データソース（ODBC）のセットアップ」を選択
3、「システムDSN」タブを選択→追加ボタン
4、「Microsoft Access Driver(*mdb,*accdb)」を選び完了
5、データソース名を入力（任意で、「test1」としました）→選択ボタン
6、「test1.accdb」ファイルのあるフォルダーを指定し、該当するファイルを選択→OK
7、前画面にもどるので、そのままOK
8、システムデータソースに追加されていることを確認→OK

3

4

5

6

8

Ⅱ、接続と抽出
1、データベース「test1」、テーブル「uriage」を作成し、
2、データを登録しています。
3、PHPファイルに下記のコードを記述し、XAMPPの「htdocs」フォルダーの適当な場所に保存します。
4、Webで表示すると抽出されます。
　　　　⇒[実行]　　（※1日に数時間だけ稼動）

1

2

4

　

　登録、更新、削除について。Mysqlに接続するものとします。
　新たにデータベース「drinks」、テーブル「goods」を作ってます。
　

登録

　テーブルの構造と下記のコードを実行した様子

4-2-1_1

4-2-1_2

odbc4-2-1_1.php

odbc4-2-1_2.php

odbc4-1-1.php（抽出、参考）

更新、削除

　下記のコードを実行した様子

4-3-1_1

4-3-1_2

4-3-1_3

4-4-1_2

4-4-1_3

odbc4-3-1_1.php

odbc4-3-1_2.php

odbc4-3-1_3.php

odbc4-4-1_2.php

odbc4-4-1_3.php

Mysqlの場合

前提：レンタルサーバーでMysqlとPHPが使えること。又は、
　　　　ローカルPCにXAMPPがインストールされ、ApacheとMySqlが、稼働していること
特徴：レンタルサーバー、ローカルPC、共にWebで公開できる。
方法：PHPのPDOを使う。

　レンタルサーバーにあるMysqlのデータをPHPのPDOにより抽出してみます。

実行⇒http://khmito3.sub.jp/test/

　Mysqlに関しては検索すればたくさんあるし、本も出版されている。
　参考文献は、以下の本。接続、抽出、登録、更新、削除その他、くわしく書かれています。サンプルあり。

　なお、レンタルサーバーでアクセスの「mdb」ファイルを使うには、まず、レンタルサーバーのOSがWindowsでないといけないそうです。ロリポップのOSを調べたら、Linuxだったので実験できませんでした。
　また、上記の本やWebを参考にローカルWebで「.mdb」の抽出を試みましたが、それも失敗しました･････
　まあ、レンタルサーバーでMysqlが使えれば問題ないし、この件に関しては、Give up !