ＡｃｃｅｓｓからＥｘｃｅｌへ

質問

Ａｃｃｅｓｓの任意のテーブルのデータを配列変数に代入しておいて、Ｅｘｃｅｌの既にあるファイルの任意のセルを指定して転記して表を作成する・・・という事をしたいのですが。
Ｅｘｃｅｌに出力するというマクロがあることも知っていますが、それをすると内容が書き換わってしまうだけでデータの追加が出来ませんし、Ｅｘｃｅｌに出力した後列幅やら表示形式やらのレイアウトも同時にＶＢＡで変更したいのですがうまくできません。

私ＤＡＯはある程度わかります。（古いバージョンを使用しているのでＡＤＯは使ったことがありません。）Loopや配列変数のプロシージャも一応理解できます。
こんな風にしたら出来ます、ないしはこの書籍に載ってます。という回答お願いします。

回答No.3

BraveStory · ベストアンサー

以前仕事で作ったコードを単純にしたものです。こんな感じでアクセスからエクセルオブジェクトを操作できます。
Win98+オフィス2000で動作確認してますが、いくつか条件があります。エクセルシートのA列2行目以降に必ず何か値（数値など）が必要、A列には途中でNullのデータ（空白）が無いこと（A列にはNull許可されていないフィールドの値が既にある程度入っているという想定です）。
ご覧になればお解りかと思いますが、アクセスよりむしろエクセルのVBAの知識が必要です。エクセルはマクロを記録すればコードを吐き出してくれますから、それを参考にされると良いでしょう。
===================================================
Private Sub コマンド0_Click()

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim appExcel As Object
Dim Worksheets As Object

Set DB = CurrentDb
Set RS = CurrentDb.OpenRecordset("テーブル名")

'エクセルオブジェクト生成
Set appExcel = GetObject("エクセルファイルのフルパス")
Set Worksheet = appExcel.Worksheets("ワークシート名")

'作業中はエクセルシートを非表示
appExcel.Parent.Windows(appExcel.Name).Visible = False

With Worksheet

'表の最下行を検出してデータ追加のスタート行を決める
i = .Range("A1").End(4).Row + 1

RS.MoveFirst

'データ追加ループ
Do Until RS.EOF = True
.Cells(i, 1) = RS.Fields("フィールド1")
.Cells(i, 2) = RS.Fields("フィールド2")
RS.MoveNext
i = i + 1
Loop

'列幅調整
.Cells.Columns.AutoFit
'書式
.Range("A:A").NumberFormatLocal = "#,##0_ "

End With

appExcel.Parent.Windows(appExcel.Name).Visible = True

'エクセルブックを閉じる
appExcel.Close True

' オブジェクトの参照を解放
Set Worksheets = Nothing
Set appExcel = Nothing

MsgBox ("エクセルへの出力が終了しました")

End Sub

お礼

かなり具体的なコーディング例を提示していただき有難うございます。
さっそくＥｘｃｅｌのサンプルデータファイルとＡｃｃｅｓｓフォームとボタンを作成しClickイベントに、このコーディングを貼りつけて実験してみました。

Set Worksheet = appExcel.Worksheets("ワークシート名")
のところで「変数が定義されていません。」というメッセージでコンパイルエラーが発生します。もし、何かお気づきの点があれば教えてください。

私の方もここまでのコーディング例を出してもらっているので、自分なりに調べてみようと思います。やはりＥｘｃｅｌ-ＶＢＡを勉強しとかないとオブジェクトの使い方とか判らないですね。
といっても、本屋で調べてみるのですが、ここらへんのＥｘｃｅｌ－ＶＢＡの参考文献になかなか当たらなくて・・・皆さんどうやって勉強しているのでしょうか？

回答No.4

BraveStory

#3です。すみません。変数名が２箇所ちがってますね。

Set Worksheet = appExcel.Worksheets("ワークシート名")

With Worksheet

上の２箇所の Worksheet を Worksheets に訂正してください。
それで動くと思います。

お礼

再度のアドバイスありがとうございます。２箇所訂正して、あとDIM i as Intger
だけ追加したら見事に動きました。バッチリです。
これを応用して作業をすすめていきます。本当に有難うございました。

回答No.2

taka_tetsu

