質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.97%

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

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 888

onionion

score 11

前提・実現したいこと

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

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

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

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

というものです。

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

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

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

該当のソースコード

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

Sub 転記()
Dim fP , fN as String
Dim Ln , Lp as Long

  fP = Range("A1").Value '1
  fN = Range("A2").Value '1
  Lp = Worksheets("あああ").Cells(Rows.Count, 5).End(xlUp).Row '2 実際はデータの始まる場所が1になるようにいくつかマイナスしています。
  Workbooks.Open a & "\" & b '1
 Ln = Workbooks(fN).Sheet1.Cells(Rows.Count, 2).End(xlUp).Row '2 ★エラーが発生するコード★

    If vbNo = MsgBox("ファイルパス:" & a & vbCrLf & "ブック名:" & b & vbCrLf & vbCrLf & "この場所に内容を転記しますか?", vbYesNo, "転記する/しない") Then Exit Sub

  '以降3 転記用のコード

end sub
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • stdio

    2019/03/28 17:17 編集

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

    キャンセル

回答 3

checkベストアンサー

+2

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

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

(追加のアドバイス)

指摘① 宣言文について

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

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

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

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

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

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

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


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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/29 16:32

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

    キャンセル

+2

「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 というプロパティがない”というだけのような気がします。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/28 18:41

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

    キャンセル

  • 2019/03/29 16:30

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

    キャンセル

+1

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/03/29 16:29

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.97%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる