[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBAでコードをコピーする先のシートを指定』（てん）
初めて質問させていただきます。よろしくお願いいたします。
環境は
エクセル→Excel2003
OS→WindowsXｐ
です。
ファイルAのVBAで、あるコードをファイルBにコピーしたいのですが、コードは外部のテキストファイルに保存されています。
コードが保存されているファイルを「test.txt」、このテキストファイルのパスが変数a_pathに格納されているとすると、ファイルA内のコードでファイルBをOpenした状態で
With Application.VBE.VBProjects.Item(i).VBComponents("Sheet1")
.CodeModule.AddFromFile a_path & "\test.txt"
End With
とするとコピーできることはわかっているのですが、コピー先のシートの指定がうまくできません。
シート番号または、モジュール名での指定はきでるのですが、シート名で指定できないでしょうか？（シート名→ファイルBを開いたときに、シート名のタブに表示される文字列）
と言いますのが、コードをコピーしたいシートが、ファイルBの何番目のシートに挿入されているかが未定で、シート名だけが固定になっているためです。
よろしくお願いいたします。
こんな感じで出来るとは思いますが・・・
Workbooks("ファイルＢ.xls").VBProject.VBComponents("Sheet1").CodeModule.AddFromFile a_path & "\test.txt"
以前にもありましたが、そもそもそんな事をしなくても良いような仕組みにするのが 理想だと思います。
（ｍｏｍｏ）
説明が悪くて申し訳ありません。。。。
シート番号 モジュール名 シート名
1 Sheet1 シート1
2 Sheet2 コピー先シート
3 Sheet3 シート3
と言う状態のとき（この場合のシート名はエクセルファイルを表示したときにタブに表示される文字列です）に、
シート名（たとえば、「コピー先シート」のように）でコピー先のシートを指定したいのです。
コピー先のシート名が固定で決まっており、シート番号が未定のためです。
教えていただいたコードでは、モジュール名の指定になっているために、もともとの私のコードと
あまり変わりないのでは。。。と思うのです。
試してみましたが、”Sheet1”の箇所に”コピー先シート”と入れておくと、「Indexが有効範囲にありません」のエラーが出ます。
よろしくお願いいたします。
CodeName BJ
BJさんと被りますが
コードネームを予め取得しておけば良いのでは？
Dim myCodeName As String
With Workbooks("ファイルＢ.xls")
myCodeName = .Worksheets("コピー先シート").CodeName
.VBProject.VBComponents(myCodeName).CodeModule.AddFromFile a_path & "\test.txt"
End With
しかし・・・怖い事をしたがる方が多いのですね・・・ （ｍｏｍｏ）
>もともとの私のコードとあまり変わりないのでは。。。 これが気になるので補足。
元々のコードではVBEに対するVBProjectとして扱っていますので 複数のブックやアドインなどがあるとどのProjectがファイルBのProjectかわからなくないですか？ Item(i)となっていますが、ループさせて処理しているのでしょうか？
ブック名のProjectを参照するだけで済むというだけでも意味はあると思いますよ？ （ｍｏｍｏ）
もう解決済みのお話のようですが、 txt で保存するのではなく、マクロを書いたEXCEL ファイルとして保存してお いて、シートをコピーして、その上に必要なシートをコピーすればできないで しょうか。 このファイルをテンプレートとして使えば、もっとスマートだと思いますが。
あるいはマクロだけをアドインとして使うのも、運用としては一般的だと思い ます。 こうしておけば、後からコードを変更したいというときも、更新の仕組み を入れておくこともできます。 http://www.asahi-net.or.jp/~ef2o-inue/haifu/sub06_030.html (Mook)
＞ｍｏｍｏ さん
はい、ループさせて処理していました・・・
（てん）
myCodeName = Workbooks(ファイルB).Worksheets("test.txt").CodeName
Workbooks(ファイルB).VBProject.VBComponents(myCodeName).CodeModule.AddFromFile (a_path & "test.txt")
としたところ、実行後にファイルBを開くと、
「このブックにあるVBAプロジェクト、ActiveXコントロール、およびその他のプログラミング関連の機能は失われています」
と表示され、ファイルBをVBAエディターで見ることができません（コピーしたコードは消滅？？）。
何が悪いのでしょうか？？
よろしくお願いします
（てん）
どんなコードをAddしているのかもわからないので正直わかりません。 それに、先に申しましたように本当にしっかりコーディングしないと 思わぬエラーやファイルの破損にも繋がりかねません。
なので私ならMookさんの仰っているような方法や別の方法で実現します。
私自身、そのような事に遭遇した事が無いですし試すこともしないので これ以上のフォローは難しいです。
（ｍｏｍｏ）
>結構怖いことをやろうとしていたみたいですね・・・；； Vbprojectを扱うことが怖いことではないですよ！！ これを使って何を何のために作るのか の返答によっては、 >結構怖いこと にもなるということです。 実際、Vbprojectを操作している開発者向けのツールが重宝している 場合もありますからね！！
今回のVbprojectの扱いでも以下のような目的なら、ツールとして納得できます。
既に納品されている（使用されている）VBAコードを含んだブックのコードの修正 本来は、コード修正したブックをそのまま差し替えれば済みますが、 そのブックにユーザーが作成したデータまで入っているとそうはいきませんね。
それは、最初から設計ミスなのでは？ そもそも プログラムブックとデータの媒体は、別にすべき。 ↑もちろんこれが正論ですが、 VBAの場合、プロラミングを基礎からみっちり積み上げてくる方ばかりではありませんから、 始めたばかりだとついコードとデータが一体型のブックを作ってしまいます。
このようなVBAコードとデータが一緒になったブックにまるでパッチをあてるような VBAプログラマのためのVBAコードは、あったら便利だと思いますよ！！
てんさんへ もし、上述したような目的のコードだとしたら・・・。
まず、提示されたエラーの再現手順書をきっちり記述してください。
ぱっと見た限り、VBAコードの修正対象となるブックと修正されたコードが含まれた テキストファイル、それから、それらのファイルをVBProjectを操作して、 コードを書き換えるVBAコードを含んだブック
エラーを再現するためには、これらブックやテキストファイルの説明から始まり、 コードの提示、エラーを再現するための事細かなオペレーティング。
てんさんの記述どおり実行すれば、みんながそのエラーが体験できる 再現手順書を記述することです。
多くの方に再現してもらえるように詳細に記述してください。 （Excelの設定なども説明も必要ですよ！！ Excelの設定をしなきゃ、Vbprojectは、再現できませんから・・・。）
たくさんの方にチェックしてもらえば、色んなことが見えてきます。 対象エラーは、Excel2003でしか起こらないことかもしれないしね
このあたりの記述をやってみてください。
ichinose
下記はあくまで私見ですので、気に障ったら読み飛ばしてください。
「できる・できない」、「危険・便利」という観点はさて置いて、 推奨できる方法ではないと思います。
>VBAの場合、プロラミングを基礎からみっちり積み上げてくる方ばかりではありませんから、 >始めたばかりだとついコードとデータが一体型のブックを作ってしまいます。 このような方が、コードによるコード修正を行うことこそが怖い気がしますが。 プログラミング知識のない人が、いろいろな既存のデータとの整合を考え、 様々なテストケースで、動作検証をしたりできるでしょうか。
まぁ、製品ではないでしょうから、影響範囲は自分だけというのであれば そこまで言及しませんが・・・。
言いっぱなしで申し訳ありませんが、議論が炎上するのは好みませんので、 こちらからは以降返信いたしませんので悪しからずご了解ください。 (Mook)
>推奨できる方法ではないと思います。 >>VBAの場合、プロラミングを基礎からみっちり積み上げてくる方ばかりではありませんから、 >>始めたばかりだとついコードとデータが一体型のブックを作ってしまいます。 >このような方が、コードによるコード修正を行うことこそが怖い気がしますが。 ん？推奨できない理由が↑だとすると、逆の言い方をすれば、 プログラミング知識があれば、推奨できるということですか？ （尚、これは、たとえ話であって、てんさんのことを言っているわけではありません。） 色んな想定は、できますよ 作成したコードとデータの一体型ブックは、構造的に問題はあったが、社内では好評で、20台ぐらいの ＰＣに入っている。ところが2年後に前述ような修正の必要があったとします。 プログラム作成時点から、時間たっていて、作成者も当初に比べれば、数段プログラミング技術は、進歩している。 進歩しているからこそ、Vbprojectを操作して、プログラム修正ツール作ろうという発想になっている。 これなら、推奨できるということですね？
あるいは、一体型ブックの作成者ではない別の熟練プログラマがコード修正に時間かけている作成者を見 るに見かねて、簡単に修正できるVbprojectを操作したツールを作成した。 これなら、推奨できるのですか？
>プログラミング知識のない人が、いろいろな既存のデータとの整合を考え、 >様々なテストケースで、動作検証をしたりできるでしょうか。 ここで言っているツールというのは、前述したようにコード一つ一つ修正する手間を 短縮するためのツールです。整合性うんぬんの話は、関係ありますか？ 手で修正するか、修正をプログラムにやらせるか ですよ！！ Vbprojectって、Helpもありますし、扱いは難しくないですよね！！ すくなくとも、複雑な数式やSQLなんかより、ずっとわかりやすいと思いますけどねえ！！
私は、繰り返しますが、このVbprojectに関しては、何を何のために 作成するのか？ これによって、Vbprojectの使用の是非を考える という考え方ぐらいしか規制はできない という 意見なんです。
と、記述しましたが、では、ｍｏｍｏさんやMookさんと全く意見が違うのかというと そうではないと思っています。これに対する微妙な立ち位置の違いだと思います。
現に前回投稿の最後にてんさんに対して 「エラーになるまでの再現手順書を記述してください。これが記述できないようなら、 Vbprojectを操作するのはもう少し先に延ばしたほうが良いかもしれませんよ」
の1行を加えようとしていました。 VBprojectの操作と再現手順書の話は、別問題だなあ と思い、削除しましたが・・。
ｍｏｍｏさんが言っている「怖い事」も頷けます。 が、Vbprojectを使った便利なツールが存在する以上、Vbprojectの全否定はできないのです。
今回のエラーがVbprojectの有効利用するためにも 再現手順の記述は、私は知りたいなあ と思っています。
てんさんの投稿を待ちたいと思っています。
ichinose
上記にも書きましたとおり、ファイルAから、test.txtファイルの内容（コード）をファイルBにコピーしています。
ファイルAは、一枚のシートにコマンドボタン一個のみを配置し、そのボタンをクリックすることによって、ファイルB内のデータを操作するものです。
ところが、このファイルB内のデータを、セルへの直接手入力で変更した際、他のセルの値も連動してほしい、という要望があり、この値の連動が更に他のファイルを参照したり、と単なる式では記述できないために、コードをコピーすることを考えました。
test.txtファイル内には、ワークシートのChangeイベント発生時の関数（Private Sub Worksheet_Change(ByVal Target As Range)）を記述しています。
ファイルAの参照設定で、「Microsoft VisualBasic for Applocations Extensibility 5.3」にチェックを入れています。
ファイルAのコードは以下のとおりです。
'ファイルBをオープンします
Workbooks.Open filename:=a_path & "\" & "ファイルB"
’ファイルBの特定のシートのCodeNameを取得します
myCodeName = Workbooks("ファイルB").Worksheets("Sheet1").CodeName
’ファイルBにtest.txtの内容をコピーします
Workbooks("ファイルB").VBProject.VBComponents(myCodeName).CodeModule.AddFromFile (a_path & "\" & "test.txt")
’ファイルBを保存して
Workbooks("ファイルB").Save ← X行
’ファイルBをクローズします
Workbooks("ファイルB）.Close ← Y行
としています。
このコードを実行した後、ファイルBを開くと、「このブックにある、VBA プロジェクト、ActiveX コントロール、およびその他のプログラミング関連の機能は失われています。 」というメッセージが表示されて、OKボタンをクリックして表示すると、コピーしたはずのコードは保存されていません。
そこで、上記ファイルA内のコードのX行とY行をコメントアウトしてみました。実行後、表示されたままのファイルBを手動で保存してから閉じると、次回起動時には正常に起動でき、コードも保存されていました。
以上、よろしくお願いいたします。
（てん）
ちょっと再現手順を整理してみましょう。 Excel2002での再現手順です。
まず、VBProjectを操作するには、セキュリティを実質下げなければなりません。
Excelを起動してください。
http://support.microsoft.com/kb/813969/ja
ここに倣って(Excel2002では、2003と同じ手順です）、 Vbprojectの操作が可能になるように設定してください。 尚、上記の設定は、実験終了後は、元に戻して置いてください。
尚、このテストでは 参照設定は不要です。
以下に記述する三つのファイルを同一フォルダ上に作成してください。
BookB.xls
何も入力されていない状態で結構ですから、 新規ブック（Sheet1というシート名は存在する）をそのままBookB.xlsとして保存してください。
TEST.TXT メモ帳等で以下のテキストデータを記述して、上記の名前で保存してください。
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "ichinose"
End Sub
BookA.xls
新規ブックの標準モジュールに
'=============================================================
Sub test()
Dim dmy As Object
Dim mycodename As String
Workbooks.Open Filename:=ThisWorkbook.Path & "\bookb.xls"
Set dmy = Workbooks("bookb.xls").VBProject.VBComponents 'おまじない
mycodename = Workbooks("bookb.xls").Worksheets("Sheet1").CodeName
Workbooks("bookb.xls").VBProject.VBComponents(mycodename).CodeModule.AddFromFile ThisWorkbook.Path & "\" & "TEST.TXT"
Workbooks("bookb.xls").Close True
End Sub
上記のコードを記述し、保存してください。
一度、Excelを終了してください。
再度、Excel起動してください。
BookA.xlsだけ開いてください。
testを実行してみてください。処理自体は、正常に処理されます。
処理を施したBookB.xlsを開いてください。
「このブックにある、VBA プロジェクト、ActiveX コントロール、 およびその他のプログラミング関連の機能は失われています。 」 というメッセージが表示され、意図したブックが作成されません。
修正コードは、次投稿にて。
ichinose
BookB.xlsを前回投稿と同じ手順で再度、作り直してください。
BookA.xlsにあるコードを以下のように変更してください。
'=================================================================
Sub test()
Dim dmy As Object
Dim mycodename As String
Workbooks.Open Filename:=ThisWorkbook.Path & "\bookb.xls"
Set dmy = Workbooks("bookb.xls").VBProject.VBComponents
mycodename = Workbooks("bookb.xls").Worksheets("Sheet1").CodeName
Workbooks("bookb.xls").VBProject.VBComponents(mycodename).CodeModule.AddFromFile ThisWorkbook.Path & "\" & "TEST.TXT"
Application.OnTime Now(), "closebk"
End Sub
'========================================================================
Sub closebk()
Workbooks("bookb.xls").Close True
End Sub
上記のコードを記述し、保存してください。
一度、Excelを終了してください。
再度、Excel起動してください。
BookA.xlsだけ開いてください。
testを実行してみてください。前回同様、正常に処理されます。
処理を施したBookB.xlsを開いてください。
今度は、TEXT.TXTの内容のコードがSheet1に作成されているはずです。
試してみてください。
次投稿に続く
ichinose
修正方法は、他にもあるかもしれませんが、前投稿で正常に処理できています。
ご質問のエラー自体は、興味深い話でしたが、
てんさんに対しては 、 >「怖い」「怖くない」あるいは「推奨できる」「できない」はこの際おいておいて
この問題もよくよく検討してください。というのは、記述された内容だけでは、 Vbprojectの使用の是非を判断できません。 （どんな目的で、だれがこのプログラムを使うのか はっきりわからない）
私もVbprojctの操作には、どんな仕様でも使ってよい と思っているわけでは ありません。
ここでも同じような話が展開されています。
↑ここでは、Vbproject使用に反対しています。
> Vbprojectの操作は、セキュリティレベルを下げないと使用できない、 >Vbprojectの操作が、その仕様が、セキュリティレベルを下げてまで必要なのか？ >という問いかけに 少なくともプログラマであれば、他人が納得できる説明は必要であろう >ということです。 >例えば、その仕様の有用性、Vbprojectを操作すること以外に実現不可能だということの説明、 >そのプログラムの使用者は、セキュリティレベルを下げることの危険性をわかっているか？
このような意見を記述しました。
コードの用途やプログラムを使用するのは、誰なのか？ 使用者のレベルがセキュリティを下げてもよい相手なのか？ 他に方法は、本当にないのか？
私からすれば、この判断のほうがずっと大事だと思います。
検討してみてください。
ichinose
議論はしないつもりの前提で 私の考え方はVBProjectの操作は「自己責任」の範疇のみです。 そのための知識やコードすら教えてもらって使うつもりもないですし 自信の責任で調べて出来る場合のみに限定して 尚かつ、そのコードの影響範囲が自信のPC内に限定される場合のみで その方法以外がどうしても無い場合、または有効な場合のみです。 もちろん私自身が使う会社のPCでも使いません。
自信でモジュールをインストールしたり書き換えたり出来ない相手に VBEのセキュリティを下げてまで自動的に配布するような仕様には反対ですので そこら辺が見えない内容でしたのであえて「怖い事」と書かせてもらいました。 （ｍｏｍｏ）
ご教示ありがとうございました。
無事に解決できました。
みなさま
いろいろとご教示＆ご意見ありがとうございました。
大変勉強になりました。これからも精進したいと思います。
ありがとうございました。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.