こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

ACCESSとEXCELの連携について

ACCESSとEXCELの連携について教えてください。

メインフォームA(単票)の中にサブフォームB(単票)があり、更にサブフォームB(単票)の中にサブフォーム(メインからみると孫フォーム)C(帳票)があるという構成のフォームがあります。各フォームにはそれぞれテキストボックスtext_X,text_Y,text_Zがあり、メインフォームにコマンドボタンQがあります。いま、このコマンドボタンQをクリックすることによってtext_X,text_Y,text_Zの内容をEXCELの特定ファイルの特定セル(例えばL1、M1、N1~10)に反映させたいと思っているのですが、メインフォーム(A)にあるtext_Xとサブフォーム(B)にあるtext_YはEXCELのセル(L1、M1)に取り込むことができるのですが、孫フォーム(C)にあるtext_Zをセル(N1~10)に反映させることが出来ません。text_Zはフォームが帳票フォームであることからLOOPを使っています。コマンドボタンQのクリック時のイベントでコードの書き方が違っているらしいのです。VBAは全く素人の手探り状態です。どなたか、素人でもわかるように教えていただけたら幸いです。よろしくお願い致します。


具体的には次のようなものです。(一部抜粋)
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)F.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("DVD送付").Value = Forms![営業F]![営業入力SF]![DVD送付日]
oApp.Range("正式見積書").Value = Forms![営業F]![営業入力SF]![正式見積書送付日]
oApp.Range("契約書送付").Value = Forms![営業F]![営業入力SF]![契約完了日]


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

oApp.Range("J" & Format(i)).Value = rs!テキスト1
oApp.Range("L" & 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

(WINDOWS XP    ACCESS 2002  EXCEL 2002 を使用)

投稿日時 - 2011-02-05 20:30:46

QNo.6500458

すぐに回答ほしいです

質問者が選んだベストアンサー

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

ということなので、
ほぼ、同じ方法で実際にフォームを作ってやってみましたが、
データは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("J" & Format(i)).Value = rs!名前
oApp.Range("L" & 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

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

投稿日時 - 2011-02-07 04:33:39

お礼

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

投稿日時 - 2011-02-08 00:12:33

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

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

投稿日時 - 2011-02-08 22:18:28

お礼

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

投稿日時 - 2011-02-09 20:36:10

ANo.7

#4です

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

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

これは、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

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

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

とか

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

If rs.RecordCount > 0 Then
  i = rs.RecordCount - 10 ' ★最後から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


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


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

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

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

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


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

投稿日時 - 2011-02-08 13:09:40

お礼

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

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

そうです。

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

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

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

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

投稿日時 - 2011-02-08 22:30:30

ANo.6

サブフォームに 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;

投稿日時 - 2011-02-08 01:46:20

ANo.4

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


親フォーム「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 をうまく扱えなかった経験があります。
そういうことがあるかもしれないということで・・・

投稿日時 - 2011-02-06 20:07:12

お礼

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

投稿日時 - 2011-02-07 00:03:48

ANo.3

訂正です。
No2で、

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


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

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

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


また、

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

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

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

のようになります。

投稿日時 - 2011-02-06 19:29:50

お礼

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

投稿日時 - 2011-02-07 00:21:45

ANo.2

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

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

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

>担当1F

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

(2)
Accessから見ると、

>担当

は変数に見えます。したがって、
>担当(1)F

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

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

を、

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

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

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

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

なお、

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

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


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

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

>素人の怖い物知らず

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

投稿日時 - 2011-02-06 19:09:16

ANo.1

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


>Dim rs As DAO.Recordset
>Dim i As Long
>Set rs = Me!営業入力SF.Form.RecordsetClone
>Set rs = 担当(1)F.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)F.Form.RecordsetClone

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

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

になってしまいます。



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

>oApp.Range("J" & Format(i)).Value = rs!テキスト1
>oApp.Range("L" & Format(i)).Value = rs!テキスト4

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

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

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

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

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

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

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

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

投稿日時 - 2011-02-05 21:43:21

お礼

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

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

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

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

取り急ぎ御礼まで。

投稿日時 - 2011-02-05 23:41:27

あなたにオススメの質問