複数ブックの特定セル列を新しいブックへ集約したい

質問

お願い致します。
下記の操作をVBAで行いたいと思っています。

下記画像サンプルにあるように、複数ブック（同じ階層）に同じ配列のデータがあります。
こちらの、黄色く色付けしている（実際は色付けされていません）複数列を新たなブックの１シートへ抽出したいと考えています。
列のデータ量は、各ブックごとに違っていますので、11列のデータを基準に抽出できればと思います。

複数シートを1つのシートに纏めCSVで出力するとこまではVBAで出来たのですが、
複数ブックのシート指定列を抽出して1つの違うブックへ纏めることが出来ないでいます。

どうぞご教授よろしくお願いいたします。

回答No.4

Prome_Lin · ベストアンサー

「VBScript」による回答です（「Windows限定」です）。

このプログラムの特徴は、これから読み込みたい「csv」ファイルの存在するフォルダにプログラムファイルを放り込んで、ダブルクリックするだけです。

同じフォルダに「MergeCsv.xlsx」という結果ファイルを残します。

最後に「Finished!」と表示しますので、「OK」を押して、終了してください。

以下のプログラムを、メモ帳かテキストエディタに貼り付け、「～.vbs」という名前で保存します。

「～」の部分は、何でもかまいませんが、「.vbs」の部分は、必ず半角です。

できたプログラムファイル（「～.vbs」ファイル）を、これから処理したい「csv」ファイルの存在するフォルダに放り込んで、ダブルクリック（「シングルクリック」→「Enter」の方が確実）するだけす。

