エクセルで写真をサムネール表示するマクロを組んでいます。

質問

エクセルで写真をサムネール表示するマクロを組んでいます。
ところが、Sheetに挿入した写真をコピーするところで
エラーが出ます。

いろいろ試行錯誤を繰り返したところ
コマンドボタンなどのボタン類を張り付けてあるSheetでは、
コピーできないことが分かりました。

　具体的なマクロサンプルを挙げておきますので、
同じようなことが起こるか試してもらえませんでしょうか。
またその原因も教えていただければ幸いです。

Sub test()
Dim x As Variant
x = Application.GetOpenFilename("jpgFiles,*.jpg")
If VarType(x) = vbBoolean Then Exit Sub
ActiveSheet.Pictures.Insert(CStr(x)).Select
Selection.Width = 162
Selection.Height = 100
Selection.Copy
ActiveSheet.Paste
End Sub

　上のtest()マクロをコマンドボタンの有るSheetと無いSheetで
実行すると、
◎コマンドボタンの有るSheet ⇒ Selection.Copyで
　　　　『PictureクラスのCopyメソッドが失敗しました』というエラーが出る。
◎コマンドボタンの無いSheet ⇒ エラーが出ずコード通り実行される

　因みに、
エクセル2007は、(12.0.6425.1000)SP2 MSO(12.0.6529.5000)
OSは、XP　HomeEdition　Version 2002　Service　Pack 3
です。

回答No.7

end-u · ベストアンサー

とりあえずPictures.Copyの問題はUpdateで解決の方向のようで
良かったですね。

先にも書きましたがPicturesオブジェクトについては
便利なのですが、隠しオブジェクトという事もあり、
Pictures.InsertもMicrosoft非推奨のようです。
http://moug.net/faq/viewtopic.php?t=51292
今後はShapes.AddPictureに切り替えていく事なども検討の必要が
出てくるのかもしれませんね。

>P.S
の現象については結構ありがちです。
マクロ記録のままのコードを実行してもエラーが出るケース。
Selectionが絡んでたりする事が多いでしょうし。
そんな事もあるんだ、
くらいであまり気にされないほうが良いかと。

現状のトラブルについては、
念の為2007のセキュリティレベルを確認してみる事と、
『コントロールは作成されていないため、デザインモードを終了できません』
これをキーワードにgoogle検索して、
いろんな情報を探ってみたほうが良いかもしれません。

BookあるいはSheetの破損の可能性..という情報もありますが、
確認してみたほうが良いと思えるのが
http://naganonopapa.blog102.fc2.com/blog-date-20100610.html
この情報。KB978262に原因があるのでは、という事です。
もちろん環境によって全く関係ないかもしれませんので参考まで。

ではでは。　:)

お礼

end-uさん、最後まで手間のかかる質問者に
丁寧にお答えいただき、本当にありがとうございました。

　結局、今回のマクロ不具合はバージョンの違いが原因
だったようですね。私の知識不足と勘違いなどのため
すんなりUpdateしていればもっと早く原因が分かって
いたはずで、反省しております。

　まだ、
『コントロールは作成されていないため、デザインモードを
終了できません』
という不具合については解決できていませんが、
紹介していただいたURLなどを参考に、これから解決
していきたいと思います。

　また、何かありましたらよろしくお願いいたします。

回答No.6

end-u

気を悪くというよりも、脱力系？な疲れです。失礼しました。

まず、
Sub test1()
　　Dim x As Variant
　　x = Application.GetOpenFilename("jpgFiles,*.jpg")
　　If VarType(x) = vbBoolean Then Exit Sub
　　With ActiveSheet.Pictures.Insert(CStr(x))
　　　　.Width = 100
　　　　.Copy
　　　　ActiveSheet.Pictures.Paste
　　　　.Delete
　　End With
End Sub
●Q1)これではエラーが出るんですね？

その環境で
Sub test2()
　　Dim x　As Variant
　　Dim sp As Shape

