【VBA】フォルダ内の全てのテキストファイルから値をコピペするコードについて

「2018」というフォルダ内にあるすべてのcsvファイル（500個程度あります。1ファイルについて1レコードのファイルが大半で稀に３レコードぐらいあるファイルがあります）の1列目から3列目を、「集計」というシートのA列からC列にコピペするVBAを作りたいと思っています。

似たようなVBAを探して少し修正し、以下のとおり作りましたが、フォルダ中の一番上のファイルのレコードのみコピペされて新しいファイルが作られた状態で、エラーメッセージの400が表示されて止まってしまいます。

どのように直せばよいのか、どなたかご教示願います。


Sub test()
Const FolderPath As String = "C:\2018"
Dim Filename As String
Dim Sh0 As Worksheet, Sh As Worksheet
Dim c As Long

Set Sh0 = ActiveSheet
Filename = Dir(FolderPath & "\*.LOG")
Do Until Filename = ""
c = c + 1
Set Sh = Workbooks.Open(FolderPath & "\" & Filename).Sheets(1)
Sh.Columns(1).Copy Sh0.Columns(a)
Sh.Columns(2).Copy Sh0.Columns(b)
Sh.Columns(3).Copy Sh0.Columns(c)
Application.DisplayAlerts = False
Sh.Parent.Close
Application.DisplayAlerts = True
Filename = Dir()
Loop
End Sub

No.1 ベストアンサー 回答者： siffon9 回答日時： 2018/01/22 20:59

よくわかりませんが、こんな感じでしょうか。

Sub test()
Const FolderPath As String = "C:\2018"
Dim Filename As String
Dim Sh0 As Worksheet, Sh As Worksheet
Dim row As Long, baseRow As Long

Set Sh0 = ActiveSheet
Filename = Dir(FolderPath & "\*.LOG")
baseRow = 1
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do Until Filename = ""
Call Workbooks.OpenText(FolderPath & "\" & Filename, Comma:=True, StartRow:=1)
Set Sh = ActiveWorkbook.Sheets(1)
row = Sh.UsedRange.Rows.Count
Sh.Range(Cells(1, 1), Cells(row, 3)).Copy Sh0.Cells(baseRow, 1)
baseRow = baseRow + row
Sh.Parent.Close
Filename = Dir()
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

この回答へのお礼

ありがとうございます。
動きました。
しかし、コードを見ても以下の箇所が理解できなかったので教えていただけると幸いです。

このコードの意味が理解できませんでした。
Call Workbooks.OpenText(FolderPath & "\" & Filename, Comma:=True, StartRow:=1)

以下の部分は、一つずつのコードを見るとなんとなくわかるのですが、流れとして行っていることがわかりませんでした。それぞれのlogファイルを開いて貼り付ける処理が必要だと思うのですが、どこで行っているのでしょうか。（また、logファイルを一つコピペするたびにすでに貼り付けた部分をコピペしていたりするのでしょうか？）

Set Sh = ActiveWorkbook.Sheets(1)　→開いているブックの一番左のシートをShに入れる。
row = Sh.UsedRange.Rows.Count　→Shの値が入っている範囲の行を数え、その数をrowに入れる。
Sh.Range(Cells(1, 1), Cells(row, 3)).Copy Sh0.Cells(baseRow, 1)
baseRow = baseRow + row→Shのセル（1,1）からセル（row,3）までをコピーし、Sh0の値の入っている範囲の次に貼り付ける。
Sh.Parent.Close→（どういう意味でしょうか？）

お手数おかけして申し訳ありませんが、よろしくお願いいたします。

お礼日時：2018/01/24 14:44

No.3 回答者： siffon9 回答日時： 2018/01/25 15:11

No.1です。

> なぜcallを使っているのでしょうか？

無くても良いです、その場合は引数のカッコを削除してください。

> プロシージャをcallで呼び出して組み込もうとしたのですが、うまくいきませんでした。

ご提示のプロシージャのうち変数rowが、処理したログファイル数だと思います。
これをcallする側でプロシージャの引数として与える必要があります。

呼び出し側
call ステータス表示(処理したファイル数）

プロシージャ定義
Sub ステータス表示(row as long)
　略
End Sub
このあたりはVBAのプログラミングを勉強してみてください。


今回はプロシージャでなく本体に組み込んでしまった方が手っ取り早いと思います。
Sub test()
Const FolderPath As String = "C:\2018"
Dim Filename As String
Dim Sh0 As Worksheet, Sh As Worksheet
Dim row As Long, baseRow As Long
' ステータス表示用の変数宣言
Dim FileCount As Long, Count As Long

' 全LOGファイル数カウント
Filename = Dir(FolderPath & "\*.LOG")
Do Until Filename = ""
FileCount = FileCount + 1
Filename = Dir()
Loop

' コピー開始
Set Sh0 = ActiveSheet
Filename = Dir(FolderPath & "\*.LOG")
baseRow = 1
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do Until Filename = ""
Workbooks.OpenText FolderPath & "\" & Filename, Comma:=True, StartRow:=1
Set Sh = ActiveWorkbook.Sheets(1)
row = Sh.UsedRange.Rows.Count
Sh.Range(Cells(1, 1), Cells(row, 3)).Copy Sh0.Cells(baseRow, 1)
baseRow = baseRow + row
Sh.Parent.Close

' ステータス表示
Count = Count + 1
Application.StatusBar = "実行中..." & Left(String(Int(Count / FileCount * 10), "■") & String(10, "□"), 10)

Filename = Dir()
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True

' ステータス消去
Application.StatusBar = False
MsgBox "処理が終了しました。"

End Sub

この回答へのお礼

ありがとうございます。
親切に教えていただき、助かりました。

お礼日時：2018/01/26 20:57

No.2 回答者： siffon9 回答日時： 2018/01/24 19:45

No.1です。

動作を簡単に説明してみます。


> このコードの意味が理解できませんでした。
> Call Workbooks.OpenText(FolderPath & "\" & Filename, Comma:=True, StartRow:=1)

Workbooks.Openで拡張子がLOGのファイルを開いた場合はカンマ区切りのデータが個々のセルに入らない（全てA列に入ってしまう）のでこのようにしました。
Workbooks.OpenTextは、開くファイルがカンマ区切りのテキストデータであり、データが1行目から始まると明示してファイルを開いています。
これでLOGファイルをEXCELで開くと、そのBOOK（LOGファイル）がアクティブになるので

> Set Sh = ActiveWorkbook.Sheets(1)

で、アクティブなBOOK(LOGファイル)のシート1をShにいれます。
つまり、Shはコピー元のシートを意味することになります。

> row = Sh.UsedRange.Rows.Count
コピー元のデータ行数を取得

> Sh.Range(Cells(1, 1), Cells(row, 3)).Copy Sh0.Cells(baseRow, 1)
コピー元の1～row行目のA～C列を
コピー先(Sh0)のbaseRow行、A列を左上角とする四角エリアにコピーする。

> baseRow = baseRow + row
コピー先のbaseRow行からrow行分にデータをコピーしたので、次のLOGファイルをコピーする位置はbaseRow + row行目からになる
（例 最初のLOGファイルのデータを1～3行目にコピーしたとすると、次のLOGファイルは4行目～へコピーすることになる）

> Sh.Parent.Close
Shシートの親のブック（Workbooks.OpenTextで開いたLOGファイル）を閉じる


これでご理解いただけましたでしょうか。

この回答へのお礼

詳しくせつめいしていただいたありがとうございます。
ようやく理解できましたが、1点理解できない点がありました。

Call Workbooks.OpenText(FolderPath & "\" & Filename, Comma:=True, StartRow:=1)
とありますが、なぜcallを使っているのでしょうか？callとは、別のプロシージャを呼ぶためのものではないのですか？


また、もう1点、うまくいかなかった点があるので、教えていただけると幸いです。処理時間が1時間程度かかりそうなため、処理のステータスを表示させたいと思っています。
ネットで調べて以下のプロシージャをcallで呼び出して組み込もうとしたのですが、うまくいきませんでした。どのようにコードを書けばよいのか、教えていただけないでしょうか。

Sub ステータス表示()
Dim row As Long
Application.StatusBar = "実行中..." & Left(String(Int(row / 500 * 10), "■") & String(10, "□"), 10)
Application.StatusBar = False
MsgBox "処理が終了しました。"
End Sub

お礼日時：2018/01/25 14:16