>その部分がどんなコードを書いたらいいのかわかりません。
>ＤＡＯといってもAccess内の任意のテーブルのデータを参照するとか、
>書き換えるとか、追加するとか位しかやったことがなくて別のアプリケーションを起動して、
>書き込んで、そのアプリケーションを保存して、クローズして・・・
>というところが経験が無いのです。多分DIMでアプリを定義して、何かをSETしてとか。。。>
>そんな感じのコードなんでしょうが・・・本屋でさがしてもそういう文献が見つからないのです。

DAOにそんな豊富な機能はありません。mdb内のテーブル操作等の操作ができるだけです。DataAccessObjectですし。
Excelに対して何かを行うのであれば、Excelのマクロがわからないと何にもできませんよ。
Excelのマクロを”知りません”、”わかりません”といわれても、マクロを使わなければできないことをしたいのですから。

>マクロ(VBA)自体は、MDBにあっても、Excelブック内にあってもかまいませ
>ん。DAOが使えるのであれば、どちらからでもデータは取得できるんですし。
その通りだと思います。仮にAccessのフォームに、「EXCELデータに追加」というボタンがあって、そこをクリックしたら既存EXCELファイルにレコードが追加される（しかも必要フィールドや並びが変わる）、処理が終わるとAccessフォームに戻るという動きなら、Accesss側でVBAを組んだほうが良いのだろうと思ったのですが・・・

Accessのフォームが立ち上がっている状態からであれば、それでいいのでは？
最初の質問文からはそこまではわからなかったもので。

となると、AccessのVBAでDAOを使ってデータをデータを取得し、その結果をAccessからExcelをオートメーションで操作することになりますね。

具体的にはこんな感じになります。

Dim xlsApp As Object

'ExcelのAppliationのオブジェクトを取得
Set xlsApp = CreateObject("Excel.Application")

あとは、取得したxlsAppのメソッドやプロパティを使って操作していきます。
Excelのメソッドやプロパティについては、Excelのヘルプを見てください。

お礼

再度のアドバイスありがとうございます。この回答で何とか糸口がつかめそうです。Ｅｘｃｅｌマクロが不得意であることは事実ですが、同じＶＢＡですから何とかなると思います。ボケた質問をしていますが、真摯な対応をして頂き感謝しています。

回答No.1

taka_tetsu

>Ａｃｃｅｓｓの任意のテーブルのデータを配列変数に代入しておいて、Ｅｘｃｅｌの既にあるファイルの任意のセルを指定して転記して表を作成する・・・という事をしたいのですが。

まず、配列変数に入れたい意味は何でしょう？
DAOで取得した値をそのままExcelシートのセルに入れてはいけないんですか？

つまり、Excelのマクロ(VBA)で、テーブルから取得してきた内容を任意のセルに入れればいいだけです。

マクロ(VBA)自体は、MDBにあっても、Excelブック内にあってもかまいません。DAOが使えるのであれば、どちらからでもデータは取得できるんですし。

また、AccessのVBAでExcelを制御することも可能ですので、こちらからExcelブックのレイアウト変更なども可能です。

補足

>まず、配列変数に入れたい意味は何でしょう？
AccessVBAはよく使いますが、ExcelのVBAにあまり経験がありません。配列変数というのは、多分シート・行・列番号を操作するのにはRangeで代入するときの指定に、２次元配列か３次元配列でやるのがいいのだろうなぁという憶測です。

>DAOで取得した値をそのままExcelシートのセルに入れてはいけないんですか？

その部分がどんなコードを書いたらいいのかわかりません。ＤＡＯといってもAccess内の任意のテーブルのデータを参照するとか、書き換えるとか、追加するとか位しかやったことがなくて別のアプリケーションを起動して、書き込んで、そのアプリケーションを保存して、クローズして・・・というところが経験が無いのです。多分DIMでアプリを定義して、何かをSETしてとか。。。そんな感じのコードなんでしょうが・・・本屋でさがしてもそういう文献が見つからないのです。

>マクロ(VBA)自体は、MDBにあっても、Excelブック内にあってもかまいませ
>ん。DAOが使えるのであれば、どちらからでもデータは取得できるんですし。
その通りだと思います。仮にAccessのフォームに、「EXCELデータに追加」というボタンがあって、そこをクリックしたら既存EXCELファイルにレコードが追加される（しかも必要フィールドや並びが変わる）、処理が終わるとAccessフォームに戻るという動きなら、Accesss側でVBAを組んだほうが良いのだろうと思ったのですが・・・