アクセスのデータをエクセルに上書き保存し

質問

先日、「アクセス2013でエクセルにデータを出力したい」で質問し、
回答をもらい、行いたいことができました。
http://okwave.jp/qa/q8634369.html

数行あけて出力できるようになったのですが、これを繰り返して、間にデータを
挿入していこうとすると、保存した値が消えて最後に関数を呼び出した結果しか表示されません

上書き保存で実施していくためにはどうしたらよいのでしょうか？

Accessは2013です

～テーブルイメージ～

テーブル１
メーカー名　　2014/6/1　　2014/6/2　　2014/6/3　　合計
-----------------------------------------------------
メーカーA　　　　　2　　　　1　　　　　　1　　　　　4
メーカーB　　　　　1　　　　1　　　　　　1　　　　　3

テーブル２
メーカー名　　2014/6/1　　2014/6/2　　2014/6/3　　合計
-----------------------------------------------------
メーカーA　　　　　5　　　　6　　　　　　7　　　　　18
メーカーB　　　　　2　　　　2　　　　　　2　　　　　6

～出力エクセルイメージ～
A　　 　　B　 　C　　　　D・・・　H　　
1　メーカーA　　 2　　　　1　　　　1　　　　4
2 メーカーA　　 5　　　　6　　　　7　　　　18
3
4
5　メーカーB　 　1　　　　1　　　　1 　　 　3
6 メーカーB　 　2　　　　2　　　　2　　　　6
7

回答No.4

NotFound404 · ベストアンサー

１・
追記する場合。
前もって前回回答の
Set RS = DB.OpenRecordset("実際のテーブル名に", dbOpenSnapshot) を
Set RS = DB.OpenRecordset("select * from 実際のテーブル名に order by メーカー名", dbOpenSnapshot)
のように並び替えを指示してやる必要があります。
http://support.microsoft.com/kb/834927/ja
または「実際のテーブル名」を出力したクエリで並び替え指定を
行っているのならテーブル名の代わりにクエリ名を指定します。
で一回目の出力をします。

二回目は前回回答の一部を修正して
Sub xlS2()
Dim oXL As Object
Dim oBK As Object
Dim oSH As Object
Dim DB As DAO.Database 'ここでエラーになったら参照設定の変更を
Dim RS As DAO.Recordset
Dim i As Long, j As Long

Set DB = CurrentDb
Set RS = DB.OpenRecordset("select * from 実際のテーブル名 order by メーカー名", dbOpenSnapshot)
'か前述のようにクエリ名
Set oXL = CreateObject("Excel.Application")
'oXL.Visible = True
Set oBK = oXL.Workbooks.Open("e:\123.xlsx") 'ここ
Set oSH = oBK.sheets(1)

'For i = 1 To RS.Fields.Count 'フィールド名転記
'oSH.cells(1, i) = RS.Fields(i - 1).Name
'Next

j = 3 'レコード転記は３行目から ’ここ
Do Until RS.EOF
For i = 1 To RS.Fields.Count
oSH.cells(j, i) = RS.Fields(i - 1).Value
Next
RS.MoveNext
j = j + 5 '空き間隔調整
Loop
oBK.Close True 'ここ

oXL.Quit
Set oBK = Nothing: Set oXL = Nothing
RS.Close: Set RS = Nothing

End Sub

です。
まとめて一気にというのでしたら、
直積とユニオンクエリで空白行を作っても
出来そうですが、すみません割愛させてください。

お礼

何度もありがとうございます。

やりたいこと、できました～～！！！

ほかの方もいろいろとやり方を教えていただき勉強になりましたが、今回は私のやりたい方法をかいていただいたこの方をベストアンサーとさせてもらいます。

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

回答No.3

30246kiku

おそらく、クロス集計クエリ数個を Excel 出力して、
・メーカー名順にしたい
ということと解釈しました。

せっかく Excel に直接書き込むので、
・ズラッと出力後に Excel 機能を使って
・メーカー名、出力順でソートする
方法もあるかと思います。

以下は、その一例になります。
（解釈違いならスルーしてください）

Public Sub Samp1()
　　Dim rs As DAO.Recordset
　　Dim vA As Variant, v As Variant
　　Dim iRow As Long, iCol As Long, iCnt As Long
　　Dim i As Long
　　Const xlAscending = 1
　　Const xlNo = 2

　　vA = Array("Q1", "Q12", "Q3", "Q9", "Q5", _
　　　　　　　　"Q6", "Q7", "Q8", "Q4", "Q10", _
　　　　　　　　"Q11", "Q2", "Q13", "Q14", "Q15")