　　x = Application.GetOpenFilename("jpgFiles,*.jpg")
　　If VarType(x) = vbBoolean Then Exit Sub
　　Set sp = ActiveSheet.Pictures.Insert(CStr(x)).ShapeRange.Item(1)
　　With sp
　　　　.Width = 100
　　　　.Copy
　　　　ActiveSheet.Pictures.Paste
　　　　.Delete
　　End With
　　Set sp = Nothing
End Sub
●Q2)これではどうですか？

その後
>取り敢えずExcelを終了して
>Windowsスタートメニュー[ファイル名を指定して実行]から
>%temp%
>と入力して、開いたフォルダ内のファイルを
>全て削除して実行してみたらどうなりますか？
tempフォルダは作業用フォルダです。
そのフォルダ内のファイルは不要なので削除して構いません。
拡張子tmpのファイルがいくつか削除できない事があるかもしれませんが、
それ以外のファイルは削除してください。
●Q3)この作業後のSub test1、Sub test2の結果を教えてください。

MicrosoftUpdateを実行してください。
最新の状態では Excel2007 (12.0.6535.5002)SP2 になると思うんですが
なりませんか。Updateを何回かやってみてください。
●Q4)更新できたら、Sub test1、Sub test2の結果を教えてください。

>Copyと[ActiveXコントロール]に
>何か関係があるのでは、と自分的には強く感じています。
cj_moverさんも書かれてますが、
>シート上にコントロールを貼る人なら、
>Tempフォルダの掃除は人一倍心がけておいた方がよいです。
シート上に[ActiveXコントロール]を配置して使った場合、
Tempフォルダ内に拡張子tmp、emf などのゴミファイルが残り易い...
というのは以前からある現象で、これらのファイルがExcelの起動速度や
Shape関係の挙動などに影響を与えるという事例があるようです。
これは実際にTempフォルダ内の不要ファイルを削除する事で簡単に検証できます。

>　それで教えていただいたtry()マクロを試してみました。
>結果、上手く行きました。Copyがどうしても旨くいかない場合は
>これで行かせてもらいたいと思います。
前レスSub try も Copyメソッドを使ったものでしたが。
(Q2)の段階でエラーが出ないなら、
PicturesオブジェクトのCopyメソッドの問題かと推測されます。
それと[ActiveXコントロール]が影響してるのか、
それに加えTempフォルダの不要ファイルが影響してるのか、わかりません。

2007ではShape関係の仕様も大きく変わったようですし、
PicturesオブジェクトはExcel97の頃より隠しオブジェクトになっています。
Pictures.CopyとShapes.Copyで挙動に違いが出ているのかもしれません。
実際にDuplicateメソッドで違いが出てたりもします。

ただ、複数の回答側で現象が確認できない事、
>　結果、エクセル2007で初めてエラーなく（サクサクと）表示していました。
>WINDOWS7 HomePremium
>Excel2007 (12.0.6535.5002)SP2 MSO(12.0.6535.5002)
>でした。
との記述から、既に更新プログラムが提供されている可能性が高いのでは
ないかと思っています。
(Q3)Sub test1がエラー、(Q4)Sub test1で解消、ならそういう事になります。

お礼

end-uさん、昨日はどうもありがとうございました。

　補足で結果報告をさせてもらいましたが、お礼を兼ねて
補足の補足をさせて頂きます。

　昨日、補足で書きましたように変な現象が出てきて
混乱状態になってしまい、とりあえず復元ポイントに戻しました。
　その後、改めて再度Updateを実行しました。
全てのUpdate終了後、再び補足と同じ現象が出てきましたので
今回はエクセル2007の
『MS Office 診断プログラムの実行』を行いました。
　結果はやはり、
『'CommandButton1’コントロールは作成されていないため、
デザインモードを終了できません』
と同じ警告が出ます。

　エクセル2003では、今までどおり正常に動作していますので
また、Copyも機能するようになったようですので
復元ポイントには戻らず、しばらく様子を見ることにしたいと思います。

