VBA フォルダ内のブックの再呼び出し

Question

例えば "test.xls"なるブックの以下のマクロの For Each ～ Nextで、"World"という別のフォルダにある複数のブックを順次呼び出してコピー操作をさせ、下記の①の個所で test.xls を呼び出した後に、②の所で再び "World"のフォルダに戻りたいのですがその方法が判りません。
御教示の程、宜しくお願いします。

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("D:\World")
For Each objFile In objFolder.Files
     If Right(objFile.Name, 4) = "xlsm" Then
          Set Wb = Application.Workbooks.Open(objFile) 
          name_Nation = ActiveWorkbook.Name    ' ブック名から国名抽出、表示
          ThisWorkbook.Activate   ' ①
- 省略 -
          ' ②
- 省略 -
Next

めぐみん_ · Accepted Answer

No.11です。

自分なりに考えたNo.10をコードにした物です。

Sub megu()
Dim objFSO As Object
Dim objFolder As Object
Dim WB_t As Workbook, WB_w As Workbook
Dim WS As Worksheet
Dim r_t As Range, r_w As Range, objFile

Application.ScreenUpdating = False

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("D:\World")
Set WB_t = Workbooks.Open("D:\TEST	est.xls")

With WB_t.Worksheets("都市人口") '
If .Range("A1").Value = "" Then
Set r_t = .Range("A1")
Else
Set r_t = .Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
End With

For Each objFile In objFolder.Files
If Right(objFile.Name, 4) = "xlsm" Then
r_t.Value = "【" & objFSO.GetBaseName(objFile) & "】"
Set r_t = r_t.Offset(1)

Set WB_w = Workbooks.Open(objFile)

For Each WS In WB_w.Worksheets
If WS.Range("B1").Value >= 500000 Then
r_t.Value = WS.Range("A1").Value
Set r_t = r_t.Offset(1)
End If
Next
WB_w.Close False
End If
Next
' WB_t.Close True '上書き保存(test.xls)
Application.ScreenUpdating = True

Set r_t = Nothing
Set WB_w = Nothing
Set WB_t = Nothing
Set objFolder = Nothing
Set objFSO = Nothing
End Sub

－－－－－－

一応test.xlsは閉じるようにはしてません。
そちらで調整願います。

めぐみん_ · Answer

No.12です。

No.11のコードですが。

Range型の変数：r_t は
Set WB_t = Workbooks.Open("D:\TEST	est.xls")
With WB_t.Worksheets("都市人口")
～
によりBookとWorkSheetは固定されてますので、『戻る』と表現されてた行動は必要なくなります。
あとは書き込む毎に .Offset(1) で１行下にセットし直していきます。

最初にセルA1に値がある場合と無い場合でSet方法が変わる位です。

めぐみん_ · Answer

No.10です。

よくよく読み返してみるとNo.10での回答は間違いのようでした。
都市_人口.basは標準モジュールを外に出しているんですね。

１．都市_人口.basを実行。
２．test.xlsブックを開く。BookはSetステートメントにより変数にセット。
３．フォルダ “国別”内にあるBookを順次取得。（For Each objFile In objFolder.Files ~ Next)
４．取得したBook名をtest.xlsのA列に書き込む。
５．取得したBookを開く。BookはSetステートメントにより変数にセット。
６．このBookの全てのシートを検索。
７．シートのセルB1の値が50万以上であればセルA1の値をtest.xlsのA列に追記する。
８．全てのシートを調べたらBookを閉じる。
９．３．に戻って次のBookを取得する。・・・をBook数分繰り返す。

みたいな事なのでは？

なので質問文にあった「戻る」云々ってのは『やりたい事』に該当しないように感じました。

めぐみん_ · Answer

No.8です。

私も
＞今回の質問は、④のステップをどのように記述するかという事です。
この件について疑問がありますね。

そもそも

＞ “都市_人口.bas” を使って転記される

によって既に転記されている訳ではないのですか？
それを何故また転記する必要があるのか？

