[[20211009114725]]　『WorkbookとWorksheetの指定(VBA)』（秋）　ページの最後に飛ぶ

『WorkbookとWorksheetの指定(VBA)』（秋）

WorkbookとWorksheetをそれぞれ変数に格納して作業したいのですが、
エラーになってしまい、Bookが開きません。教えて下さい。
（実行時エラー438 オブジェクトは、このプロパティまたはメソッドをサポートしていません。）となります。

マクロのコードが書いてあるファイルをwb1としたいです。今開いています。
もう一つ別のファイルを開きたいです。wb2としたい。
wb2のファイルのパスはwb1の分析シートのC11セルに入っています。
wb2を開く部分でエラーが出てしまいます。
最終的には、wb2を開いて、sheet1にあるA-E列を、wb1の元データシートのA-D列に貼り付けたいです。
ファイルを開く時に、Workbooks.Open(wb1.ws1.Range～のような指定方法は合っていますか？

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    Application.ScreenUpdating = False

    Set wb1 = ThisWorkbook
    Set ws1 = Worksheets("分析")
    Set ws2 = Worksheets("元データ")
    Set wb2 = Workbooks.Open(wb1.ws1.Range("C11").Value)

    wb2.Sheets("Sheet1").Range("A:E").Copy
    wb1.wb2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues

宜しくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

＞Workbooks.Open(wb1.ws1.Range～のような指定方法は合っていますか？
そこはあってます。

 wb1.wb2.Cells

↑が間違ってます。
↓のようにしたかったのでは、ありませんか？

 ws2.Cells

（もこな２） 2021/10/09(土) 12:08

もこな２様
wb1.ws2.Cellsです。ミスです。

そこを直しても、下記でエラーになり、開きません。分かりますでしょうか？

   Set wb2 = Workbooks.Open(wb1.ws1.Range("C11").Value)

下記だと開きます。

    Set wb2 = Workbooks.Open(wb1.Worksheets("分析").Range("C11").Value)

また、ご指摘いただいたwb1.ws2.Cells～部分も、wb1.ws2～だと動かなくて、下記のようにシート名を指定すると動きました。

    wb1.Sheets("元データ").Cells(1, 1).PasteSpecial Paste:=xlPasteValues

更なる疑問ですみませんが、
↓
wb1をThisworkbookにすると、この後Wb2を開いた後にそっちがアクティブになってしまうように思うのですが、thisworkbook以外でwb1の今指定しているファイル名を変数に格納する方法も併せて教えて頂きたいです。
（秋） 2021/10/09(土) 12:17

あぁごめんなさい

 × Set wb2 = Workbooks.Open(wb1.ws1.Range("C11").Value)
 ◯ Set wb2 = Workbooks.Open(ws1.Range("C11").Value)

ですね。
ws1にブックの情報も含まれているので二重指定になっちゃっているのが問題ですね。

（もこな２） 2021/10/09(土) 12:23

状況を整理しましょう。

マクロのあるブック「ThisWorkbook」 → wb1
wb1内の「分析」シート「wb1.Worksheets("分析")」 → ws1
このws1のC11セルにファイルパスが入力されいる。→ ws1.Range("C11").Value
このファイルを開く → Workbooks.Open(ws1.Range("C11").Value)

C11に正しいパスが入っているなら、下記でブックは開くはずです。

    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("分析")
    Set ws2 = wb1.Worksheets("元データ")
    Set wb2 = Workbooks.Open(ws1.Range("C11").Value)
（hatena） 2021/10/09(土) 12:25

もこな2様、hatena様

大変ありがとうございます。勉強になります。
お二人に教えて頂いたのを組み合わせて、無事に下記でやりたい事が再現できました。
二重指定になってしまうのですね。
そして、wsの変数setの際に、wb1.を入れるというのも大変勉強になりました。

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    Application.ScreenUpdating = False

    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("分析")
    Set ws2 = wb1.Worksheets("元データ")
    Set wb2 = Workbooks.Open(ws1.Range("H1").Value)

    wb2.Sheets("Sheet1").Range("A:E").Copy
    ws2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
（秋） 2021/10/09(土) 12:46

私ならこうですかね（スマホからなのでタイプミスがあるかもしれま。）

 sub さんぷる（）
     with ThisWorkbook
       with Workbooks.Open(.Worksheets("分析").Range("H1").Value)
         .Sheets("Sheet1").Range("A:E").Copy
       end with

       .Worksheets("元データ").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
     end with
 end sub

（もこな２） 2021/10/09(土) 13:12

Sub test()

    Dim sFullName As String
    Dim rngFrom As Range
    Dim rngTo As Range
    Dim wbBase As Workbook

    Let sFullName = ThisWorkbook.Worksheets("分析").Range("H1").Value
    Set rngTo = ThisWorkbook.Worksheets("元データ").Range("A1").Cells
    Set wbBase = Workbooks.Open(sFullName)
    Set rngFrom = wbBase.Sheets("Sheet1").Range("A:E").Cells

    rngFrom.Copy
    rngTo.PasteSpecial Paste:=xlPasteValues

    wbBase.Close False
End Sub

操作したいのはセル範囲なので、
セル範囲を変数に入れてみてはいかがでしょうか？

（まっつわん） 2021/10/09(土) 13:36

もこな2様、まっつん様
ありがとうございます！！
なるほど、色々なアプローチがあるのですね。
その時その時に最適な変数格納を考えるといい事、大変勉強になります。
まだまだ勉強始めたばかりで応用がきかないですが、教えて頂いた事例を今後活用してみます。

ありがとうございました。
（秋） 2021/10/09(土) 13:45

コメント返信：

[ 一覧(最新更新順) ]

[[20211009114725]] 『WorkbookとWorksheetの指定(VBA)』（秋） ページの最後に飛ぶ

[[20211009114725]]　『WorkbookとWorksheetの指定(VBA)』（秋）　ページの最後に飛ぶ