　ここをごらん頂いている方々にはお騒がせして
申し訳なく思っています。（汗、汗）

P.S
　Updateする前のエクセル2007で、いろいろ試した中で
次のような現象もありました。

１）新規Bookを開き
マクロの記録をとりながら
写真（予め小さくしてある）を挿入し、コピー貼り付けをしました。
このとき、マクロの記録も正常にできたのですが、
改めて、記録されたマクロから実行させると
Copyのところでエラーが出るという珍現象がありました。

２）test()等をいろいろ修正していると、
ActiveSheet.Pictures.Insert(CStr(x))
の所でもエラーが出ることもありました。

補足

end-uさん、とても丁寧なご教示ありがとうございます。

　早速、試してみました。

　新しいBookを新規から作り、test1()、test2()を標準モジュールにコピーし
アクティプXコントロールのコマンドボタンを一つ貼り付け
（コマンドボタンにはコードは何も書いていません）
一旦、2003のxls形式で保存し再度そのファイルを開いてから検証しました。

　結果は、
●Q1)やはり、Copyところで、これまでと同じエラーが出ます。
●Q2)エラーは出ず、コード通り動きました。（正常）

>その後
>取り敢えずExcelを終了して
>Windowsスタートメニュー[ファイル名を指定して実行]から
>%temp%
>と入力して、開いたフォルダ内のファイルを
>全て削除して実行してみたらどうなりますか？
　%temp%の意味が分かっていなくて前回はファイルが
表示された時点で？？？でそこから進めなかったのですが、
今回は意味ややり方教えていただき、ファイルを全て削除して
から試してみました。
　その結果は、
●Q3)上と同じで
test1()ではエラーが出て、test2()ではエラーが出ませんでした。

>MicrosoftUpdateを実行してください。
>最新の状態では Excel2007 (12.0.6535.5002)SP2 になると思うんですが
>なりませんか。Updateを何回かやってみてください。
>●Q4)更新できたら、Sub test1、Sub test2の結果を教えてください。

　前回までは、セキュリティー関係のUpdateは飛ばしていたのですが、
今回は全てのUpdate（OSも含め）をしたところ、
Excel2007 (12.0.6535.5002)SP2 になっていました。
　これで上手くいくだろうと
●Q4)test1、test2を試したところ、
今度は両方ともコード通り正常に動作しました。

　これでOKと思って、元々の2003で正常に動作している
サムネール（マクロ入り）ファイルを開いて、マクロを試そうとしたところ、
『'CommandButton1’コントロールは作成されていないため、
デザインモードを終了できません』
というメッセージが出て、それを閉じると勝手に
デザインモードになってしまい、マクロがききません。
　その後、ボタンを使っていないマクロを試そうとしても
またその警告が出てしまい、結局このファイルではマクロが
全く機能しませんでした。

　えーーーっなんでぇーーーーっと
頭が混乱してしまいました。

　その後、このファイルを一旦保存して閉じようとすると
『初期化されていないActiveXコントロールを選択した
形式（xlsのこと）に保存することは出来ません。
続行するとこのようなコントロールは失われます。』
との警告が出たため、名前を変えてxls形式で
保存し、再び開いてみると
ボタン類は表示だけされていますが、全然機能しません。
　次にまた元の最初のファイルを開いて、今度は2007形式で
保存し、再び開いてみると今度は
全てのボタン類が消えて失くなっていました。
（因みにCopyの所ではエラーは出なかったです）

　結局、理解不能で気色悪くなって来て、復元ポイントを使い
元に戻しました。
（end-uさんではないですが、私も脱力状態になりました。）

　ということで、しばらく頭を冷やすことにしたいと思います。

　最後にend-uさんには、何回も付き合っていただき
本当に感謝しております。どうもありがとうございました。

回答No.5

cj_mover

お邪魔します
ひとつの可能性として、ですが、、、

とりあえず
（シートモジュール） CommandButton のイベントプロシージャーの先頭（若しくは問題の記述の直前）
に１行
　ActiveCell.Activate
を追加した場合はどうでしょう。

