Q&A
Workbooks("コピー先ファイル.xlsm")は存在しているのですか？
前提・実現したいこと
自分のパソコンで使う用にVBAでマクロを作成しました。
他のパソコンでも同じマクロを使いたいとなりアドインで設定したところ
「インデックスが有効範囲にありません。」とエラーが出ました。
ネットで調べたり自力でできることはやってみたのですが、エラーの解消ができず行き詰まりました。
こちらで初めて質問しますので、情報の不足等でお手数をお掛けするかもしれませんが
エラーの解消方法をご教示いただければと思います。
どうぞよろしくお願いいたします。
具体的には以下です。
【マクロの内容】
社内ネットワーク上にあるシステムから開くエクセルファイルの「"コピー元シート"」から
特定のセル（複数）の内容をコピーし、「"コピー先ファイル.xlsm"」（自分のパソコン上）の「sheet1」にペーストする。
（コードはソースコード部分に記載）
【自分のパソコン】
個人用マクロブックにマクロを書き、リボンに実行ボタンを作ってマクロを割り当てる。
コピー元ファイルとコピー先ファイルを開き、
実行時はコピー元ファイルをアクティブにし、実行ボタンを押す。
問題なく動く。
環境：Windows10 Home Excel2013
【他のパソコン】
マクロを書いたファイル「コピー先ファイル.xlam」をAddinフォルダに入れ、
エクセルを開いてアドインを有効にする。
リボンに実行ボタンを作ってそのアドインを割り当てる。
デスクトップのフォルダ内に「コピー先ファイル.xlsm」を置く。
コピー元ファイルとコピー先ファイルを開き、
実行時はコピー元ファイルをアクティブにし、実行ボタンを押す。
エラーになる。
環境：Windows10 Home Excel2016
発生している問題・エラーメッセージ
「インデックスが有効範囲にありません。」と出ます。 デバッグ画面では以下の部分（コピー動作の最初の行）でハイライトになります。 Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C2") = ActiveWorkbook.Worksheets("コピー元シート").Range("E2")
該当のソースコード
VBA
1Sub サンプル() 2 ActiveWorkbook.Worksheets("コピー元シート").Unprotect 3 4 Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C2") = ActiveWorkbook.Worksheets("コピー元シート").Range("E2") 5 Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C9") = ActiveWorkbook.Worksheets("コピー元シート").Range("E6") 6 Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C6") = ActiveWorkbook.Worksheets("コピー元シート").Range("H5") 7 8 Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("G3:G14").Value = ActiveWorkbook.Worksheets("コピー元シート").Range("J26:J37").Value 9 10End Sub 11 12（補足） 13「"コピー元ファイル"」をActiveWorkbookとして書いているのは、 14システムから開くたびにファイル名が変わる（日付等が入るため）です。
試したこと
●他のパソコンのマクロの設定は「警告を表示してすべてのマクロを無効にする」にし、
「コピー先ファイル.xlsm」の保存場所を「信頼できる場所」に設定しました。
●他のパソコンでも最初に個人用マクロブックで設定してみたのですが、
エラーになったのでアドインで設定しました。
補足情報（FW/ツールのバージョンなど）
自分のパソコン：Windows10 Home Excel2013
他のパソコン：Windows10 Home Excel2016
コメントありがとうございます。
はい、「"コピー先ファイル.xlsm"」は他のパソコンのデスクトップのフォルダ内に入っています。
実行時は開いています。
インデックスのエラーですと「Workbooks("コピー先ファイル.xlsm").Worksheets(1)」か「ActiveWorkbook.Worksheets("コピー元シート")」のどちらかが原因かと思われますが、直前のコード「ActiveWorkbook.Worksheets("コピー元シート").Unprotect」でエラーが出ないのであれば前者がエラーの原因かと思います。
Workbooks("コピー先ファイル.xlsm").Worksheets(1).Selectなどでエラーは出ますでしょうか？
なるほど、Selectで試してみるという方法は思いつきませんでした。
会社の環境で試せるのが週明けですので結果報告が遅くなって申し訳ないのですが、
試したらまたこちらでご報告します。
ありがとうございます。
meg_様
ActiveWorkbook.Worksheets("コピー元シート").Unprotect
Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C2").Select
と書いたら、selectの行でエラーが出ました。
やはり"コピー先ファイル.xlsm"の方が原因のようです。
下記コードなどで開いているワークブックの名前を確認してみては？
For i = 1 To Workbooks.Count
Debug.Print Workbooks(i).Name
Next
meg_様
ありがとうございます。
試してみたいと思います。
単に指定の場所の、指定のファイル(可変）の指定のシートのデータを、
コピペしてくるだけですよね？
使用頻度がどれくらいかわからないですが、
アドインとしてエクセルに登録しておく必要がありますかね？
マクロ付きテンプレートファイルとして配布して、
おけばいいように思えます。
そうすると、そのファイルを開いたときに、
1)指定の場所からファイルを探しす
2）もし見つからなかったら、エラー表示して終了
3）見つかったファイルを開く
4）データをコピー
5)開いたファイルを閉じる
という流れで、自動化できると思います。
で、保存するときにはマクロ無しで保存するようにしたらどうかと思いますが。。。
テンプレート形式だと、開くときにコピーが開くので、
元のファイルには影響がありません。
mattuwan様
コメントありがとうございます。
使用頻度は毎日２～３回といったところです。
マクロ付きテンプレートファイルは使ったことがなく、考えが及んでいませんでした。
調べてみようと思います。
ありがとうございます。
回答2件
0
ベストアンサー
Excelのマクロブックをアドインとして注意する所があるのですが、
その一番は、ワークブックやワークシートの指定方法です。
Workbooksコレクションでワークブックを指定していますが、これが一番の原因です。
アドインにすると、Excelのファイル名が変更されてしまうのはご存知でしょうか？
（正確には、拡張子が異なる）
人によっては、ファイル名そのものを異なるファイル名で保存する人がいることを想定するのがアドインを作る上で、ポイントになります。
対処方法は、色々あります。
お薦めは、ワークシートの指定方法をオブジェクト名で指定する方法です。
事例のようにWorksheetコレクションを用いて、名前を入れて指定する方法が一般的ですが、ワークシートの指定方法には、オブジェクト名を指定する方法があります。
結構、スコープ範囲が広いので、使えます。
図の場合だと、下のように使用します。
vba
1 Sheet1.Range(Cells(i, j), Cells(i + 4, J + 4).Copy
他にも方法はあります。
例えば、アドインファイルを開いたときに、ファイル名をどこかのシートに保存するとかですね。
投稿2020/07/25 03:01
総合スコア344
kai_keitai様
ご回答ありがとうございます。
会社の環境で試せるのが週明けになりますので、すぐに結果報告ができず申し訳ないのですが、
以下についてもう少しご教示いただけますと幸いです。
>アドインにすると、Excelのファイル名が変更されてしまうのはご存知でしょうか？（正確には、拡張子が異なる）
「アドイン用ファイルは拡張子が『.xlam』になる」ということでしょうか？
いま、アドイン用として「コピー先ファイル.xlam」をAddinフォルダに入れ、
内容のコピー先として「コピー先ファイル.xlsm」をデスクトップのフォルダ内に入れています。
（アドイン用の「.xlam」ファイルというのは、コードを保存するためだけのものだと思っているためです。）
他のパソコンの使用者には、ファイル名は拡張子を含めて変更しないようにと伝えていますので、
手動でファイル名が変更されているということはないと思います。
>お薦めは、ワークシートの指定方法をオブジェクト名で指定する方法です。
例えば「コピー元ファイルのSheet2」から「コピー先ファイルのSheet1」にセル内容をコピーしたいとすると、今回の場合だと以下のように書くということでしょうか？
Workbooks("コピー先ファイル.xlsm").Sheet1.Range("C2") = ActiveWorkbook.Sheet2.Range("E2")
Sheet1.Range("C2") = ActiveWorkbook.Sheet2.Range("E2")
複数ブックで、使用することが多いと思います。
この場合、アドインのブックのシートのオブジェクト名をユニークな名前にすることをお勧めします。
オブジェクト名を変更するには、開発タブのプロパティコマンドで、オブジェクト名を変更するか、VBEのプロパティで変更して下さい。
Sheetのオブジェクト名を指定する方法でも上手く行かない場合があります。
その場合、Bookを開くときに、自分自身のワークブック名を、どこかに保存して、流用する方法もあります。
それと、アドインしたExcelブックについて、勘違いしていますので、アドインファイルについてにも説明します。
アドインに変換したファイルは、通常、アドインが保存される場所にExcelファイルとして保存されます。
Excelファイルと言っても、特殊なファイルとなりますが、Excelのファイルの一つです。
コードだけが保存されるのではなく、ブック全体が保存されるだけです。
よって、アドインにしたとろで、Excelファイルは、Excelファイルです。
コードだけではなく、ワークシートの内容、セルの内容、セルに書かれている数式や値も保存されています。
よって、アドインに変換したExcelファイルのマクロを実行するときも、オブジェクト指定の方法は変わりません。
Workbook.Worksheet.Range のような階層でオブジェクトを指定します。
今回のワークシートの指定方法が、Workbook("*****.xlsm")と、マクロブックになっていました。
とうぜん、ここも、Workbook("*****.xlam")と、アドインファイルを指定するのが正しい記述になります。
アドインの良い所は、ユーザーが、マクロブックを開いたり閉じたりという感覚が無いことだけです。
通常、マクロは、ファイルを開かないと動きません。
ですが、アドインは、Excelを起動したときに開いてくれるので、便利なのです。
kai_keitai様
ご返答ありがとうございます。
私の理解が追いついておらず、少々時間がかかってしまうかもしれませんが、
試してみてまたこちらで結果をご報告致します。
この動画が参考になると思います。
https://www.youtube.com/watch?v=8rAzR26Nn50
kai_keitai様
動画のご紹介までありがとうございます。
拝見しました。分かりやすかったです。
今回の場合は、「コピー元シート」が仮に「Sheet2」で名前を変更しないとすると、
Workbooks("コピー先ファイル.xlsm").Sheet1.Range("C2") = ActiveWorkbook.Sheet2.Range("E2")
と書けばよいのでしょうか？
>今回のワークシートの指定方法が、Workbook("*****.xlsm")と、マクロブックになっていました。
>とうぜん、ここも、Workbook("*****.xlam")と、アドインファイルを指定するのが正しい記述になります。
上記に関してもまだ理解が追いついていないのですが、
今回コピー先のファイルに「"コピー先ファイル.xlsm"」を指定しているのは、
「"コピー先ファイル.xlsm"」内にさらにその中でだけ使うマクロを作っていて、
ボタンを作ってそのボタンに割り当てているためです。
ボタンに割り当てているマクロは各シート１つずつで計５つほどあります。
それはそのファイル内でしか使わないので（指定範囲のプリントアウト機能等）、
アドインではなくボタンにする方が使いやすいと思いました。
また、アドインファイルはAddinフォルダに入れないと使えないと思うのですが、
"コピー先ファイル"はデスクトップから開きやすい位置に置いておきたいと思ったのと、
Addinフォルダ内のファイルはあまり触りたくない（アドインに不具合が出そうで）という心情から
xlsmとxlamファイルを分けて作成しました。
Workbooks("コピー先ファイル.xlam").Sheet1.Range("C2") = ActiveWorkbook.Sheet2.Range("E2")
と書かなければ動かないでしょうか？
回答、遅くなり申し訳ございません。
Workboosコレクションは使わない手法が一般的だと思います。
Sheet1.Range("A2") = xxx
こんな感じの指定です。
ただ、Sheet1のオブジェクト名のままでは、100%エラーが出ます。
オブジェクト名を変更して使用するのが基本です。
kai_keitai様
いえ、とんでもありません。
ご回答いただけるだけでも大変ありがたいです。
長い質問になってしまっているにも関わらず、お付き合いいただき感謝しています。
>Sheet1.Range("A2") = xxx
>こんな感じの指定です。
>ただ、Sheet1のオブジェクト名のままでは、100%エラーが出ます。
>オブジェクト名を変更して使用するのが基本です。
この書き方で書いて試してみたいと思います。
Workbookを指定せずにどうしてエクセルがコピー先のファイルを識別できるのだろうと
不思議に思うのですが、それは
「シート名をそのファイルにしか存在しない名前に変更することで、
ファイル名を飛び越えてシート名で識別するのでエラーにならない」、ということでしょうか？
kai_keitai様
結果のご報告が遅くなり申し訳ありません。
まだ現時点ですべて解決できてはいないのですが、
教えていただいたようにシートのオブジェクト名を変更したら、
インデックスエラーは出なくなりました。
ただ、エラーは出ないが内容がコピーできていないという状況で、
どこか別のところにコピーされているのか（例えば.xlamファイル内とか）と思っています。
（これも自分のパソコンではちゃんとコピーされるのですが、別のパソコンではできません。
何かパソコンの設定が原因なのかなとも思います。）
もう少し自力で調べて解決したいと思います。
ご丁寧に何度もお答えいただき、ありがとうございました。
本当は、ある程度の経験者であれば、もっと良い方法を教えることができました。一応、初心者マークがあったので、一番簡単な方法を教えただけです。
ワークブックの指定方法には、何種類も存在していて、時と場合によって、使い分けるのが一般的なので、結局、プログラマの経験が、一番の武器になります。
本当だったら、レジストリを活用したり、Workbookのオブジェクト型を作って、インスタンスを作ったり、作業用ワークシートにブック名を保存したりと、やり方は様々です。
こちらも、詳しく説明できなく、申し訳ございません。
0
こんにちは。
少々 手を加えましたけど 多分こんな感じでいいのかなぁ と思います。
もし、"コピー元シート" の 非アクティブが心配ならならプロテクト解除の後に
Activateメゾットなんか使うと良いと思います。
Sub サンプル()
Workbooks("コピー元シート.xlsm").Worksheets(1).Unprotect
Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C2") = _
Workbooks("コピー元シート.xlsm").Worksheets(1).Range("E2")
Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C9") = _
Workbooks("コピー元シート.xlsm").Worksheets(1).Range("E6")
Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("C6") = _
Workbooks("コピー元シート.xlsm").Worksheets(1).Range("H5")
Workbooks("コピー先ファイル.xlsm").Worksheets(1).Range("G3:G14").Value = _
Workbooks("コピー元シート.xlsm").Worksheets(1).Range("J26:J37").Value
End Sub
'
'（補足）
'「"コピー元ファイル"」をActiveWorkbookとして書いているのは、
'システムから開くたびにファイル名が変わる（日付等が入るため）です。
ワークシートには任意で名前を変えれる一般的なものと、あまり目立たない
Excelがシートを作成した順番に自動的に番号を振る オブジェクト名があります。
（添え字 とか言ったりします。）
オブジェクト名も変更できるのですがあまりお勧めできません。
私が勝手に添え字を （1）にしましたが実物はご本人でご確認ください。
もし、ファイルの場所が変わらないなら、パスを上手に 使えるかもしれませんね。
Sub パス()
Debug.Print ThisWorkbook.Path
// 実行結果 C:\Users\user\Desktop
Debug.Print ThisWorkbook.FullName
// 実行結果 C:\Users\user\Desktop\copy先.xlsm
End Sub
開く度にファイル名が変わるのも、やっかいですねぇ。
しょうがないけど、 頑張って下さい。
投稿2020/08/02 01:13
編集2020/08/02 01:19
総合スコア42
あなたの回答
tips
プレビュー
