none
Excel VBA シートコピー時の挙動について RRS feed

  • 質問

  • 下記、長文となりますが
    Excel VBAの処理に関しましてご質問させて頂きます。

    【主な処理の概要】
        特定のブックの内容(ブック内のシートは複数)をコピーし、
        新しいブックへ複製する処理。

    【主な処理の流れ】
        1.ブック(B1)内のシート(S1)をコピー
          → シートをCopyメソッドでコピーし、
             新しいブック(B2)が作成される
        2.シート(S2)をコピーし、
          ブック(B2)へペーストする。
        3.シートの数だけ2.を繰り返す

    【★問題】
        新しいブックへの複製処理で、
        ブック内にシートが複数作成されるのでは無く、
        1ブック1シートでバラバラに複製されてしまう。
       
        〇問題が発生する環境
          OS:Windows 10
          Excelバージョン:2016
         
          OSのBuildバージョンなど、
          その他詳細は不明です。
         
          ※現状、上記の環境以外では発生しておりません。

    【★考察・予測】
        1.下記【主なVBAの処理】で①の新規作成時(1)でシートをCopyメソッドでコピーした際、
          新しいインスタンスがブック名"BookX"(Xは数値)で作成される。
        2.続けて、上記VBAの処理で②のExcelのバージョンが2003以降の場合の条件(1)で、
          1.で作成したブック名の判定処理でFalseになり、ブック名を取得できない。
         
        →上記のロジックの通りだと、ブックはシート数分作成され、
          【★問題】通りの挙動となります。
         
          ②のExcelのバージョンが2003以降の場合の条件(1)は、
            A.拡張子が.xlsまたは.xlsxのものではないこと
            B.そのブックが互換モードでは無いこと(Excel8CompatibilityModeのプロパティ)★
            C.ブック名の始まりが"Book"であること
         
            になりますが、そのうちA.とC.は条件としては正しく(Trueになる)、
            残りのBが不正(Falseになる)になっているのではと予測しました。

    【★ご質問】
        ※上記の考察と予測を含めてのご質問となりますが、
          Copyメソッドでシートをコピーして新規にブックが作成された場合、
          互換モードで作成されることはあるのでしょうか。
          また、その場合どのような条件で作成されるかなどをご教授頂けたら幸いです。
          それ以外にも、何かしら糸口なるような情報がありましたらご連携頂けると助かります。
         
          〇以下、試したこと
          ・Excelの保存形式を互換モード(Excel97-2003ブック(*.xls))に設定しておく。
            →再現NGでした。

    既存システムの内容で安易にロジック変更やログを
    仕込めない状況下で、予測の範囲内を超えない部分が
    多々あることをご了承ください。

    細かな部分のご質問となりますが、
    よろしくお願いいたします。


    2019年5月16日 0:48