それでダメならイベントプロシージャー側のサブルーチンの呼び出しを工夫して２行
　ActiveCell.Activate
　Application.OnTime Now(), "Test"
　（Sheet1（←オブジェクト名）モジュールにあるプロシージャなら
　Application.OnTime Now(), "Sheet1.Test"）
として、イベントプロシージャーをとりあえず抜けるようにしてみるとか。

場合によっては（ひょっとして）　ActiveCell.Activate　に代えて
　AppActivate Application.Caption
だとどうなるか、とか。

ActiveX コントロールの CommandButton なら、プロパティー設定で
.TakeFocusOnClick プロパティーをFalseにして試してみるとか。

何れの方法も最善策とはいえないけど、検査薬にはなるかと。
結果が出れば識者からより良い薬が貰えると思います。

どの場合でも Selection を使わないで書く方がベターです。

シート上にコントロールを貼る人なら、
Tempフォルダの掃除は人一倍心がけておいた方がよいです。

見当外れでしたらすみません。

#微妙に被りましたが、せっかく書いたので、、、

補足

cj_moverさん、ご回答ありがとうございます。

>ActiveCell.Activate
>を追加した場合はどうでしょう。
　を（若しくは問題の記述の直前）で試してみましたが、
上手く行きませんでした。

>CommandButton のイベントプロシージャーの先頭・・・
　についても上手く行きません。
そもそもCommandButtonには何のコードも書いていません。
エラー確認のためにCommandButtonを貼り付けているだけです。
（言葉足らずで申し訳ありませんです）

　上の方の補足にも書かせていただきましたが
当方では、エラーの原因はコードの書き方ではなく
ActiveX コントロールとCopyとの関係があると思っています。
このあたりのことで何か推察できることはありませんでしょうか？

回答No.4

end-u

元々画像リサイズの為のCopyであればその辺りも考慮したほうが良いかもしれませんね。
Sub try()
　Dim x As Variant
　Dim s As Shape

　x = Application.GetOpenFilename("jpgFiles,*.jpg")
　If VarType(x) = vbBoolean Then Exit Sub
　Set s = ActiveSheet.Shapes.AddPicture(Filename:=CStr(x), _
　　　　　　　　　　　　　　　　　　　　LinkToFile:=msoFalse, _
　　　　　　　　　　　　　　　　　　　　SaveWithDocument:=msoTrue, _
　　　　　　　　　　　　　　　　　　　　Left:=0, _
　　　　　　　　　　　　　　　　　　　　Top:=0, _
　　　　　　　　　　　　　　　　　　　　Width:=162, _
　　　　　　　　　　　　　　　　　　　　Height:=100)
　s.Copy
　ActiveSheet.Pictures.Paste
　s.Delete
　Set s = Nothing
End Sub

もしCommandButtonが[ActiveXコントロール]のCommandButtonだったら
その[プロパティ]でTakeFocusOnClickプロパティを False にしておいたり、
[ActiveXコントロール]じゃなくて[フォームコントロール]のボタンに
変更したりしてみるのもありかもしれません。

『？』つきで質問しても応答ないのでこの辺で失礼しますが、
回答側に貴方のファイルがあるわけでもなく、同一の環境でもなく、
ましてやエラーが再現しないので、推測でアドバイスするしかありません。
その過程で、
可能性は薄いかもしれませんが実際にそちらの環境でやってもらう事で
１つずつ潰して絞り込んでいくような手法を取らざるを得ません。
中にはダメ元的なものもあります。
それを実際に試されないのならアドバイスしがいがないですよね。

「コマンドボタンなどのボタン類」という情報って
どの種類のボタンが何個あるか、
『類』とは、他の種類のコントロールがあるのか
とかまた推測するしかないですが、
提示された情報を元にこちらで新規Bookで試しても再現しません。
新規Bookにボタンを追加して、そのコードでどの端末でも
どの画像でもエラーが発生するのでしょうか。