Option Explicit
Dim a, bk, c, cv, ex, f, gf, i, j, r, sh, so
Set so = CreateObject("Scripting.FileSystemObject")
Set gf = so.GetFolder(".")
Set ex = CreateObject("Excel.Application")
ex.Application.DisplayAlerts = False
ex.Visible = False
Set bk = ex.Workbooks.Add()
Set sh = bk.Worksheets(1)
c = Array(6, 11, 12, 13, 16)
r = 0
For Each f In gf.Files
If LCase(so.GetExtensionName(f.Name)) = "csv" Then
Set cv = so.OpenTextFile(gf & "\" & f.Name, 1)
Do Until cv.AtEndOfStream
a = Split(cv.ReadLine, ",")
r = r + 1
For j = 0 to UBound(c)
sh.Cells(r, j + 1).Value = a(c(j) - 1)
Next
Loop
cv.Close
Set cv = Nothing
End If
Next
bk.SaveAs(gf & "\MergeCsv.xlsx")
bk.Close
ex.Quit
Set sh = Nothing
Set bk = Nothing
Set ex = Nothing
Set gf = Nothing
Set so = Nothing
MsgBox("Finished!")

簡単な説明です。

Option Explicit

「厳密に」というような意味ですが、気にしないでください。

Set so = CreateObject("Scripting.FileSystemObject")

ファイルやフォルダを扱えるようにしていますが、今回は特に、「csv」というテキストファイルを扱うのにも必要です。

Set gf = so.GetFolder(".")

プログラムファイル（「～.vbs」ファイル）の存在するフォルダ、したがって、これから処理したい「csv」ファイルの存在するフォルダを取得しています。

Set ex = CreateObject("Excel.Application")

エクセルを扱えるようにしています。

ex.Application.DisplayAlerts = False
ex.Visible = False

「上書きしますか」などと聞いてこないように、また、エクセルを表示しません。

Set bk = ex.Workbooks.Add()
Set sh = bk.Worksheets(1)

ワークブックを新規作成し、一番左端のシートを「sh」にセット。

c = Array(6, 11, 12, 13, 16)

読み込む列を配列変数に入れています。

「c(0) = 6」、「c(1) = 11」、「c(2) = 12」、「c(3) = 13」、「c(4) = 16」としているのと同じです。

r = 0

行カウント用変数の初期化。

For Each f In gf.Files

プログラムファイルの存在するフォルダ内のすべてのファイルを１つずつ処理。

If LCase(so.GetExtensionName(f.Name)) = "csv" Then

拡張子が「csv」なら、

Set cv = so.OpenTextFile(gf & "\" & f.Name, 1)

そのファイルを開いています。

Do Until cv.AtEndOfStream

そのファイルの終端まで処理。

a = Split(cv.ReadLine, ",")

１行ずつ読み込みながら、区切り記号を使って、配列に格納しています。

たとえば、読み込んだ１行が「1,2,3」の場合、「a(0) = 1」、「a(1) = 2」、「a(2) = 3」としています。

r = r + 1

１行カウント。

For j = 0 to UBound(c)

「UBound()」は、配列変数の添え字（「()」内の数字）の最大値を返します。

今、「c(4) = 16」の「4」がそれにあたります。

したがって、「For j = 0 to 4」と記述しているのと同じです。

sh.Cells(r, j + 1).Value = a(c(j) - 1)

実際にセルに書き出しています。

書き出す列は「c()」に入っていて、実際の読み込んだ値は「a()」に入っています。

Next
Loop

を、繰り返しています。

cv.Close
Set cv = Nothing
End If

テキストファイルを閉じています。

Next

を、すべてのファイルに対して行っています。

bk.SaveAs(gf & "\MergeCsv.xlsx")
bk.Close

同じフォルダ内に「MergeCsv.xlsx」という名前で保存して、閉じています。

ex.Quit

エクセルそのものを終了しています。

Set sh = Nothing
Set bk = Nothing
Set ex = Nothing
Set gf = Nothing
Set so = Nothing
MsgBox("Finished!")

あとは、終了処理ののち、「Finished!」と表示しています。

回答No.3

tsubu-yuki

あ、失礼。書き損じです。

その２の手順２～３は見事に重複です。
手順２の「貼り付け」は手順３に回りますね。
私が提示したサンプルは同時にやっちゃってますが(笑)。

ところで、１番さんもおっしゃる通り、
「現状はどのように書いているのか」を
見られたくない部分はフェイクを入れつつでも提示した方が良いです。
それがあると、より具体的なアドバイスをもらえるかもしれません。
前後の流れから書き方が変わることもありますからね。

回答No.2

tsubu-yuki

ひとまず・・・

考えやすくて書きやすい手段その１
・（お作りのコードで）ＣＳＶ全部まとめる
・要らない行を削除する　※
（※）
　　列（行）削除は後ろからが鉄則です。
　　Columns("N:O").Delete
　　Columns("G:J").Delete
　　という具合。

考えやすくて書きやすい手段その２
・ＣＳＶ開く
・必要列コピー貼り付けする　※
・新規ブックの末尾に貼る
（※）
　　Sheets("Sheet1").Range("F:F,K:M,P:P").Copy Sheets("Sheet2").Range("A1")
　　な感じでできますよ。

この辺りなら考えやすくて早くないですかね？
注）効率が良い、見た目かっこいいなどは別の話です。

回答No.1

imogasi

この質問課題を解決するために、どんな勉強をしたのか。
回答をコピペするために、丸投げしたいのか？
ーーー
３つの課題があると思う。
＜ブック＞
（１）多分同一フォルダ内の指定名の１ブックを都度開きリスト分だけ繰り返す。
＜シート＞
（２）１つのブックを開いたら、その中の２つ以上のシートを１つのシートにまとめる。
複数のシートはどうゆう風に選択するのか。あるものすべてか？
＜１シートにまとめる＞
（３）指定列（全列のうち、どういう風に、まとめる対象列を決めるのか）を
まとめる。
（あ）持ってくる列データの最終列
（い）まとめる列の現在の最終行
とも、Sub test01()
lr = Range("B100000").End(xlUp).Row
MsgBox lr
End Sub
を参考に、応用を考えれば簡単。Ｒａｎｇｅの前にブックとシート名を限定しておく。
ーー
これらの（１）、（２）、（３）のオブジェクトを、どう選択するのか、全部を対象にするのか、除外するものがあるのか、質問に書いてないから、現状では、回答例は出せない。
質問するときのポイントは、どういう選択をすればよいのか書かないと。
名前とか属性とかあって、名前による方が処理が簡単。
ＶＢＡを少しやったら、普通はその点で苦労するのはすぐわかるだろ。それを書いてないのはＶＢＡの経験が少ないのに、それ以上のことをやろうとしているだろう。またやってみて自分の行き詰まった点を認識してない証拠。
ーー
後出回答のために、上記の点の説明を追加したらどう。