ACCESSとＥＸＣＥＬの連携について

質問

ACCESSとＥＸＣＥＬの連携について教えてください。

メインフォームＡ（単票）の中にサブフォームＢ（単票）があり、更にサブフォームＢ（単票）の中にサブフォーム（メインからみると孫フォーム）Ｃ（帳票）があるという構成のフォームがあります。各フォームにはそれぞれテキストボックスtext_X,text_Y,text_Zがあり、メインフォームにコマンドボタンＱがあります。いま、このコマンドボタンＱをクリックすることによってtext_X,text_Y,text_Zの内容をＥＸＣＥＬの特定ファイルの特定セル（例えばＬ１、Ｍ１、Ｎ１～１０）に反映させたいと思っているのですが、メインフォーム（Ａ）にあるtext_Xとサブフォーム（Ｂ）にあるtext_YはＥＸＣＥＬのセル（Ｌ１、Ｍ１）に取り込むことができるのですが、孫フォーム（Ｃ）にあるtext_Zをセル（Ｎ１～１０）に反映させることが出来ません。text_Zはフォームが帳票フォームであることからＬＯＯＰを使っています。コマンドボタンＱのクリック時のイベントでコードの書き方が違っているらしいのです。ＶＢＡは全く素人の手探り状態です。どなたか、素人でもわかるように教えていただけたら幸いです。よろしくお願い致します。

具体的には次のようなものです。（一部抜粋）
Private Sub コマンド145_Click()
Dim oApp As Object
Dim rs As DAO.Recordset
Dim i As Long
Set rs = Me!営業入力SF.Form.RecordsetClone
Set rs = 担当(1)Ｆ.Form.RecordsetClone
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

'指定のエクセルファイルを開く
oApp.Workbooks.Open Filename:="I:\再出発！\受注票.xlt"

'エクセルファイルへデータセットする
oApp.Range("名称").Value = Me![名称]
oApp.Range("住所").Value = Me![住所]
oApp.Range("パンフ送付").Value = Forms![営業F]![営業入力SF]![パンフ送付日]
oApp.Range("ＤＶＤ送付").Value = Forms![営業F]![営業入力SF]![ＤＶＤ送付日]
oApp.Range("正式見積書").Value = Forms![営業F]![営業入力SF]![正式見積書送付日]
oApp.Range("契約書送付").Value = Forms![営業F]![営業入力SF]![契約完了日]

'サブフォームの内容をエクスポートする
i = 11
Do Until rs.EOF

oApp.Range("Ｊ" & Format(i)).Value = rs!テキスト１
oApp.Range("Ｌ" & Format(i)).Value = rs!テキスト4
i = i + 1

rs.MoveNext
Loop
Set rs = Nothing
Exit_コマンド145_Click:
Exit Sub

Err_コマンド145_Click:
MsgBox Err.Description
Resume Exit_コマンド145_Click

End Sub

（ＷＩＮＤＯＷＳ　ＸＰ　　　　ＡＣＣＥＳＳ　２００２　　ＥＸＣＥＬ　２００２　を使用）

回答No.5

piroin654 · ベストアンサー

>多分、孫フォーム自体を認識していないの
>ではないかと思っています。

ということなので、
ほぼ、同じ方法で実際にフォームを作ってやってみましたが、
データはExcelに表示されます。
各サブフォームを表示するコントロール名は
初期値のままの埋め込み0で行なっています。

孫サブフォームのレコードソースに適当にテーブルを設定し、
そのテーブルのフィールドの二つ、名前と住所のデータを
Excelにだしました。コードの中に

＞If rs.Recordcount > 0 Then

などを入れていますが、孫サブフォームに
データがなければ、孫サブフォームの
データをExcelに送る処理を中止し、
メッセージをだします。
以下は、孫サブフォームのレコードを
Excelに送り出す部分。

Private Sub コマンド1_Click()
Dim rs As DAO.Recordset
Dim oApp As Object
Dim i As Long

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

'孫サブフォームのレコードセットを設定
Set rs = Forms!フォーム1!埋め込み0!埋め込み0!.Form.RecordsetClone

