ExcelVBA どこが間違えていますか?

質問

ExcelVBA 初心者です。
下記のようなプロシージャを人様の作ったのをコピーして作ったのですが意図したとおりになりません。どこが間違っているのか教えてください。
私の意図は選択したフォルダの中の全ファイル(Book)名をアクティブシートのＡ列に順番に表示したい、です。今は実行すると全部セルＡ１に表示されてしまって、最後の１つのファイル名しか分かりません。ファイルごとに別のセルに表示したいのです。よろしくお願いします。

Sub ファイル一覧()
Dim foldername As String
Dim filename As String
Dim i As Integer
Dim dlg_folder As FileDialog

Set dlg_folder = Application.FileDialog(msoFileDialogFolderPicker)

Folder_Dialog:
dlg_folder.Show

If dlg_folder.SelectedItems.Count <> 1 Then
Exit Sub
Else
foldername = dlg_folder.SelectedItems.Item(1)
MsgBox "選択したフォルダは " & foldername & " です。"
filename = Dir(foldername & "\*.xls", vbNormal)

If filename = "" Then
MsgBox "Excelファイルがありません。"
GoTo Folder_Dialog
End If
End If

Set dlg_folder = Nothing

Do While filename <> ""

For i = 1 To Workbooks.Count
Cells(i, 1).Value = filename
i = i + 1
Next i

filename = Dir()
Loop

MsgBox "フォルダ " & foldername & " の中のファイルはすべて表示されました。"

End Sub

あれ、なぜかインデントが無効になっています。

回答No.1

ki-aaa · ベストアンサー

全体的な処理の流れは理解できませんが、次の部分をこうしたらどうでしょうか。

Do While filename <> ""

For i = 1 To Workbooks.Count
Cells(i, 1).Value = filename
i = i + 1
Next i

filename = Dir()
Loop

を次のようにする

i = 1
Do While filename <> ""
Cells(i, 1).Value = filename
i = i + 1
filename = Dir()
Loop

お礼

ご回答感謝いたします。
ご教示の方法で意図したとおりの結果を得られました。
不思議なことにさっきまであなた様のご回答が表示されませんでした。No.２のかたのご回答がNo.１になっていました。時間を見るとあなた様の方が早かったのですね。当方ダイアルアップ接続のためでしょうか？？
No.２のかたへのお礼が先になってしまいました。申し訳ありません。また、教えてください。

回答No.2

imogasi

下記で動きました、ただフォルダの指定は直接にしました。拡張子（filenameのright３桁）をＸＬＳに絞ればエクセルブックだけに出来ます。
Sub ファイル一覧()
Dim foldername As String
Dim filename As String
Dim i As Integer

foldername = "c:\My Documents\"
MsgBox "選択したフォルダは " & foldername & " です。"
filename = Dir(foldername)
i = 1
Do While filename <> ""
Cells(i, 1).Value = filename
i = i + 1
filename = Dir()
Loop
MsgBox "フォルダ " & foldername & " の中のファイルはすべて表示されました。"
End Sub
・Dim dlg_folder As FileDialog でエラーになりました。（エクセル２０００）
・前半は理解不能。後半のＦｏｒで回し、ＤｏＷｈｉｌｅで回し、何故２重ループしないといけないのか判らない。
ただ私も初心者ですが。ＷｏｒｋＢｏｏｋはファイルで、
ブックの数だけ回したとして、なぜまたＤｏＷｈｉｌｅで
回すのでしょうか。

お礼

いつもお教えいただき感謝しています。
あなた様が初心者なら、私は超^３の初心者です。人様の作品なので私も良く分かりません。前半は任意のフォルダを選択する意味のようです。これも私の意図するところの１つです。

Do While filename <> ""
For i = 1 To Workbooks.Count
Cells(i, 1).Value = filename
i = i + 1
Next i
filename = Dir()
Loop
i = 1

の部分をご教示いただいた

Do While filename <> ""
Cells(i, 1).Value = filename
i = i + 1
filename = Dir()
Loop
に書き換えると私の意図したとおりになるようです。ありがとうございました。