いずれにしても、
実際に使えないファイルだったら何とかしないといけないでしょう。
ボタンが原因だとある程度わかってるなら
それを使用しなくてもよい仕様に変更するとか。
ワークシート上に置くんじゃなくてUserFormを使ったりとか。
または１からファイルを作り直すようにして、
2003から作成するパターンと
2007から互換ファイルで作成するパターンと２通り試し、
バックアップを取りながら少しずつ機能を増やしていってはどうですか。

補足

end-uさん、ご回答ありがとうございます。

>それを実際に試されないのならアドバイスしがいがないですよね。
　恐れ入ります。全くその通りでお気を悪くされたようで
本当に申し訳なく思っております。

　当方の知識や、PCの設定（特に職場のPC）で実際に試せないこともありまして、
試すことができるものだけ補足させてもらっている次第です。
（職場で使うことになりますので）

>元々画像リサイズの為のCopyであればその・・・
　おっしゃる通りです。（メモリー節約のためこれを使うということは
この教えてgooで教えてもらいました。）

　それで教えていただいたtry()マクロを試してみました。
結果、上手く行きました。Copyがどうしても旨くいかない場合は
これで行かせてもらいたいと思います。

>もしCommandButtonが[ActiveXコントロール]のCommandButtonだったら
・・・
>「コマンドボタンなどのボタン類」という情報って
>『類』とは、他の種類のコントロールがあるのか・・・
　コマンドボタンやその他の[ActiveXコントロール]すべて
に共通して1個でも張り付けるとCopyの所で同じエラーが出ます。
（言葉足らずで申し訳ありません）

>その[プロパティ]でTakeFocusOnClickプロパティを False にしておいたり、
　これは試してみましたが、上手く行きませんでした。

>[ActiveXコントロール]じゃなくて[フォームコントロール]のボタンに
>変更したりしてみるのもありかもしれません。
　これについては、エラーは出ませんでした。
（これもまた、不思議です）
ただフォームコントロールは今まで全く使ったことがありません
ので勉強する必要があります。
　ただこれを使うとなるとかなり大幅な変更も伴いますので
気分的には少し気が重いです。

　今日、たまたま職場で最近の新しいノートパソコンを使っている同僚が
いましたので試させてもらいました。
　結果、エクセル2007で初めてエラーなく（サクサクと）表示していました。
WINDOWS7 HomePremium
Excel2007 (12.0.6535.5002)SP2 MSO(12.0.6535.5002)
でした。
　やっぱり、2007でも上手く行くPCがあるということを実感しました。

　そういうこともあり、またここまでやってきた以上、
なんとかCopyのままで行けないか、もう少し拘ってみようと思っています。
　またまたしつこいようですが、Copyと[ActiveXコントロール]に
何か関係があるのでは、と自分的には強く感じています。

回答No.3

watabe007

>◎コマンドボタンの有るSheet ⇒ Selection.Copyで

挿入した写真をSelectしないで処理すれば如何かな
Sub test2()
　　Dim x As Variant

　　x = Application.GetOpenFilename("jpgFiles,*.jpg")
　　If VarType(x) = vbBoolean Then Exit Sub
　　With ActiveSheet.Pictures.Insert(CStr(x))
　　　　.Width = 162
　　　　.Height = 100
　　　　.Copy
　　　　With ActiveSheet.Pictures.Paste
　　　　　　.Top = Range("B5").Top
　　　　　　.Left = Range("B5").Left
　　　　End With
　　　　.Delete
　　End With
End Sub

補足

watabe007さん、ご回答ありがとうございます。

　watabe007さんのPCでは、私と同じエラーは出なかったようですね。
それで、記していただいたtest2()マクロを試してみました。

　結果はやはり、コマンドボタンのあるSheetでは、
Copyのところで同じエラーが出ます。

　質問マクロtest()は、家のパソコンと職場のパソコン合わせて3台で
試していますが、すべて同じエラーが出ます。
◎職場のパソコン⇒Vistaでエクセル2007
◎家のパソコン⇒XPでエクセル2007
　　　　　　　（家のパソコンにはエクセル2003も入っています）

