VBAで複数のエクセルファイルを開いて、情報を集約（転記）することをしたいです

簡単なVBAは作ったことがありますが、基本的には初心者です。
グーグル検索、参考書を見ながらやっています。

・やろうとしていたこと。
① 個社別のエクセルファイル名を取得。　←これはなんとか出来ました
② そのファイルを開く。　←ここがうまくいきません
③ 特定のセルの情報を取得。

・詳細
用意しているエクセルファイルが２つ。
（A）とりまとめ用のエクセルファイル
（B）個社別の情報が入力されているエクセルファイルが複数（個社別分）
このファイルを「20190322」というフォルダに保存しています（作成対象一覧のB3セルに「20190322」と入力）

Aのファイルには2つのシートがあります。
（1）シート名：ファイル名一覧表
Bのファイル名をマクロで取得してくる　←　これはなんとか出来ました
（2）シート名：作成対象一覧
2のシートに記載のファイル名と一致するファイルを開いて必要な情報（セル2つ分）をもってきたい

BのファイルのB12セル、H12セルに記載の情報を
AのファイルのA6セル以降とB6セル以降に転記したいと考えていますが、
「実行時エラー　1004　アプリケーション定義またはオブジェクト定義のエラーです。」
というエラーメッセージが表示されます。

（最終的には、この転記された情報を社内申請用のテンプレートに転記していきたいのですが
、まだそこまでいけません…）

○ 作成したプロシージャ
Sub ファイル名一覧表()
Application.ScreenUpdating = False
Dim i As Long, bookname As String
With Sheets("ファイル名一覧表")
.Columns(1).ClearContents
bookname = Dir(ThisWorkbook.Path & "" & _
Sheets("作成対象一覧").Range("B3") & "*")
Do While bookname <> ""
i = i + 1
.Cells(i, 1) = bookname
bookname = Dir()
Loop
End With
End Sub

Public Sub 相対ヘッダー用情報一覧()
Application.ScreenUpdating = False
Dim i As Long, r As Long
Dim f As Worksheet
Set f = Sheets("ファイル名一覧表")
With Sheets("作成対象一覧")
.Range("A5").CurrentRegion.Offset(1, 0).ClearContents
For i = 1 To f.Cells(Rows.Count, 1).End(xlUp).Row
r = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Workbooks.Open ThisWorkbook.Path & "" & _
.Range("B3") & f.Cells(i, 1)
.Cells(r, 1) = Range("B12")
.Cells(r, 2) = Range("H12")
ActiveWorkbook.Close savechanges:=False
Next
End With
End Sub

初歩的な問題なのかとも思いますが、何かアドバイスをいただけると助かります。
ご質問の仕方などお作法的な問題もあるかとも思いますが、ご容赦ください。

行動規範の内容に同意します

回答2件

ベストアンサー

エラーの原因は、単純にフォルダ名とファイル名の間に""がないためブックが開けていないからだと思います。

その他に指摘するとすれば、開いたブックはアクティブブックになるとは思いますが、アクティブブックやアクティブシートに頼ったコーディングは避けた方がいい、というところでしょうか。

現在のコードではWorkbook.Openの戻り値を取っていませんが、このメソッドは開いたブックを戻り値として返しています。

VBA
1Dim wb As Workbook
2Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & .Range("B3") & "\" & f.Cells(i, 1))
3
4.Cells(r, 1) = wb.Worksheets(1).Range("B12")
5.Cells(r, 2) = wb.Worksheets(1).Range("H12")
6
7wb.Close savechanges:=False