すべての返信

  • 【主なVBAの処理】
        シートを複製の際、初めは新規でブックが作成され、
        作成後はその作成したブックの情報を取得し、
        シートを追加していく処理になっています。

        ①シートをコピーする処理部分
            下記の処理で、新しいブックへの複製処理を行っています。
            シートの数だけ、下記の処理が走るようになっています。

            ~敬称略~

            With Workbooks("コピー元ブック名").Sheets("対象のシート名")

            bname = DecideBook '②ブック名を取得するメソッド の呼び出し

            If bname = "" Then
                .Copy '新規作成時(1)
            Else
                .Copy After:=Workbooks(bname).Sheets(Workbooks(bname).Sheets.Count) '追加コピー時(2)
            End If

            ~敬称略~

        ②ブック名を取得するメソッド
            既存で開いているエクセル名を検索し、
            "Book"で始まる(=Copyメソッドで新規に作成したもの)ブック名を
            取得しています。

            Function DecideBook()
                Dim WBK     As Workbook
                Dim bname   As String
               
                For Each WBK In Workbooks
                    If IsExcelVersionOver2003 = True Then '起動しているExcelのバージョンが2003以降かを判定(IsExcelVersionOver2003 は独自メソッド)
                        'Excelのバージョンが2003以降の場合の条件(1)
                        If LCase(Right(WBK.Name, 5)) <> ".xlsx" And LCase(Right(WBK.Name, 4)) <> ".xls" And WBK.Excel8CompatibilityMode = False And Left(WBK.Name, 4) = "Book" Then bname = WBK.Name
                    Else
                        'Excelのバージョンが2003以下の場合の条件(2)
                        If LCase(Right(WBK.Name, 4)) <> ".xls" And Left(WBK.Name, 4) = "Book" Then bname = WBK.Name
                    End If
                Next WBK
                DecideBook = bname
                ThisWorkbook.Activate
            End Function

    2019年5月16日 0:49
  • Array を使うと複数の Worksheet を含む Sheets コレクションを取得できます。
    注意点として複数の Worksheet オブジェクトを一度にまとめて扱う場合は
    Worksheets  コレクションではなく Sheets コレクションであるということです。
    (Worksheets コレクションに代入しようとするとエラーになります)

    また、複数のブックの Worksheet を Sheets コレクションとして取得はできません。
    必ず同じブックの Worksheet である必要があります。
    なお、Worksheet のコピーはコピー元のブックは非表示でもいいですがコピー先の
    ブックが非表示だとエラーになります。

    Sub test()
        Dim sh As Sheets
        ' "Sheet1", "Sheet2", "Sheet3" の Worksheet オブジェクトを持つ
        ' Sheets コレクションを取得する
        Set sh = Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
        ' 新規ブックを作成して最初の Worksheet の次にコピーする
        sh.Copy , Workbooks.Add.Worksheets(1)
    End Sub
    

    2019年5月16日 6:17
  • 堀内さん、こんにちは。

    貴兄がアップされたスクリプトを基に、次のようなスクリプトを組んで実行してみた結果を報告します。1つのブックの複数のシートすべてを新しいブックにコピーするものです。

    Sub copyMain()
    Dim i As Integer
    Dim iMax As Integer
    Dim bName As String
      iMax = Workbooks("testOriginal.xlsx").Sheets.Count
      For i = 1 To iMax
        With Workbooks("testOriginal.xlsx").Sheets(i)
          bName = DecideBook '②ブック名を取得するメソッド の呼び出し
          If bName = "" Then
            .Copy '新規作成時(1)
          Else
            .Copy After:=Workbooks(bName).Sheets(Workbooks(bName).Sheets.Count) '追加コピー時(2)
          End If
        End With
      Next i
    End Sub

    Function DecideBook()
    Dim Wbk   As Workbook
    Dim bName As String
     
      For Each Wbk In Workbooks 'Excelのバージョンが2003以下の場合の条件(2)は省略

            If LCase(Right(Wbk.Name, 5)) <> ".xlsx" And LCase(Right(Wbk.Name, 4)) <> ".xls" And Wbk.Excel8CompatibilityMode = False And Left(Wbk.Name, 4) = "Book" Then bName = Wbk.Name
      Next Wbk
      DecideBook = bName
      ThisWorkbook.Activate
    End Function

    結果は、Windows7 + Excel2013 の場合も、 Windows10 + Excel2016 の場合も、あらたに作られた1つのブックにコピー元のブックのシート(小生のお試しでは3シート)がすべてコピーされました。

    なので、システムのせいではないような気がしますが、どうでしょうか。バージョンによる処理区分に関するif文を省略したので、そこら辺が問題なのでしょうか。

    ご参考まで。


    2019年5月16日 10:50
  • ご返信ありがとうございます。

    現状は.Copyでコピーした際(上記の .Copy '新規作成時(1))の処理で

    互換モードでコピーされる可能性があるかが気になっている所です。

    コピー元のブックが互換モードの場合は、Copy 実行時に互換モードでコピーされることは認識済みです。

    記載が漏れており申し訳ありませんでしたが、本課題環境下ではコピー元のブックは

    拡張子が”.xlsx"であり、互換モードではない認識です。

    (コピー元のブックが読み込み後に互換モードになることはあるのでしょうか?)

    また、コピー先のブックが互換モードになる条件(上記のコピー元のブックを除いて)

    があるのかが気になる所です・・・。

    上記、複数シートコピーのロジックのご連携ありがとうございます。

    互換モードの原因が解決できない場合は、代替案として参考にさせて頂きます。

    2019年5月20日 11:17
  • ご返信ありがとうございます。

    >結果は、Windows7 + Excel2013 の場合も、 Windows10 + Excel2016 の場合も、
    >あらたに作られた1つのブックにコピー元のブックのシート(小生のお試しでは3シート)がすべてコピーされました。

    上記、確認ありがとうございます。
    やはり環境によって、挙動が異なるようですね。

    上記、記載が出来ておらず申し訳ありませんが、
    こちらの環境下では、読込み元のブックは.xlsx(=互換モードではない)で
    実行しており、なぜ互換モードになるのかが気になる所でした・・・。
    2019年5月20日 11:24
  • XL2010でちょっとテストしただけの報告ですが・・

    コピー元が [互換モード] だと、コピー先ブック(※1)も [互換モード] になりますね。

    ※1 コピー先が新規ブックの場合のみで、既存ブックの場合は除く

    コピー元が [互換モード] かどうかについてですが、xls形式のブックを開くと当然 [互換モード] になっています。

    そして、この状態で[名前を付けて保存] から [xlsm形式] にして保存しなおしても、 [互換モード] は継承されたままであり、この [xlsm形式のブック] からコピーしたブックも[互換モード] になりました。

    ところが、[xlsm] 形式にして保存しなおしたブックを一旦閉じてから開きなおすと、[互換モード] ではなくなっています。

    つまり、[互換モード] かどうかは、拡張子から一概には判断できず、例え同じブックであっても、操作フローによって異なる場合があるのだと思いました。

    なので、

    WBK.Excel8CompatibilityMode = False

    の部分は、

    WBK.Excel8CompatibilityMode = ThisWorkbook.Excel8CompatibilityMode

    にすべきかと。

    ----------------------------------

    > Copyメソッドでシートをコピーして新規にブックが作成された場合、互換モードで作成されることはあるのでしょうか。

    > また、その場合どのような条件で作成されるかなどをご教授頂けたら幸いです。


    ご質問への回答(になるかどうかわかりませんが)として、コピー元が [互換モード] ならコピー先も [互換モード] になるようです。

    なので、コピーする前に、コピー元の [互換モード] 状態を確認してみてはどうでしょうか?

    • 回答の候補に設定 Takumi_Q 2019年5月23日 16:12
    • 回答の候補の設定解除 Takumi_Q 2019年6月16日 17:40
    2019年5月21日 7:25
  • 堀内さん、こんばんは。

    前回の小生の返信は、貴兄のご質問に対してトンチンカンな回答であったかもしれないと反省しております(;xlsxファイルのコピー結果を報告していました。)。申し訳ありませんでした。

    論点は、1)Copyされて新たに作られたブックのワークシートが互換モードであるかどうか、と言う事と、特に、2)「 Copyメソッドでシートをコピーして新規にブックが作成された場合に、互換モードで作成されることはあるのでしょうか。」という事であると理解しました。

    1)のCopyされて新たに作られたブックのワークシートが互換モードであるかどうかについて報告します。

    xls互換モードのブック(既に開かれていてタイトルバーに互換モードと表示されているブック。次の段落で同じ。)の3つのシートを1つのブックに「.Copy After:=Workbooks(bName).Sheets(Workbooks(bName).Sheets.Count) '追加コピー時(2)」でコピーした場合 ;どのワークシートを表示した場合もタイトルバーには「Book1-Excel」と表示されるのみで、「互換モード」の文字はない。ファイル>情報 を見ても、互換モードの表示はない。以上から、このブックのシートは互換モードではないのではないか。

    xls互換モードのブックの3つのシートを3つのブックに1シートづつ「.Copy '新規作成時(1)」でコピーした場合 ;どのブックのタイトルバーも「Book1-Excel」「Book2-Excel」「Book3-Excel」と表示されるのみで、「互換モード」の文字はない。同じく、ファイル>情報 を見ても、互換モードの表示はない。以上から、これらのブックのシートは互換モードではないのではないか。

    以上は、Windows10 + Excel2016 のxlsmファイルのマクロの実行結果です。

    2)の「 Copyメソッドでシートをコピーして新規にブックが作成された場合に、互換モードで作成されることはあるのでしょうか。」については、小生の試行の限りでは、ない、ということになりますが、絶対にないのか、と言われれば、絶対にないとは断言はできません。貴兄は2019年5月20日11:17の投稿で「コピー元のブックが互換モードの場合は、Copy 実行時に互換モードでコピーされることは認識済みです。」と記されているのですが、それはどのようなことからそのように認識されたのか、教えていただけると嬉しいです。もし、そうであるとすると、上記の1)の結果で互換モードと表示されていないけれども、実は互換モードでコピーされたものであり、互換モードなのでしょうか。

    ※ちなみに、2019年5月21日 7:25に投稿されたminmin312さんも「コピー元が [互換モード] だと、コピー先ブック(※1)も [互換モード] になりますね。※1 コピー先が新規ブックの場合のみで、既存ブックの場合は除く」と書かれていますから互換モードになるのかもしれません。小生が上記環境でVBAでコピーした場合は既述の通り互換モードの表示はありませんでした。念のため、手動で、「移動またはコピー」からシートを新しいブックにコピーした場合もタイトルバーに互換モードの表示はなく、ファイル>情報 を見ても、互換モードの表示はありませんでした。食い違っていますが、動作環境の違いによるのかもしれません。

    なお、互換モードか否かを判断する手掛かりは、タイトルバーや「ファイル>情報」以外にどうやって判断されているのか、教えていただけると嬉しいです。質問に対して質問するのはルール違反かもしれませんが、どうぞよろしくお願いします。不勉強で申し訳ありません。

    2019年5月23日 15:29
  • 横からすみません。
    >互換モードか否かを判断する手掛かりは、タイトルバーや「ファイル>情報」以外にどうやって判断されているのか、教えていただけると嬉しいです。

    コードに使われていますが、
    WorkbookオブジェクトのExcel8CompatibilityModeプロパティで判定できます。
    Excel8CompatibilityModeプロパティは、そのブックが互換モードで開かれているときTrueを返します。
    Debug.Print ActiveWorkbook.Excel8CompatibilityMode
    2019年5月23日 16:56
  • Takumi_Qさん、こんばんは。ご教示ありがとうございます。

    堀内さん、こんばんは。

    Takumi_Qさんから教えていただいた判定法で、再度、今回は、Windows7+Excel2013とWindows10+Excel2016の2つの環境で、vbaのマクロでコピーを試行して判定した結果を報告します。

    1.Windows7 + Excel2013 の場合

     元の互換モードのブックの3シートを、1ブックに1シートづつコピーする場合も、1ブックに3シートコピーする場合も、互換モードのブックとなりました。

    2.Windows10 + Excel2016 の場合

     同じ元の互換モードのブックの3シートを、1ブックに1シートづつコピーする場合も、1ブックに3シートコピーする場合も、こちらは互換モードではないブックとなりました。

    何なんでしょうね。

    以上で、小生の試行できる範囲での試行のご報告とさせていただきます。

    2019年5月24日 12:16
  •         Function DecideBook()
                Dim WBK     As Workbook
                Dim bname   As String
               
                For Each WBK In Workbooks
                    If IsExcelVersionOver2003 = True Then '起動しているExcelのバージョンが2003以降かを判定(IsExcelVersionOver2003 は独自メソッド)
                        'Excelのバージョンが2003以降の場合の条件(1)
                        If LCase(Right(WBK.Name, 5)) <> ".xlsx" And LCase(Right(WBK.Name, 4)) <> ".xls" And WBK.Excel8CompatibilityMode = False And Left(WBK.Name, 4) = "Book" Then bname = WBK.Name
                    Else
                        'Excelのバージョンが2003以下の場合の条件(2)
                        If LCase(Right(WBK.Name, 4)) <> ".xls" And Left(WBK.Name, 4) = "Book" Then bname = WBK.Name
                    End If
                Next WBK
                DecideBook = bname
                ThisWorkbook.Activate
            End Function

    ちょっと気になったのはこの方法では Book* という名前のワークブックを探して
    その名前を取得しようとしているようですが複数の新規ワークブックがあった場合、
    Workbooks コレクションえ該当するワークブックの内、一番最後にあるものの
    名前になると思います。
    新規ワークブックを手動で行ってたり、このマクロと連携できない処理でやってたり
    するのでなければ基本的には Workbooks コレクションから探すのではなく、
    Workbooks.Add などで新規作成したときにそのオブジェクトを取っておいて
    利用した方がいいと思います。

    アクティブなワークブックの全ワークシートを新規のワークブックにコピーするなら
    以下のようにするといいかもしれません。

    Sub test()
        Dim wb As Workbook
        Dim ws As Worksheet
        For Each ws In Worksheets
            If wb Is Nothing Then
                ' ループの最初はコピー先ワークシートを指定しない
                Set wb = SheetCopy(ws)
            Else
                ' 2 回目以降は指定する
                SheetCopy ws, wb.Worksheets(wb.Worksheets.Count)
            End If
        Next
    End Sub
    
    Function SheetCopy(ByVal FromWs As Worksheet, Optional ByVal ToWs As Worksheet) As Workbook
        If ToWs Is Nothing Then
            ' コピー先のワークシートが指定されていないときは新規ブックを作成する
            FromWs.Copy
            ' 新規ワークブックを作成すると自動的にそのブックがアクティブになるので
            ' アクティブなワークブックを返す
            Set SheetCopy = ActiveWorkbook
        Else
            ' コピー先ワークシートが指定されているときはそのワークシートの後ろにコピーする
            FromWs.Copy , ToWs
            ' コピー先のワークシートのワークブックを返す
            Set SheetCopy = ToWs.Parent
        End If
    End Function
    
    2019年5月24日 15:00