[Excel ADO]合計額の挿入は可能ですか？

質問

Excel2007のADOを使ってCSVファイルのデータを集計する方法を習得中です

ある業務アプリからエクスポートしたデータなのですが、
売上伝票データ：
伝票日付、伝票番号、取引先名、明細番号、商品名、数量、単価、金額、取引区分、納入先コード
納入先コードテーブル：
納入先コード、納入先名

取引区分が0なら売上、1なら入金（入金伝票の場合納入先コードがNULL）

これを集計して売上明細表をつくるとして、
伝票毎に伝票合計額、および納入先名を挿入するようなことが
SQL文だけで可能でしょうか？

回答No.4

ushi2015 · ベストアンサー

続きです。
[uriage#csv].[取引区分]=1 "
mSql = mSql & "Group By [uriage#csv].[伝票日付], "
mSql = mSql & " [uriage#csv].[伝票番号], "
mSql = mSql & " [nonyu#csv].[納入先名];"

rs.Open mSql, cn, adOpenForwardOnly, adLockReadOnly
Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset rs

cn.Close

Set cn = Nothing
Set rs = Nothing

With Worksheets("Sheet1")
.Range("A1").CurrentRegion.Sort _
Key1:=.Columns("A"), Key2:=.Columns("B"), Key2:=.Columns("J"), Header:=xlYes
.Columns("J").ClearContents

For i = .Range("A1").CurrentRegion.Rows.Count To 2 Step -1
If .Cells(i, 3) Like "取引先*" Then
.Cells(i, 2) = ""
ElseIf .Cells(i, 3) Like "納入先*" Or _
.Cells(i, 3) Like "消費税*" Then
.Cells(i, 2) = ""
.Cells(i, 1) = ""
ElseIf .Cells(i, 3) = "振込" Then
.Cells(i, 1) = ""
Else
.Cells(i, 1) = ""
End If
Next
For i = 2 To .Range("A1").CurrentRegion.Rows.Count
If .Cells(i, 2) = .Cells(i - 1, 2) Then
.Cells(i, 2) = ""
End If
Next
End With
End Sub

お礼

お礼が遅くなり、大変申し訳ありません。

示していただいたコードで、動作確認できました。
趣旨としては、取引先名、納入先名、その他明細、入金明細を
それぞれSQLで抽出し、シート上に並べて貼り付け
「ソート」列他でソートすることで伝票順に並べ替える、
という理解でよろしかったでしょうか？

こういうやり方もあるんですね。大変参考になりました。
本当にありがとうございました。

できれば、SQL文のみ（UNIONを使えばできる？）で
やるとどうなるか、お示しいただけると勉強になります。
（無論自分でも調べるつもりですが）

回答No.3

ushi2015

こんにちは
UNIONとか使えるといいのですが、まだそこまで試す時間がないので、
Sub test1()
Dim cnStr As String
Dim mSql As String
Dim cn As Object
Dim rs As Object
Dim i As Long

'CSVファイル2つを専用のフォルダに入れておく
Const csvFol = "C:\CSV"

cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & csvFol & ";" & _
"Extended Properties=""Text;HDR=YES;FMT=Delimited"""

Set cn = CreateObject("ADODB.Connection")
cn.Open cnStr

With Worksheets("Sheet1")
.Activate
.Range("A1").CurrentRegion.Offset(1).ClearContents
End With

mSql = ""
mSql = mSql & "SELECT [uriage#csv].[伝票日付] AS 日付, "
mSql = mSql & " [uriage#csv].[伝票番号] AS '伝票No', "
mSql = mSql & " '取引先 '+[uriage#csv].[取引先名] AS '商品名・入金内容', "
mSql = mSql & " '' AS '数量', "
mSql = mSql & " '' AS '単位', "
mSql = mSql & " '' AS '単 価', "
mSql = mSql & " '' AS '売　　上', "
mSql = mSql & " '' AS '伝票計', "
mSql = mSql & " '' AS '入金', "
mSql = mSql & " [uriage#csv].[伝票番号] AS 'ソート' "
mSql = mSql & "FROM [uriage#csv] "
mSql = mSql & "Group By [uriage#csv].[伝票日付], "
mSql = mSql & " [uriage#csv].[伝票番号], "
mSql = mSql & " [uriage#csv].[取引先名];"

Set rs = CreateObject("ADODB.Recordset")
rs.Open mSql, cn, adOpenForwardOnly, adLockReadOnly

For i = 1 To rs.Fields.Count
Cells(1, i) = Replace(rs.Fields(i - 1).Name, "'", "")
Next
Range("A2").CopyFromRecordset rs
rs.Close

mSql = ""
mSql = mSql & "SELECT [uriage#csv].[伝票日付] AS 日付, "
mSql = mSql & " [uriage#csv].[伝票番号] AS '伝票No', "
mSql = mSql & " [uriage#csv].[商品名] AS '商品名・入金内容', "
mSql = mSql & " [uriage#csv].[数量] AS '数量', "
mSql = mSql & " [uriage#csv].[単位] AS '単位', "
mSql = mSql & " [uriage#csv].[単価] AS '単 価', "
mSql = mSql & " [uriage#csv].[金額] AS '売　　上', "
mSql = mSql & " '' AS '伝票計', "
mSql = mSql & " '' AS '入金', "
mSql = mSql & " [uriage#csv].[伝票番号] AS 'ソート' "
mSql = mSql & "FROM [uriage#csv] "
mSql = mSql & "WHERE [uriage#csv].[取引区分]<>0 "

rs.Open mSql, cn, adOpenForwardOnly, adLockReadOnly
Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset rs
rs.Close

mSql = ""
mSql = mSql & "SELECT [uriage#csv].[伝票日付] AS 日付, "
mSql = mSql & " [uriage#csv].[伝票番号] AS '伝票No', "
mSql = mSql & " '振込' AS '商品名・入金内容', "
mSql = mSql & " '' AS '数量', "
mSql = mSql & " '' AS '単位', "
mSql = mSql & " '' AS '単 価', "
mSql = mSql & " '' AS '売　　上', "
mSql = mSql & " '' AS '伝票計', "
mSql = mSql & " [uriage#csv].[金額] AS '入金', "
mSql = mSql & " [uriage#csv].[伝票番号] AS 'ソート' "
mSql = mSql & "FROM [uriage#csv] "
mSql = mSql & "WHERE [uriage#csv].[取引区分]=0 "
mSql = mSql & "Group By [uriage#csv].[伝票日付], "
mSql = mSql & " [uriage#csv].[取引先名], "
mSql = mSql & " [uriage#csv].[伝票番号], "
mSql = mSql & " [uriage#csv].[金額];"

rs.Open mSql, cn, adOpenForwardOnly, adLockReadOnly
Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset rs

rs.Close

mSql = ""
mSql = mSql & "SELECT [uriage#csv].[伝票日付] AS 日付, "
mSql = mSql & " [uriage#csv].[伝票番号] AS '伝票No', "
mSql = mSql & " '納入先 '+[nonyu#csv].[納入先名] AS '商品名・入金内容', "
mSql = mSql & " '' AS '数量', "
mSql = mSql & " '' AS '単位', "
mSql = mSql & " '' AS '単 価', "
mSql = mSql & " '' AS '売　　上', "
mSql = mSql & " SUM([uriage#csv].[金額]) AS '伝票計', "
mSql = mSql & " '' AS '入金', "
mSql = mSql & " [uriage#csv].[伝票番号] AS 'ソート' "
mSql = mSql & "FROM [uriage#csv] LEFT JOIN [nonyu#csv] "
mSql = mSql & " ON [uriage#csv].[納入先コード] = [nonyu#csv].[納入先コード] "
mSql = mSql & "WHER

回答No.2

ushi2015

こんにちは
数件分で良いので、
売上伝票データと納入先コードテーブルのテストデータと、処理後の結果を提示して下さい。

明細と、この集計を並べて出すという事ですが、位置関係がよく分かりません。

補足

お言葉に甘えまして、補足させていただきます。
売上データ及び納入先コード表は以下の通りです。

uriage.csv
伝票日付,伝票番号,取引先名,明細番号,納入先コード,商品名,単位,数量,単価,金額,取引区分
270901,00000664,X社,1,0071,商品A,個,4,2250,9000,1
270901,00000664,X社,2,0071,商品B,個,4,1750,7000,1
270901,00000664,X社,3,0071,消費税,,0,0,1280,2
270903,00000739,X社,1,0067,商品C,式,1,0,0,1
270903,00000739,X社,2,0067,商品D,個,1,0,0,1
270915,00000360,X社,1,,振込,,0,0,4320,0
270915,00000363,X社,1,,振込,,0,0,78840,0
270915,00000363,X社,2,,振込,,0,0,135540,0
271022,00000831,Y社,1,0090,商品B,式,1,10000,10000,1
271022,00000831,Y社,2,0090,消費税,,0,0,800,2
271022,00000832,Y社,1,0144,商品D,個,1,2000,2000,1
271022,00000832,Y社,2,0144,消費税,,0,0,160,2
271105,00000447,Y社,1,,振込,,0,0,2160,0
271110,00000459,Y社,1,,振込,,0,0,10800,0

nonyu.csv
納入先コード,納入先名
0067,本社
0071,M支店
0084,N支店
0090,本社
0109,S支店
0144,T支店

結果の提示をどのようにすれば明確にお伝えできるか悩みましたが、
CSV化してみましたのでご確認ください

日付,伝票No,商品名・入金内容,数量,単位,単　価,売　　上,伝票計,入金
270901,,取引先 X社,,,,,,
,664,商品A,4,個,2250,9000,,
,,商品B,4,個,1750,7000,,
,,消費税,,,,1280,,
,,納入先 M支店,,,,,17280,
270903,739,取引先 X社,,,,,,
,,商品C,1,式,0,0,,
,,商品D,1,個,0,0,,
,,納入先 本社,,,,,0,
270915,,取引先 X社,,,,,,
,360,振込,,,,,,4320
,,取引先 X社,,,,,,
,363,振込,,,,,,78840
,,振込,,,,,,135540
271022,,取引先 Y社,,,,,,
,831,商品B,1,式,10000,10000,,
,,消費税,,,,800,,
,,納入先 本社,,,,,10800,
271022,,取引先 Y社,,,,,,
,832,商品D,1,個,2000,2000,,
,,消費税,,,,160,,
,,納入先 T支店,,,,,2160,
271105,,取引先 Y社,,,,,,
,447,振込,,,,,,2160
271110,,取引先 Y社,,,,,,
,459,振込,,,,,,10800

趣旨としては、伝票の前に取引先名、伝票の後に納入先名および伝票計をつけたいということです
（飽くまで理想です）

回答No.1

ushi2015

こんにちは
ADOに参照設定しておいて、2つのCSVファイルを専用のフォルダにまとめておいて、
Sub test()
Dim cnStr As String
Dim mSql As String
Dim cn As Object
Dim rs As Object

'CSVファイル2つを専用のフォルダに入れておく
Const csvFol = "C:\CSV"

cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & csvFol & ";" & _
"Extended Properties=""Text;HDR=YES;FMT=Delimited"""

mSql = mSql & "SELECT [売上伝票データ#csv].[伝票番号], "
mSql = mSql & "[納入先コードテーブル#csv].[納入先名], "
mSql = mSql & "SUM([売上伝票データ#csv].[金額]) "
mSql = mSql & "FROM [売上伝票データ#csv] LEFT JOIN [納入先コードテーブル#csv] "
mSql = mSql & " ON [売上伝票データ#csv].[納入先コード] = [納入先コードテーブル#csv].[納入先コード] "
mSql = mSql & "WHERE [売上伝票データ#csv].[取引区分]=0 "
mSql = mSql & "Group By [売上伝票データ#csv].[伝票番号], [納入先コードテーブル#csv].[納入先名];"

Set cn = CreateObject("ADODB.Connection")
cn.Open cnStr
Set rs = CreateObject("ADODB.Recordset")
rs.Open mSql, cn, adOpenForwardOnly, adLockReadOnly

Range("A2").CopyFromRecordset rs

cn.Close

Set cn = Nothing
Set rs = Nothing
End Sub
で、どうでしょうか？

お礼

早速のご回答、ありがとうございます。

実行してみたところ、
まさしく伝票毎の集計ができました。
これだけでも悩んでいたので助かりました

ただ、説明が足りず申し訳なかったのですが、
以下のように、明細と、この集計を並べて出すことは
可能かをお聞きしたいです

No.　商品名 数量 単価 金額
------------------------------------
1 　　商品A　2 　100 　200
　　商品B 1 　500 　500
　　　　　　　　計 700
　　　納入先　Z社

ダメそうですかね・・・