皆さん、二度目まして。
今回より、具体的なコードの解説をしていきます。
その前に、EXCELとACCESSの連携のやり方について、ざざっと解説します。
準備するもの
��ACCESSデータベース（データが入っていること）
��EXCELファイル（VBA）
��やる気
そして、EXCELはインターフェイス（入出力）、ACCSESSはデータのみというようなことを前回書きましたが、EXCELからデータを入出力する方法は複数あったりします。
ただ、方法それぞれについて解説するのはココでの趣旨ではないと思うので、下記のように方法を絞り込みます。
��OFFICE2007以上（2000とかでも動くとは思うが自信ない）
��ADOを使う
��参照設定で「Microsoft ActiveX Data Objects Library」を使用する
�て氷�み・書込みはSQLを使う
まず�，砲弔い�
OFFICE2007よりACCESSも含めて大幅に機能が追加・変更されました。
特にACCESSデータベースは拡張子が「.mdb」だったものが、「.accdb」を標準使用するように変更されました。
もちろん「.mdb」も使えますが、下位互換となっています。
ココではデータベースは「.accdb」を使用しますので、OFFICE2007でないと動きません。
次に�△砲弔い�
ADOとは「ActiveX Data Objects」の略で、ActiveXを使って入出力をする方法です。
他に古い方式としてDAOてのもありますが、使い勝手が悪いので、ADOを使用します。
次に参照設定について
EXCELを開きVBE（Visual Basic Editor」を開き（ALT＋F11で開きます）、ツール→参照設定と開くと、参照設定画面が開きます。
ココで、ADOのライブラリを参照設定しておくと、コードを作るのがとても楽になります。
画像の赤く囲ってある部分の左側にチェックを入れて「OK」で閉じれば設定完了です。
この参照設定は、今開いているEXCELファイルのみ有効です。
次にSQLについて
SQLとは簡単に言えば、データベースに対して命令を行う言語、という認識でいいです。
データベースから読込みや書き込みに関しては、一般的にはVBコードで行うほうが一般的だと思います。
ただ、細かい条件を複数つけたり、データを一気に吐き出すといったことを行う場合、SQLで書いた方が分かりやすいこともあります。
これは個人の好みなので、VBコードでやりたい方は、他の参考書等を参照してください。
では長々と書きましたが、本題に入ります。
まずは、下記のデータを使用してACCESSデータベース内にテーブルを作成してください。
----------------------------------データここから------------------------------
製品名 品番 色番 品名 仕入先 仕入先住所 担当部署
K1 775K1-10010 A1 ボード台座 A社 愛知県 調達01
K1 775K1-10010 B1 ボード台座 A社 愛知県 調達01
K1 775K1-10010 C1 ボード台座 A社 愛知県 調達01
K1 775K1-20010 Z1 F1キー B社 静岡県 調達01
K1 775K1-20020 Z1 F2キー B社 静岡県 調達01
K1 775K1-20030 Z1 F3キー B社 静岡県 調達01
K1 775K1-20040 Z1 F4キー B社 静岡県 調達01
K1 775K1-20050 Z1 F5キー B社 静岡県 調達01
K1 775K1-21010 Z1 テンキー1 C社 群馬県 調達02
K1 775K1-21020 Z1 テンキー2 C社 群馬県 調達02
----------------------------------データここまで------------------------------
一番上は見出しです。
2行目以降がデータで、10個のレコードとなっています。
ちなみに1行分を1レコードとよびます。
1つ1つデータを手打ちでもいいですが、上のデータをコピーしてEXCELに貼り付けて保存し、ACCESSから外部データとして取り込んだ方が楽だと思います。
ACCESSから、外部データ→新しいデータソース→ファイルから→EXCELを選ぶ
こうやって取り込むと、テーブル作成とデータ入力が一瞬で行えるので楽です。
テーブル名は「DB例」としておきます。
次にEXCEL側のコードです。
----------------------------------コードここから------------------------------
Public Const DB As String = "（データベースファイル名をフルパスで入力）"
Sub 出力テスト()
Dim objCnn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRec As New ADODB.Recordset
Dim SQL As String
'データの取得の準備
objCnn.Open ConnectionString:="Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & DB & ";"
Set objCmd.ActiveConnection = objCnn
'SQLをデータベースに送り実行
SQL = "SELECT * FROM DB例"
objCmd.CommandText = SQL
objCmd.CommandType = adCmdText
Set objRec = objCmd.Execute
'A1セルを起点にして全データを吐き出す
Worksheets(1).Range("a1").CopyFromRecordset objRec
'ADODBオブジェクトをクリア
If objRec.State <> ADODB.adStateClosed Then
objRec.Close
Set objRec = Nothing
End If
If objCnn.State <> ADODB.adStateClosed Then
objCnn.Close
Set objCnn = Nothing
Set objCmd = Nothing
End If
End Sub
----------------------------------コードここまで------------------------------
このコードを標準モジュールに貼り付けて実行すれば、ACCESSデータベースから全データを抜き出し、Sheet1シートのA1セルを起点として吐き出します。
次回はこのコードについて解説します。
今回より、具体的なコードの解説をしていきます。
その前に、EXCELとACCESSの連携のやり方について、ざざっと解説します。
準備するもの
��ACCESSデータベース（データが入っていること）
��EXCELファイル（VBA）
��やる気
そして、EXCELはインターフェイス（入出力）、ACCSESSはデータのみというようなことを前回書きましたが、EXCELからデータを入出力する方法は複数あったりします。
ただ、方法それぞれについて解説するのはココでの趣旨ではないと思うので、下記のように方法を絞り込みます。
��OFFICE2007以上（2000とかでも動くとは思うが自信ない）
��ADOを使う
��参照設定で「Microsoft ActiveX Data Objects Library」を使用する
�て氷�み・書込みはSQLを使う
まず�，砲弔い�
OFFICE2007よりACCESSも含めて大幅に機能が追加・変更されました。
特にACCESSデータベースは拡張子が「.mdb」だったものが、「.accdb」を標準使用するように変更されました。
もちろん「.mdb」も使えますが、下位互換となっています。
ココではデータベースは「.accdb」を使用しますので、OFFICE2007でないと動きません。
次に�△砲弔い�
ADOとは「ActiveX Data Objects」の略で、ActiveXを使って入出力をする方法です。
他に古い方式としてDAOてのもありますが、使い勝手が悪いので、ADOを使用します。
次に参照設定について
EXCELを開きVBE（Visual Basic Editor」を開き（ALT＋F11で開きます）、ツール→参照設定と開くと、参照設定画面が開きます。
ココで、ADOのライブラリを参照設定しておくと、コードを作るのがとても楽になります。
画像の赤く囲ってある部分の左側にチェックを入れて「OK」で閉じれば設定完了です。
この参照設定は、今開いているEXCELファイルのみ有効です。
次にSQLについて
SQLとは簡単に言えば、データベースに対して命令を行う言語、という認識でいいです。
データベースから読込みや書き込みに関しては、一般的にはVBコードで行うほうが一般的だと思います。
ただ、細かい条件を複数つけたり、データを一気に吐き出すといったことを行う場合、SQLで書いた方が分かりやすいこともあります。
これは個人の好みなので、VBコードでやりたい方は、他の参考書等を参照してください。
では長々と書きましたが、本題に入ります。
まずは、下記のデータを使用してACCESSデータベース内にテーブルを作成してください。
----------------------------------データここから------------------------------
製品名 品番 色番 品名 仕入先 仕入先住所 担当部署
K1 775K1-10010 A1 ボード台座 A社 愛知県 調達01
K1 775K1-10010 B1 ボード台座 A社 愛知県 調達01
K1 775K1-10010 C1 ボード台座 A社 愛知県 調達01
K1 775K1-20010 Z1 F1キー B社 静岡県 調達01
K1 775K1-20020 Z1 F2キー B社 静岡県 調達01
K1 775K1-20030 Z1 F3キー B社 静岡県 調達01
K1 775K1-20040 Z1 F4キー B社 静岡県 調達01
K1 775K1-20050 Z1 F5キー B社 静岡県 調達01
K1 775K1-21010 Z1 テンキー1 C社 群馬県 調達02
K1 775K1-21020 Z1 テンキー2 C社 群馬県 調達02
----------------------------------データここまで------------------------------
一番上は見出しです。
2行目以降がデータで、10個のレコードとなっています。
ちなみに1行分を1レコードとよびます。
1つ1つデータを手打ちでもいいですが、上のデータをコピーしてEXCELに貼り付けて保存し、ACCESSから外部データとして取り込んだ方が楽だと思います。
ACCESSから、外部データ→新しいデータソース→ファイルから→EXCELを選ぶ
こうやって取り込むと、テーブル作成とデータ入力が一瞬で行えるので楽です。
テーブル名は「DB例」としておきます。
次にEXCEL側のコードです。
----------------------------------コードここから------------------------------
Public Const DB As String = "（データベースファイル名をフルパスで入力）"
Sub 出力テスト()
Dim objCnn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRec As New ADODB.Recordset
Dim SQL As String
'データの取得の準備
objCnn.Open ConnectionString:="Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & DB & ";"
Set objCmd.ActiveConnection = objCnn
'SQLをデータベースに送り実行
SQL = "SELECT * FROM DB例"
objCmd.CommandText = SQL
objCmd.CommandType = adCmdText
Set objRec = objCmd.Execute
'A1セルを起点にして全データを吐き出す
Worksheets(1).Range("a1").CopyFromRecordset objRec
'ADODBオブジェクトをクリア
If objRec.State <> ADODB.adStateClosed Then
objRec.Close
Set objRec = Nothing
End If
If objCnn.State <> ADODB.adStateClosed Then
objCnn.Close
Set objCnn = Nothing
Set objCmd = Nothing
End If
End Sub
----------------------------------コードここまで------------------------------
このコードを標準モジュールに貼り付けて実行すれば、ACCESSデータベースから全データを抜き出し、Sheet1シートのA1セルを起点として吐き出します。
次回はこのコードについて解説します。
|
|
|
|
コメント(9)
>>[3]
すみません、追加です。
MACは動作確認してません。検証する実機もありませんので。
どなたか動作確認された方がいましたら、情報をお願います。
また、Microsoft Access データベース エンジンですが、OFFICE製品以外からではほぼ扱えません。
例えばVBS（Visual Basic Script）では、ACE 12.0のプロバイダーにはアクセスできません。ただ、OFFICEさえインストールされていれば、JET 4.0プロバイダーにはアクセスできるようです。
データベースを使うのに、MicrosoftのAzureや、ORACLEを使用しようと、別途ソフトのインストールが必要で、どのPCでもお手軽に使えるわけではありません。
なので、EXCEL＋ACCESSが、とっつきやすさでは一番敷居が低いと私は思っています。
すみません、追加です。
MACは動作確認してません。検証する実機もありませんので。
どなたか動作確認された方がいましたら、情報をお願います。
また、Microsoft Access データベース エンジンですが、OFFICE製品以外からではほぼ扱えません。
例えばVBS（Visual Basic Script）では、ACE 12.0のプロバイダーにはアクセスできません。ただ、OFFICEさえインストールされていれば、JET 4.0プロバイダーにはアクセスできるようです。
データベースを使うのに、MicrosoftのAzureや、ORACLEを使用しようと、別途ソフトのインストールが必要で、どのPCでもお手軽に使えるわけではありません。
なので、EXCEL＋ACCESSが、とっつきやすさでは一番敷居が低いと私は思っています。
>>[6]
> 私は、Microsoft.ACE.OLEDB.12.0 を使って書いたものを、Accessが入っていない人に渡したらエラーが出たので Microsoft.Jet.OLEDB.4.0 に変更した記憶があります。
今は確認する環境がないので、時間があるときに再度試してみます。
指摘ありがとうございます。
たしかに私の検証はPC十数台程度なので、まだ確認できていない条件があるかもしれません。
こちらでもさらに確認してみます。
＞ 今のところ、Windows7 と Windows10 のそれぞれ32ビットと64ビットで動くことは確認済みです。
ご存じだとは思いますが、Windows x64 でも Office はデフォルトでは 32bit版 が入りますね。
私は知らずに、はまったことがあります。
私もハマりました。
言われてみればWindows7 64ビット＋ OFFICE 64ビットは試してなかったはずです。
検証は難しいかもしれませんが、チャンスがあれば試してみます。
> 私は、Microsoft.ACE.OLEDB.12.0 を使って書いたものを、Accessが入っていない人に渡したらエラーが出たので Microsoft.Jet.OLEDB.4.0 に変更した記憶があります。
今は確認する環境がないので、時間があるときに再度試してみます。
指摘ありがとうございます。
たしかに私の検証はPC十数台程度なので、まだ確認できていない条件があるかもしれません。
こちらでもさらに確認してみます。
＞ 今のところ、Windows7 と Windows10 のそれぞれ32ビットと64ビットで動くことは確認済みです。
ご存じだとは思いますが、Windows x64 でも Office はデフォルトでは 32bit版 が入りますね。
私は知らずに、はまったことがあります。
私もハマりました。
言われてみればWindows7 64ビット＋ OFFICE 64ビットは試してなかったはずです。
検証は難しいかもしれませんが、チャンスがあれば試してみます。
>>[7]
コメントありがとうございます。
> ついでではありますが、関連情報を一つ。コネクションのオープン時にプロバイダエラー（プロバイダがインストールされていないとかなんとか）がでる場合は、「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」をインストールすると解決する場合があります。URL：https://www.microsoft.com/ja-jp/download/details.aspx?id=13255
OFFICE以外のソフトからプロバイダを参照しようとした時に、ご指摘の再領布パッケージをインストールすると解決した記憶があります。
でも、できればこういったものをインストールしなければ使用者が使えないということはできる限り避けたいですね。
コメントありがとうございます。
> ついでではありますが、関連情報を一つ。コネクションのオープン時にプロバイダエラー（プロバイダがインストールされていないとかなんとか）がでる場合は、「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」をインストールすると解決する場合があります。URL：https://www.microsoft.com/ja-jp/download/details.aspx?id=13255
OFFICE以外のソフトからプロバイダを参照しようとした時に、ご指摘の再領布パッケージをインストールすると解決した記憶があります。
でも、できればこういったものをインストールしなければ使用者が使えないということはできる限り避けたいですね。
- mixiユーザー
- ログインしてコメントしよう！
|
|
|
|
EXCEL VBA 更新情報
-
最新のアンケート
-
まだ何もありません
-