　因みに、エクセル2003では、上のようなエラーは出ません。

回答No.2

end-u

まだ解決してなかったんですね。
http://okwave.jp/qa/q6064055.html?order=asc
この前から、どうにも再現しません。

いくつか提案してますが、実行するには難しい環境なんでしょうか。
>・端末に別ユーザーでログイン。（もしくは新規ユーザー）

取り敢えずExcelを終了して
Windowsスタートメニュー[ファイル名を指定して実行]から
%temp%
と入力して、開いたフォルダ内のファイルを
全て削除して実行してみたらどうなりますか？

補足

end-uさん、今回もお付き合いいただきありがとうございます。
また、よろしくお願いします。

　前回教えていただいた
Selection.CopyPicture
では、写真の表示が凄く遅いのと、
画面をスクロールさせたりするとまた表示し直すようで
実用には少し難があるという感じでした。

　それで、何が原因かあれこれ試すうちに
コマンドボタン類があるとCopyの所で必ずエラーが出る
というのが分かった次第です。
（同じSheetでコマンドボタンを付けたり外したりしても
その度に、Copyの所でエラーが出たり出なかったりします）
　ここで回答していただいている方には
私のようなエラーが出ないようですので
私にはその方が不思議なくらいです。

　因みに、上記のコマンドボタン有る無しで出るエラーは
職場の2台のVistaパソコンで試しても同じ症状のエラー
がやはり出ます。（３台のパソコンで試しています）

　職場の他の人にも（違うIDで）やってもらいましたが同じでした。
ですので、
>・端末に別ユーザーでログイン。（もしくは新規ユーザー）
でも同じだと思います。

　しつこいようですがエクセル2007では、コマンドボタンと
Copyとの相性？（何か関連がある）のようなことで何か推察できることは
無いでしょうか？

回答No.1

vbhanatyan

私の環境で試した限りでは問題なく動作しましたが、本来、Copy や Paste の
動作は独立した動作として実行すべきかと思います。
CPU の性能等によって画像が表示しきらない状態でCopy が実行されたり、クリップボードに
転送されない内にPaste が実行されたりします。
従って、本来なら取得された画像が表示され、さらにその画像が選択されたかどうかを確認
してから、Copy を実行するようにしないとエラーとなる場合があります。

と言っても実際にそのようなプログラムを組むのは面倒だし複雑になるので、簡略的に待ち時間を
適当に作って動作を継続するのが一般的ですが、最近は、DoEvents や Sleep 関数を使う事の
弊害が色々言われているようなので、動作確認を十分に行って多用しないようにして下さい。

取り敢えず、テストコードとして下記を試してどうなるか確認して見て下さい。
Sub test()
　Dim x As Variant
　x = Application.GetOpenFilename("jpgFiles,*.jpg")
　If VarType(x) = vbBoolean Then Exit Sub
　ActiveSheet.Pictures.Insert(CStr(x)).Select
　Selection.Width = 162
　Selection.Height = 100
　WaitTime (1)
　Selection.Copy
　WaitTime (1)
　ActiveSheet.Paste
End Sub

Public Sub WaitTime(st As Single)
　Dim sngSt As Single
　sngSt = Timer
　Do While Timer - sngSt < st
　　DoEvents
　Loop
End Sub

補足

vbhanatyanさん、ご回答ありがとうございます。

　早速家のパソコンで試してみましたが、
やはりコマンドボタンのあるSheetでは
Selection.Copy
のところでエラーが出ます。

　質問に記したtest()マクロを
今日職場のVistaパソコンでも試しましたが
やはりCopyのところでエラーが出ます。

　この質問マクロは、元々エクセル2003で正常に
動作していたものが、2007になってエラーが出て来たことに
よるものです。ですので、

>CPU の性能等によって画像が表示しきらない状態でCopy が実行されたり、クリップボードに
>転送されない内にPaste が実行されたりします。
が原因とは思えないのですが・・・。