excelマクロでアクセスDBからのデータ取り込み

質問

office2003を使用しています。
アクセスのあるテーブルから、エクセルにデータを書き出す際、テーブルを選択→ツール→officelink→excelに出力　をして、データを書き出しています。

上記の作業をエクセルマクロにて実行したいのですが、
エクスプローラー上にある指定のアクセスファイルを開く→該当のテーブルを開く→officelinkを選択→excelに出力を選択　をマクロで自動化することは可能でしょうか？

エクセルマクロは初心者レベルですがコーディングをした経験はあり、上記の自動化方法を調べ中なのですが、マクロにてエクセルとは別のアプリを操作した事が無く、困っております。

ご存知の方、ヒントだけでも結構ですのでお教え下さい。

回答No.5

tag1701 · ベストアンサー

エクセルからアクセスのテーブルデータを呼び出し書き出す方法は
マクロでなくても出来ます。
エクセルの「データ」－「外部データの取り込み」－「新しいデータベースクエリ」を選択。
データソースの選択画面で、「MS Access Database*」を選択しokを押す。
エクスプローラより、目的のmdbファイルを選ぶ。
テーブルやクエリの一覧が出るので、必要なテーブル（今回は）を選択
あとは指示に従いokで進み最後に「どこに出力するか（確かa1何とかという小さな画面が出たと思う）」を問題なければそのままでokすると
テーブルデータが吐き出されてきます。
次からは「データ」－「！データの更新」ボタンで書き換えになります。
ちなみにクエリを選ぶ場合は都度条件設定をする項目を入れとくと
エラーがでます（例えばbetween「開始日付」 and 「終了日付」なんて
入れとくとエラーが出るので」必要な場合、前述のテーブル等選択
した後にクエリを作成しますか？と出てくる部分があるので、ここで
yesにして条件を書き込むことになります。

回答No.4

DexMachina

> 指定のアクセスファイルを開く→該当のテーブルを開く→officelinkを選択
> →excelに出力を選択　をマクロで自動化

Excel側から、Accessのメニューに表示された処理を行うのは、可能では
ありますが面倒です。

それよりも、Excelに「Access等のデータベースに保存されたテーブル等の
データを取得」する機能がありますので、「マクロの自動記録」機能を使用
してその操作をマクロ化するのが簡単かと思います。

手順は以下の通り：
1)Excelのメニューで「ツール(T)→マクロ(M)→新しいマクロの記録(R)」を選択
2)『マクロの記録』ダイアログが開くので、必要ならマクロ名などの情報を更新
　した上で『Ok』をクリック
3)Excelのメニューで「データ(D)→外部データの取り込み(D)→新しいデータ
　ベース クエリ(N)」を選択
4)『データ ソースの選択』ダイアログが開くので、『データベース』タブにある
　「MS Access Database*」を選択して『Ok』をクリック
　※最下にある「クエリ ウィザードを使って～」のチェックはオンにしておきます。
5)『データベースの選択』ダイアログが開くので、Accessファイルを指定して
　『Ok』をクリック
6)クエリ ウィザードが実行されるので、ダイアログの指示に従って、必要なデータ
　が記録されたテーブルのフィールドを指定していきます。
　※最初の画面ではテーブルとフィールドの指定が必須ですが、その後に続く
　　 「データの抽出」や「並べ替え順序の設定」は、省略も可です。
7)「完了」まで進んだら、「Microsoft Office Excelにデータを返す(R)」を選択
　して『完了』をクリック
8)『データのインポート』ダイアログが開くので、適当なセルを指定して、『Ok』を
　クリック
9)指定したセルを基点に、テーブルのデータが表示されたのを確認したら、
　マクロの記録を終了

・・・以上です。

お礼

ありがとうございます！お教え頂いたとおりに設定し、マクロを組むことができました。

やってみて分かったんですが、データはSQLサーバーに存在しているみたいで、今回はSQLサーバーと接続？されたアクセスファイル(.adpファイル)を指定しました。

ただ、マクロを実行すると、「SQLサーバーログイン」のダイアログが表示され、パスワードを求められてしまいます。パスワードを入力すれば、問題なくエクスポートされます。

できればこのパスワードも登録しておき、最後まですべて自動化したいのですが、何か方法はありますでしょうか。

この件は新しい案件として改めて質問させていただきますね。

ありがとうございました。

回答No.3

rivoisu

ExcelからAccessのデータを扱うには通常は　ADO　を使います。
例を示します。
dim DB接続 As ADODB.Connection
dim DB接続文 As String
dim DBSQL文 As String
dim DB表 As Recordset
DB接続文 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\AAA\bbb.accdb"""
Set DB接続 = New ADODB.Connection　’DBとの接続をするObjject
DB接続.Open (DB接続文)　　' DB接続文を使って接続をする
Set DB表 = New ADODB.Recordset　’レコードセット結果が入る入れ物を用意
DBSQL文 = "select * from xxx"　　　’抽出を行うSQL文
DB表.Open DBSQL文, DB接続, , adLockOptimistic　’データを抽出してDB表に入れる
この後レコードセットを使って処理をします。
ツールの参照設定でADOにチェックを入れておくこと。
一件づつ処理することもできるし
copyfromrecordsetメソッドを使えばそのままシートに貼り付けることもできます。
SQLですのでかなり複雑な条件での抽出も可能です。

回答No.2

135ok

ご参考で。

データを移行させる際、エクセル→アクセス、アクセル→エクセル共に、データをコピーし、セルまたは、テーブルに貼り付けて作業してました。できると思うので試してみては。

回答No.1

rukuku

こんばんは

以前、インターネットで調べて試したことがあります。
詳細は忘れてしまいましたが、テーブルのインポートはもっと簡単な手順で出来たと記憶しています。

追加の情報があったらまた投稿します。

とりあえずは、
Excel access 接続
で検索してみてください。
http://www.google.co.jp/search?hl=ja&lr=&safe=off&num=100&ei=A3vMSrPBNIGIkQXMxajgBA&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=Excel+access&spell=1