oApp.UserControl = True
oApp.Workbooks.Open Filename:="C:\Documents and Settings\user\デスクトップ\myData3.xls"

i = 11
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
oApp.Range("Ｊ" & Format(i)).Value = rs!名前
oApp.Range("Ｌ" & Format(i)).Value = rs!住所
i = i + 1
rs.MoveNext
Loop
Else
MsgBox "孫サブフォームにデータがありません"
End If

'後始末
rs.Close
Set rs = Nothing
Set oApp = Nothing
End Sub

なお、実際にコードが動くまでは、エラー回避
などははずしておきます。

On Error Resume Next

はコメントアウトしておいてください。

お礼

親切なご回答ありがとうございました。出来ました！孫フォームがすべてＥＸＣＥＬに表示されています。piroin654さんのものを丸写ししました。ありがとうございました。
ついでと言っては申し訳ないのですが、孫フォームにあるレコード数が多すぎて、ＥＸＣＥＬの予定表示数を越えて表示されてしまう場合もあるのですが、例えば、最新１０件のレコードを表示するとか、重要なレコードのみを表示するとかということは出来ないものでしょうか？
今回の質問事項とは直接関係はありませんがもし、ご存じでしたら教えていただけると助かります。
そんなこともありますので、もうしばらく質問を締め切らずにおきます。今回は本当にありがとうございました。

回答No.8

piroin654

No6の続きですが。

最新の場合は、たとえばフィールドに日付
という日付型のフィールドがあった場合、

SELECT TOP 10 Taaa.名前, Taaa.日付, Taaa.住所
FROM Taaa
GROUP BY Taaa.名前, Taaa.日付, Taaa.住所;

のようにすれば出てきます。これを孫サブフォームの
レコードソースにします。

同じように、重要なものは、テーブルに
重要というYes/No型のフィールドをおいて
おいて、

SELECT Taaa.名前, Taaa.住所
FROM Taaa
WHERE (((Taaa.重要)=True));

とすれば出てきます。これを孫のサブフォームに
します。

なお、最新で重要なものは

SELECT TOP 10 Taaa.名前, Taaa.日付, Taaa.住所
FROM Taaa
WHERE (((Taaa.重要)=True))
GROUP BY Taaa.名前, Taaa.日付, Taaa.住所;

ただし、両方の条件を満たす場合ですから、
両方の条件を満たさない場合はデータが
少なくなります。

これらのクエリを条件により、孫サブフォームの
レコードソースをボタンやチェックボックスの
切り替えでVBAで変更することができます。

以上のほかに、VBAにて表示方法を高機能
にすることはできますが、まずは確実に
表示できる方法を選択し、その後、表示方法、
あるいは出力方法を考えていったほうが
いいかもしれません。
もちろん、冒険は最高の上達方法です。

お礼

ご回答ありがとうございます。piroin654さんと30246kikuさんのおかげで思っているようなものが出来ました。感謝！感謝！です。このサイトはいいサイトですね。私も誰かのお役にたちたいとは思いますが、質問者に終始している現在です。さて、ベストアンサーはお二人にさしあげたいのですがそうもいかないらしいので、最初に丁寧なご回答をくださいましたpiroin654さんにしたいと思います。もちろん
30246kikuさんにも感謝しております。また何かありましたら質問させていただきますのでその時はよろしくお願い致します。本当にありがとうございました。

回答No.7

30246kiku

＃４です

できたようでなによりです。
いろいろ方法があると思いますので、以下参考までに

> 孫フォームにあるレコード数が多すぎて、ＥＸＣＥＬの予定表示数を越えて表示されてしまう場合もあるのですが、例えば、最新１０件のレコードを表示するとか、重要なレコードのみを表示するとかということは出来ないものでしょうか？

これは、Excelへ表示する（書き出す）時のことでしょうか。

i = 11
If rs.RecordCount > 0 Then
　　rs.MoveFirst
　　Do Until rs.EOF
　　　　oApp.Range("J" & i).Value = rs!名前
　　　　oApp.Range("L" & i).Value = rs!住所
　　　　i = i + 1
　　　　rs.MoveNext
　　Loop