仮に最初の転記で国名と都市名があり都市名が邪魔ならそれを消せば済む話のように思います。
『都市_人口.bas』が一体何者なのかはわかりませんが。
本来なら④については『都市_人口.bas』がやってくれているんじゃないのでしょうか？

何故

＞その結果、A列の1行目から次のように国名と都市名が追記される事になる。

で書き出されたはずの物に更にデータを加えるのか不明です。
それに

＞④ test.xls の “都市人口” ワークシートの A列1行目に国名をペースト

のワークシート（ “都市人口” ）のA1にペーストとは、その後のデータは以下に続くとしたら
やっぱ『都市_人口.bas』の実行により抽出されたデータ（どこのシートにあるのかわかりませんが）を
フィルタで国名だけ出して“都市人口” ワークシートの A列1行目以下に貼り付ければ良いようにしか思えない。

それに『都市_人口.bas』の抽出結果があるなら、質問文のFileSystemObjectの.GetFolder("D:\World")の
For Each objFile In objFolder.Files ~ Next 自体も不要にしか感じないのですが・・・

一体『都市_人口.bas』これは何なのでしょうか？
そして書き出された＜その結果、A列の1行目から次のように国名と都市名が追記される事になる。＞を
有効に利用すべきとは思いますけど。

ただしこれは実際の状況を把握できない状態での私の見解であり、把握されているからこそこれらを
質問者さんが否定する事については何も言いません。
でも私から明確な回答を出す事は不可能と思って下さい。
（多分全てが目の前にあれば補足の手順は無視してでも、やりやすいと思う方法を再検討するでしょう。）

fujillin · Answer

No7です

補足を読みましたが、
＞今回の質問は、④のステップをどのように記述するかという事です。
セルへ値を記入するには、
　Workbook.Worksheet.Cells(row, column).Value = 値
という形式で記入できることはご存知のことと思います。

＞（既にデータがある場合は、その下に追記）。
の部分がわからないということなのでしょうか？
方法はいくつかありますし、No5の例でも、縦に順に記入してゆくので、そのような方法のうちのひとつを用いています。

No5で用いている方法は
1）書き込む対象位置を記憶する変数（Range型)を用意
2) 初期値として、対象シートのA1セルをセット
3）値を記入する際は、変数の示す位置に記入。
　 記入したら、変数の示す位置を1行下げておく（行番号を+1する）
という方法です。

他の方法としては
・ 記入する行番号を示す変数（Long型）を用意し、初期値は1にしておく
　 その行番号位置に値を記入（Cells(i,1)など）し、変数を+1する
ような方法や
・ 記入する都度、End(xlUp).Rowで記入済みの最終行を求め、次の行に記入する
などが代表的な方法かと思います。
どれを用いても、同じ結果となります。（最後の方法のみ、最初の記入時に注意が必要）

＞今回の質問は、④のステップをどのように記述するかという事です。
とのことなので、④に関してだけを書きましたけれど、他の処理の方がはるかに難易度は高いように思いますけれど・・・

めぐみん_ · Answer

No.6です。

＞①の個所で test.xls（"World"とは別のフォルダに在る）を開き、そこでの処理を済ませた後に
＞もう一度 "World" というフォルダに戻り、そのフォルダ下のブックを順次操作したいという事です。

問題は、①でtest.xlsにobjFileから国名を書き出した後で、同じobjFile を②で使用すれば『フォルダに戻る』と言う表現は消えそうに思いますが、
国名の転記とその後に行なわれる処理とではobjFile で選ばれた（多分Book変数：Wb?）物とは違うBookになるのでしょうか？
例えば拡張子が違うというならIf文で"xlsm"か否かで処理を変えられそうですけど？

fujillin · Answer

No5です

＞今回の質問の目的は対象フォルダ内のブック名から国名を抽出するだけではなく、その後、
＞これとは異なるフォルダにある test.xls にそのデータを書き込み、
No4の例示で方法を示したつもりですが……No4でも一覧が作成されるブックは「異なるブック」になっているはずです。
（どこにあるのかわからないので、とりあえず「ActiveSheet」にしてあります）

