回答編集履歴
1
修正
test
CHANGED
@@ -1,62 +1,62 @@
|
|
1
|
-
|
1
|
+
フォルダ内のファイルを順に処理するよう修正してみました。
|
2
|
+
|
3
|
+
(ファイル番号が2桁固定の前提で書いているので、そうでないなら直してください。)
|
2
4
|
|
3
5
|
|
4
6
|
|
5
7
|
```VBA
|
6
8
|
|
9
|
+
Option Explicit
|
10
|
+
|
11
|
+
|
12
|
+
|
7
|
-
Sub 抽出
|
13
|
+
Sub 抽出()
|
8
14
|
|
9
15
|
|
10
16
|
|
11
|
-
|
17
|
+
Dim fso As Object
|
12
18
|
|
13
|
-
|
19
|
+
Dim f As Object
|
14
20
|
|
15
|
-
|
21
|
+
Dim fileNo As String
|
16
22
|
|
23
|
+
Dim sBuf As String
|
17
24
|
|
25
|
+
Dim ws As Worksheet
|
26
|
+
|
27
|
+
|
28
|
+
|
29
|
+
Set fso = CreateObject("Scripting.FileSystemObject")
|
30
|
+
|
31
|
+
For Each f In fso.GetFolder(ThisWorkbook.Path).Files
|
32
|
+
|
33
|
+
If f.Name Like "ファイル名_*" Then
|
34
|
+
|
35
|
+
With f.OpenAsTextStream
|
36
|
+
|
37
|
+
sBuf = .ReadAll
|
38
|
+
|
39
|
+
.Close
|
40
|
+
|
41
|
+
End With
|
42
|
+
|
43
|
+
fileNo = Mid(f.Name, 7, 2)
|
44
|
+
|
45
|
+
Set ws = ThisWorkbook.Worksheets(fileNo)
|
46
|
+
|
47
|
+
Call シート書き込み(ws, sBuf)
|
48
|
+
|
49
|
+
End If
|
50
|
+
|
51
|
+
Next
|
52
|
+
|
53
|
+
|
18
54
|
|
19
55
|
End Sub
|
20
56
|
|
21
57
|
|
22
58
|
|
23
|
-
Sub
|
59
|
+
Sub シート書き込み(ws As Worksheet, sBuf As String)
|
24
|
-
|
25
|
-
|
26
|
-
|
27
|
-
Dim fileName As String
|
28
|
-
|
29
|
-
fileName = "ファイル名_" & fileNo
|
30
|
-
|
31
|
-
|
32
|
-
|
33
|
-
Dim ws As Worksheet
|
34
|
-
|
35
|
-
Set ws = ThisWorkbook.Worksheets(fileNo)
|
36
|
-
|
37
|
-
|
38
|
-
|
39
|
-
Application.ScreenUpdating = False
|
40
|
-
|
41
|
-
Dim fso As Object
|
42
|
-
|
43
|
-
Set fso = CreateObject("Scripting.FileSystemObject")
|
44
|
-
|
45
|
-
Dim path
|
46
|
-
|
47
|
-
path = ThisWorkbook.path 'カレントディレクトリを取得
|
48
|
-
|
49
|
-
Dim sBuf As String
|
50
|
-
|
51
|
-
'テキストのフルパスを指定
|
52
|
-
|
53
|
-
With fso.GetFile(FilePath:=path & "\" & fileName).OpenAsTextStream
|
54
|
-
|
55
|
-
sBuf = .ReadAll
|
56
|
-
|
57
|
-
.Close
|
58
|
-
|
59
|
-
End With
|
60
60
|
|
61
61
|
|
62
62
|
|