は、フォームに表示している先頭から全部処理しているので
Excel２０行目を書いてやめるには、

　　　　oApp.Range("L" & i).Value = rs!住所
　　　　If (i >= 20 ) Then Exit Do　　' ★判別でループ抜け
　　　　i = i + 1

とか

また、フォーム表示の最後１０行分を処理対象にするのなら

If rs.RecordCount > 0 Then
　　i = rs.RecordCount - 10　' ★最後から１０行前を開始位置に設定
　　If (i < 0) Then i = 0　　' ★
　　rs.AbsolutePosition = i　' ★
　　i = 11
　　Do Until rs.EOF
　　　　oApp.Range("J" & i).Value = rs!名前

とか

表示最後から処理したい時は、最後に行ってから前へ前へ

If rs.RecordCount > 0 Then
　　rs.MoveLast　' ★
　　Do Until rs.BOF　' ★
　　　　oApp.Range("J" & i).Value = rs!名前
　　　　oApp.Range("L" & i).Value = rs!住所
　　　　If (i >= 20 ) Then Exit Do　　' ★判別でループ抜け？
　　　　i = i + 1
　　　　rs.MovePrevious　' ★
　　Loop

とか

また、「重要」は、それなりの判別処理を記述する必要があると思います。

例えば

　　Do Until rs.EOF
　　　　If (rs!XXXX = YYYY) Then　' ★「重要」なものの判別
　　　　　　oApp.Range("J" & i).Value = rs!名前
　　　　　　oApp.Range("L" & i).Value = rs!住所
　　　　　　If (i >= 20 ) Then Exit Do　　' ★判別でループ抜け？
　　　　　　i = i + 1
　　　　End If
　　　　rs.MoveNext
　　Loop

要は、孫フォームにどのような順で表示しているから、
どのような順で何件分書き出すか、
だと思います。

なお、孫フォームの表示自体を最新１０件分とか・・・は、
フォームの作り方等が関係すると思いますので、
現在の情報下では簡単ではないと思います。

子と孫の間で、
リンク親子フィールドを設定していたら・・・
各パターンの１０件分を用意しておいて？？・・・とか
その１０件は、どのような順？？・・・
その作り方次第で、更新できる/できないものになったり？？・・・

リンク親子フィールドを設定していなかったら・・・
こっちの方が簡単だと思います。

また、孫の RecordsetClone は使わない・・・
っていう方法が良くなったりして？？・・・

参考になればと・・・　自己責任にて・・・

お礼

ご回答本当にありがとうございます。

＞これは、Excelへ表示する（書き出す）時のことでしょうか。

そうです。

いろいろなケースを教えていただいて感謝いたしております。
とりあえず

＞フォーム表示の最後１０行分を処理対象にするのなら

If rs.RecordCount > 0 Then
　　i = rs.RecordCount - 10　' ★最後から１０行前を開始位置に設定
　　If (i < 0) Then i = 0　　' ★
　　rs.AbsolutePosition = i　' ★
　　i = 11
　　Do Until rs.EOF
　　　　oApp.Range("J" & i).Value = rs!名前

を利用してＥＸＣＥＬのフォームにうまく収まりました。
一人で考えていてはとてもこうはいきません。ホント助かりました。ありがとうございました。

回答No.6

piroin654

サブフォームに TOP10 を表示

たとえば、サブフォームに設定したテーブルに、
売上げというフィールドがあるとします。
テーブル名をTaaaとします。このテーブルを
もとに、以下のクエリを作成します。
名前を、Q孫ソース　とします。

Q孫ソース

SELECT TOP 10 Taaa.名前, Sum(Taaa.売上げ) AS 売上げの合計
FROM Taaa
GROUP BY Taaa.名前 DESC;

これを、登録して孫フォームのレコードソースに
してもいいですし、このSQL文を孫フォームの
レコードソースにところに直接貼り付けても
いいです。

試してみてください。

あるいは、IDなどを利用して表示順を以下の
ようにも設定できます。

