別ブックを指定して変数を設定したい

Question

前提・実現したいこと

現在、他のブック（以降ブックAとします）で入力した内容を管理台帳に転記するプロシージャを作成しております。
コードはブックA側に記載しております。
構造としては

１．ブックAのシート1のセル2つに、管理台帳の「ファイルパス(変数fP)」と「名前(変数fN)」を入力する欄があり、
　fP & "\" & fN のブックを開く
  例：セルA1に「D:\User\Desktop」、セルA2に「新しいブック.xlsx」と記載がある場合、
	D:\User\Desktop\新しいブック.xlsxを開く

２．管理台帳の中でB行に入力のある最終段（変数Ln）と、ブックAから転記するデータ数＝行数(変数Lp)を取得

３．転記するコードを列Lnから下にLp回数分繰り返す

というものです。

Lnは管理台帳側＝別ブックを指定して変数を設定しようとしていますが、
エラー438「オブジェクトはこのプロパティまたはメソッドをサポートしていません」が発生し、設定できません。
解決法をご教示いただければ幸いです。

発生している問題・エラーメッセージ

エラー438「オブジェクトはこのプロパティまたはメソッドをサポートしていません」が発生し、設定できません。

該当のソースコード

関係ないと思われる部分は省略・簡略化しています。

Sub 転記()
Dim fP , fN as String
Dim Ln , Lp as Long
   
  fP = Range("A1").Value '１
  fN = Range("A2").Value　'１
  Lp = Worksheets("あああ").Cells(Rows.Count, 5).End(xlUp).Row　'２　実際はデータの始まる場所が1になるようにいくつかマイナスしています。
　 Workbooks.Open a & "\" & b '１
　Ln = Workbooks(fN).Sheet1.Cells(Rows.Count, 2).End(xlUp).Row　'２　★エラーが発生するコード★
    
    If vbNo = MsgBox("ファイルパス：" & a & vbCrLf & "ブック名：" & b & vbCrLf & vbCrLf & "この場所に内容を転記しますか？", vbYesNo, "転記する/しない") Then Exit Sub

　　'以降３　転記用のコード

end sub

Comments

[stdio]

fnをCstr関数で型変更したら出来ないかな？
それか、fN = Range("A2").ValueではなくfN = Range("A2").Textにするかだね。
私の場合、sheetに関してはしっかりと探すコードを組んでいるから、違ってたらごめんよ。

Answer 1

「Ln = Workbooks(fN).Sheet1.Cells(Rows.Count, 2).End(xlUp).Row」は、

「Ln = Workbooks(fN).worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row」
なのではないでしょうか？　

エラーは、”workbookには sheet1 というプロパティがない”というだけのような気がします。

Comments

[jawa]

内容が一部被ってしまいすみません。
私も回答の追記部分を書いていて思いましたが、これが原因の可能性が高いですね。

[onionion]

ご指摘の通りでした。非常に単純なミスで恐縮ですが、ご指摘いただけてないとずっと理由がわからず困っていたかもしれません。大変助かりました。ありがとうございました。

Answer 2

コード内の変数aとbはそれぞれ変数fPとfNの誤記ではないかと思います。
この誤記が原因で、そもそもブックを開けていないからWorkbooksにも見つからないのではないでしょうか？

まずはこの誤記を修正したコードで動作確認してみてください。

（追加のアドバイス）

指摘①　宣言文について

Dim fP , fN as String
のような宣言文の書き方は、変数fPと変数fNをそれぞれString型で宣言しているように見えますが、実際にはfNだけString型で宣言され、fPは型指定なし(=Variant型)で宣言されていることになります。

今回のコードでは特に影響はないと思いますが、ご注意ください。

指摘②　開いたブックの取得について

提示いただいたコードではWorkbooks.Openでブックを開き、同じファイル名でブックを探して処理をしようとしています。

しかし、Workbooks.Openはブックを開いた際にそのブックを戻り値として返してくれています。
ですので、

' Workbooks.Open a & "\" & b '１
'Ln = Workbooks(fN).Sheet1.Cells(Rows.Count, 2).End(xlUp).Row　'２　★エラーが発生するコード★

Dim wb As Workbook
Set wb = Workbooks.Open fP & "\" & fN '１
Ln = wb.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row　'２　★エラーが発生するコード★

のように処理すれば、わざわざファイル名でブックを探す必要がなく、確実に開いたブックを対象に処理することができます。

なおSheet1というシートの指定方法は、Sheet1を単独で使用すれば「そのマクロが書かれているブックのSheet1」を参照することができますが、ワークブックのプロパティではないためWorkbooks(fN).Sheet1という使い方はできません。

指定のブックの第１シートを取得したいのであればWorkbooks(fN).Worksheets(1)といった書き方をしましょう。

以上、参考になれば幸いです。

Comments

[onionion]

ご回答ありがとうございます。
コンマで一括で「As String」みたいな宣言はできないんですね、今までずっと誤った書き方をしていました。ご指摘ありがとうございました。指摘②についてもありがとうございました。非常に参考になりました。

Answer 3

Workbooks(fN).Sheet1

の「Sheet1」というのは、シートを「オブジェクト名」で指定する書き方です。
Sheet1というオブジェクト名のオブジェクト（今回の件の場合はシートそのもの）が
存在しないとそういうエラーが出るようです。

以下のコードで、開いたファイルの各シートの「シート名」と「オブジェクト名」を
調べてみて、確認してみてはいかがでしょうか？

結果は「イミディエイトウィンドウ」に出力されます。

Option Explicit

Sub test()
    Dim ws As Worksheet         '各シート
    Dim sFullPath As String     '開くファイルのフルパス
    Dim wb As Workbook          '開いたブック
    Dim ix As Long

    '開くファイルのフルパスの生成
    sFullPath = Join(WorksheetFunction.Transpose(ThisWorkbook.Worksheets("Sheet1").Range("A1:A2").Cells), "\")

    'ファイルの存在確認
    If Len(Dir(sFullPath)) = 0 Then
        MsgBox "ファイルが見つかりません。"
        Exit Sub                'プログラムを抜ける
    End If

    'ブックを開く
    Set wb = Workbooks.Open(sFullPath)
    
    '開いたブックの各シートのシート名とオブジェクト名を調べてみる
    For Each ws In wb.Worksheets
        Debug.Print "シート名：" & ws.Name, "オブジェクト名：" & ws.CodeName
    Next
End Sub

シート名でシートを指定するにしても、
オブジェクト名でシートを指定するにしても、
存在が担保されない（ユーザーが勝手に名前を変えたり削除することが可能）
ので、ちゃんと想定するシートが存在するかの存在確認をしたら、
エラーの出ないコードになります。
左から数えた数でシートを指定することも可能ですが、
想定するデータが指定されたシート上に想定通り並んでいるかも、確実ではないかも知れませんね。
なので、まずはその辺のチェックを厳密にするところから始めてみては、
いかがでしょうか？

Comments

[onionion]

回答ありがとうございます。わざわざ検証用のサンプルコードまで作成いただき、お手数をおかけしました。
問題自体はcoco_bauerさんのご指摘がピンポイントの解決策になりましたが、問題の発見手順として大変参考になりました。今後同じような問題が起こった際に手法を利用させていただこうと思います。