このあたりの意味をご理解いただければ、、
＞再び対象フォルダ内の同じブックに戻って
＞（VBA素人の感覚としてはこんな表現になってしまうのですが）
のような発想からも脱却できるものと想像します。

例として、一覧の作成をActiveSheetではなく、特定のブックの特定のシートに出力したいとするならば、No4のコードの7行目の
＞Set rng = ActiveSheet.Cells(1, 1)
の部分を
　Set rng = Workbooks("test.xls").Worksheets(1).Cells(1, 1)
のような要領で設定しておけば、「test.xlsの最初のシートのA列」に記入されるようになります。
（ブック「test.xls」が開かれていることが前提です。開かれていないとエラーになります）
仮に、その際に事前にA列をクリアするなら、上記の後で
　rng.EntireColumn.ClearContents
としておけばクリアされます。
（その他のループ内のコードは変えなくても、一覧の出力先が変わります）

＞他のデータの取り込み操作を施したいのです。
「他のデータ」がブックを開かないと取得できない種類のものであるなら、ループの中で
　対象のブックを開き → データを取得して → ブックを閉じる
ような処理を行うことになるものと想像します。

＞そのような機能を実現できるマクロを教えて頂ければ幸いです。
マクロ化は可能だろうと想像しますけれど、ご質問の内容が抽象的なので、上記のような文章での回答しかできません。
無理やり書いても、ブックを開きたいのなら、「Workbooks.Open(～～) みたいな感じ」というのがせいぜいでしょう。
（質問者様の頭の中では十分具体的なのかも知れませんが、文章に示された情報だけからでは、とてもコード化まではできません）

めぐみん_ · Answer

No.1~2です。

①と②が同じ"World"という別のフォルダにある複数のブックを対象にしてはいるけど
やる処理が違うってなら、

For Each objFile In objFolder.Files
'～処理～
Next

を２回（別々に）行なえばよいのでは？

もし１回で行ないたいなら

＞' ②
＞- 省略 -

ここの内容次第だと思います。

fujillin · Answer

No4です

連投失礼。
今見たら、Withを使う必要がありませんでしたね。(^^;)

Sub Sample()
Dim rng As Range, fName As String
Const fPath = "D:\World" '←指定フィルダのパス

fName = Dir(fpath & "\*.xlsm")
ActiveSheet.Columns(1).ClearContents
Set rng = ActiveSheet.Cells(1, 1)

Do While fName <> ""
　rng.Value = fName
　Set rng = rng.Offset(1)
　fName = Dir()
Loop

End Sub

fujillin · Answer

No3です

＞一度 "World" というフォルダに戻り、～～
手で操作する場合は、ブックやシートをアクティブにしたり、あるいはエクスプローラでファイルやフォルダを選択したりという操作をしますが、VBAの場合は、一旦、変数等に対象オブジェクトを取得しておけば、以降はそのオブジェクトを通じて直接操作が可能になります。
そういう意味では、「戻る」とか「戻らない」という考え方自体が存在しません。

実際になさりたいことが、よくわかりませんが・・・
＞ActiveWorkbook.Name ' ブック名から国名抽出
とあるので、ファイル名がそのまま取得したい対象になっているとするなら、わざわざブックを開く必要もありません。（ブックを開かなくてもファイル名は取得できますので）

例えば、『指定フォルダ内にある「〇〇.xlsm」というファイル名の一覧を作成する』という処理を行うとするなら、以下のような方法でも可能です。ご参考までに。
（FileSystemObjectを利用しても、もちろん可能ですが、その必要もなさそうなので）

※ 実行時のアクティブシートのA列に書き出しますが、最初にA列をクリアしますのでご注意ください。

Sub Sample()
Dim rng As Range, fName As String

Const fPath = "D:\World" '←指定フィルダのパス

fName = Dir(fPath & "\*.xlsm")
With ActiveSheet
　.Columns(1).ClearContents
　Set rng = .Cells(1, 1)

Do While fName <> ""
　　rng.Value = fName
　　Set rng = rng.Offset(1)
　　fName = Dir()
　Loop
End With