SELECT TOP 10 Taaa.ID, Taaa.名前, Sum(Taaa.売上げ) AS 売上げの合計
FROM Taaa
GROUP BY Taaa.ID, Taaa.名前
ORDER BY Taaa.ID;

回答No.4

30246kiku

雰囲気だけでいいですか。

親フォーム「FrmX」には、
テキストボックス「text_X」、ボタンQ、サブフォームコントロール「FsubX」がある。

子フォーム「FrmY」には、
テキストボックス「text_Y」、サブフォームコントロール「FsubY」がある。
また、フォームは親「FsubX」の SourceObject に指定されている。

孫フォーム「FrmZ」には、
テキストボックス「text_Z」がある。
また、フォームは子「FsubY」の SourceObject に指定されている。

とした場合、

親フォームのボタンQがクリックされた時に、
テキストボックス「text_X」「text_Y」「text_Z」の値を引っ張るには、

With Me
　　XX = .text_X
　　With .FsubX.Form
　　　　YY = .text_Y
　　　　With .FsubY.Form
　　　　　　ZZ = .text_Z
　　　　End With
　　End With
End With

のように、順にサブフォームコントロールをたどっていけばよいと思います。

孫は、帳票で全部を引っ張るのなら、

With .FsubY.Form
　　ZZ = .text_Z
End With

部分を RecordsetClone を扱うように変更すれば、と思います。

例えば、

With .FsubY.Form.RecordsetClone
　　If (.RecordCount > 0) Then
　　　　.MoveFirst
　　　　While (Not .EOF)
　　　　　　ZZ1 = .Fields("フィールド名1")
　　　　　　ZZ2 = .Fields("フィールド名2")
　　　　　　.MoveNext
　　　　Wend
　　End If
End With

上記記述部分の、XX / YY / ZZ / ZZ1 / ZZ2 に、Excelのセルを指定すればよいと思います。

※　未検証

自己責任にて・・・

※　孫（子）フォームが表示されてから、一度も孫（子）フォーム側ににフォーカスが移動していなかったなどで、孫（子）の Recordset をうまく扱えなかった経験があります。
そういうことがあるかもしれないということで・・・

お礼

ご回答ありがとうございます。よく検証してみます。何分素人のうえ限られた時間内での作業ですので少しお時間をください。結果を後程ご連絡いたします。ありがとうございました。取り急ぎ御礼まで。

回答No.3

piroin654

訂正です。
No2で、

Set rs = Forms!メインフォーム名!サブフォーム表示コントロール名!サブフォーム表示コントロール名.RecordsetClone

としていましたが、正しくは、

Set rs = Forms!メインフォーム名!サブフォーム表示コントロール名!サブフォーム表示コントロール名.Form.RecordsetClone

です。最後のあたりでFormが抜けていました。

また、

Set rs = Forms!メインフォーム名!サブフォーム表示コントロール名!サブフォーム表示コントロール名.Form.RecordsetClone

は、表示コントロール名を変えていなければ、

Set rs = Forms!メインフォーム名!埋め込み0!埋め込み0!.Form.RecordsetClone

のようになります。

お礼

ていねいなご回答ありがとうございます。訂正も了解いたしました。当方、只今、検証整理中ですが、何分齢６０を越えて昼間の肉体労働の後の素人のパソコン作業ですので時間的肉体的制約もあり少々時間を必要とします。検証結果が出次第ご連絡いたします。よろしくお願い致します。
ＰＳ：現在のところ、エラーメッセージは出なくなりましたが、孫フォームがＥＸＣＥＬに反映していません。多分、孫フォーム自体を認識していないのではないかと思っています。取り急ぎご連絡まで。　ありがとうございました。

回答No.2

piroin654

>Set rs = 担当(1)Ｆ.Form.RecordsetClone

ここのところを説明しておかないと、先に進めない
と思いますので、以下を検討してください。

(1)
まず、
>担当(1)Ｆ
というのは、おそらくサブフォームの一つ
(想像では孫のサブフォームかも)を設定したいの
だろうと思いますが、フォーム名などオブジェクト
の名前に()を使うのはやめておく。