　　With CreateObject("Excel.Application")
　　　　.Visible = True
　　　　.Workbooks.Add
　　　　iRow = 1
　　　　iCnt = 1
　　　　For Each v In vA
　　　　　　Set rs = CurrentDb.OpenRecordset(v)
　　　　　　If (iRow = 1) Then
　　　　　　　　For i = 0 To rs.Fields.Count - 1
　　　　　　　　　　.Cells(iRow, i + 1) = rs(i).Name
　　　　　　　　Next
　　　　　　　　iCol = i + 1
　　　　　　　　.Cells(iRow, iCol) = "合計" ' ★1
　　　　　　　　iRow = iRow + 1
　　　　　　End If
　　　　　　If (Not rs.EOF) Then
　　　　　　　　With .Cells(iRow, 1)
　　　　　　　　　　.CopyFromRecordset rs
　　　　　　　　　　.Offset(, iCol - 1).Resize(rs.RecordCount) = iCnt
　　　　　　　　　　iCnt = iCnt + 1
　　　　　　　　End With
　　　　　　　　iRow = iRow + rs.RecordCount
　　　　　　End If
　　　　　　rs.Close
　　　　Next
　　　　iRow = iRow - 1
　　　　If (iRow > 1) Then
　　　　　　With .Range("A2", .Cells(iRow, iCol))
　　　　　　　　.Sort key1:=.Cells(1), order1:=xlAscending, _
　　　　　　　　　　key2:=.Cells(iCol), order2:=xlAscending, Header:=xlNo
　　　　　　End With
　　　　　　With .Cells(2, iCol).Resize(iRow - 1)
　　　　　　　　.FormulaR1C1 = "=SUM(RC2:RC[-1])" ' ★1
'　　　　　　　　.EntireColumn.Delete ' ★2
　　　　　　End With
　　　　End If
　　End With
End Sub

※　処理したいクエリ名を処理したい順に記述しておきます
>　　vA = Array("Q1", "Q12", "Q3", "Q9", "Q5", _

※ 各クエリで得られる項目数/項目/順は同じ・・・前提です

※ ★1 部分は現在有効ですが、
レコードセットとは別に「合計」列を追加したい場合のものです。
既に、「合計」列があるのであれば、
・★1 をコメントにして
・★2 を有効にしてください

※ 処理の過程で、この「合計」列部分に何個目のクエリでの出力か
値を埋め込んでいます。
メーカー名と、この値を使ってソートします。

ソートしたら、「合計」列の内容を B列 ～「合計」列前までを SUM するように

※ ファイルに保存する事はしていないので、追加してください
（現状、表示して終わりです）
また、ファイルに保存して終わりなら、.Visible = True は不要と思います。

お礼

ありがとうございます。
やりたいことはあっています。なるほど、エクセルに出力してから修正するという発想の転換ですね。

うまくいきそうであれこれやってみたのですが、ソートがエクセル上ではやりたいようにソールすることができません。
というか、順番を複雑にアクセスのほうで指定しているもので・・・そこをかえるとなると今までうまくいっていた集計の部分を訂正することになり数値の検証に時間がかかり・・・いままでのやり方でなんとかするか、この方法に切り替えるか・・・考えます。

でも、考え方としてとても参考になりました。ありがとうございました。

回答No.2

NotFound404

提示された例のように
テーブル１
メーカー名　　2014/6/1　　2014/6/2　　2014/6/3　　合計
-----------------------------------------------------
メーカーA　　　　　2　　　　1　　　　　　1　　　　　4
メーカーB　　　　　1　　　　1　　　　　　1　　　　　3

テーブル２
メーカー名　　2014/6/1　　2014/6/2　　2014/6/3　　合計
-----------------------------------------------------
メーカーA　　　　　5　　　　6　　　　　　7　　　　　18
メーカーB　　　　　2　　　　2　　　　　　2　　　　　6

メーカー名がテーブル１とテーブル２で『必ず』ペアで有る
というのが保証されるならやり易いのですが
片方のテーブルにしか存在しないメーカー名があるとなると
面倒です。

補足

出力されるメーカー名および日付は必ず一致するようにしてあります。

よろしくお願いします。

回答No.1

chie65536（@chie65535）

＞挿入していこうとすると、保存した値が消えて最後に関数を呼び出した結果しか表示されません

大モトの関数が「すべて上書き」で、挿入なんかしてないから、最後に関数を呼び出した結果だけになるのは当たり前。

挿入したいのであれば

・エクセルのシート中身を１つのテーブルに読み出す。

・そのテーブルに、テーブル１とテーブル２の追加分データを、挿入して追加する。

・データを足して追加したテーブルを、今まで使ってる関数を使って、エクセルのシートに「上書き」する。

と言う処理をしないといけません。

「エクセルのシート中身を１つのテーブルに読み出す。」と「そのテーブルに、テーブル１とテーブル２の追加分データを、挿入して追加する」の処理を、新しく作らないといけませんよ。

お礼

ありがとうございます。
エクセルの上書きのようなことはできないのですね。

いちど出力したいデータのテーブルをなんとか作りそれを出力という形にする必要があるということですね。

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