>担当1Ｆ

ならば、エラーはでないです。

(2)
Accessから見ると、

>担当

は変数に見えます。したがって、
>担当(1)Ｆ
を
>担当1Ｆ
に変更して
>Set rs = 担当1Ｆ.Form.RecordsetClone
としても、変数の宣言がない、というエラーがでます。
したがって、フォームを特定するならば、

>Set rs = 担当(1)Ｆ.Form.RecordsetClone

を、

Set rs = Forms!メインフォーム名!サブフォーム表示コントロール名!サブフォーム表示コントロール名.RecordsetClone

のように、します。Forms!は、はすしてはいけません。
フォームを指定します、という宣言のようなものです。
この方法は、常にメインフォームからサブフォームを
順番にもぐりこんで取得していく方法です。
ここで、

>サブフォーム表示コントロール名

というのは、フォームにサブフォームを表示するコントロール
の名前で、初期値は「埋め込み0」のような名前になっています。
そのままでもいいのですが、コントロールのところで
右クリックして名前を、コントロールソース、すなわち
サブフォームと同じ名前にしてもいいです。そのときは
上記の「サブフォーム表示コントロール名」を
変更した名前にしておきます。

なお、

Forms!メインフォーム名!サブフォーム表示コントロール名!サブフォーム表示コントロール名

は、順番に、メインフォームの名前、その下のサブフォームを
表示するコントロール名、さらにその下のサブフォームを
表示するコントロール名、という順番です。

(3)
付録として、No1でも言いましたように、
rsの設定、
rs!テキスト１のテキスト１は、サブフォームの
レコードソースのフィールド名を指定すること、
をわすれないように。

ここまで、書くと、案外解決したりする、可能性は
あるような気はします。

>素人の怖い物知らず

結構です。これがないと進歩はありません。

回答No.1

piroin654

コードを全部見ませんが、
途中のコードの間違いなどは別にして、気がついたところだけ。

>Dim rs As DAO.Recordset
>Dim i As Long
>Set rs = Me!営業入力SF.Form.RecordsetClone
>Set rs = 担当(1)Ｆ.Form.RecordsetClone

を

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim i As Long
Set rs1 = Me!営業入力SF.Form.RecordsetClone
Set rs2 = 担当(1)Ｆ.Form.RecordsetClone

のようにしないと、rsは最終的に、

>Set rs = 担当(1)Ｆ.Form.RecordsetClone

になってしまいます。

#######################################################

>oApp.Range("Ｊ" & Format(i)).Value = rs!テキスト１
>oApp.Range("Ｌ" & Format(i)).Value = rs!テキスト4

これは、もはやどのサブフォームのRecordsetCloneを指すのか
わかりません。

>rs!テキスト１
>rs!テキスト4

このテキスト１や、テキスト4はフォームのフィールド名を
さしているような感じですが、レコードセットですから、
フォームのレコードソースになっている
テーブルなり、クエリなりのフィールド名を設定しなければ
なりません。

########################################################

孫サブフォームまである取り扱いには、以下のような
Q&Aもありますが、

http://www7.big.or.jp/~pinball/discus/access/97696.html

それ以前の問題として、
もう一度、親フォーム、サブフォーム、孫サブフォームの
各レコードソース、リンク親、リンク子の各フィールド
親フォーム名、各サブフォーム名、

などを示さないと、いくら入り組んだ質問が好きな回答者でも
回答に窮します。

お礼

早速のご回答ありがとうございます。
　私も、素人の怖い物知らずで次々に必要と思われるフィールドを追加していくうちにとても複雑なものになってしまいました。

＞もう一度、親フォーム、サブフォーム、孫サブフォームの
＞各レコードソース、リンク親、リンク子の各フィールド
などを示さないと、いくら入り組んだ質問が好きな回答者でも
回答に窮します。

少し整理してから後程ご連絡いたします。

なお、仰る通りに修正して、現段階として　担当（１）にエラー表示「オブジェクトが必要です。」が出ます。

取り急ぎ御礼まで。