エクセル 別ファイルから シートを リンクを張らずに コピーしたい

質問

エクセル２００３

１～４　　はファイルの状態の説明です。
やりかたを質問したい部分は　５です。　

１、ファイル（ブック）Ａ．xls　→　計算式が大量に入った数シートからなるファイル　があるとする。

２、Ａ．xls　を　そのままコピーし コピーされたファイルは Ｂ．xls　に　リネーム。

３、Ａ．xls　は　計算式等は変更しないが、データを変更し通常業務で使用。

４、Ａ．xls　の新バージョンを作るために Ｂ．xls　を使って計算式等を編集。
（複数あるシートのうち一部シート（シート名 「あいうえ」　とする）のみの変更に加え
さらに新シート「さしすせ」を作成）

５、Ａ．xls　のシート名 「あいうえ」をＢ．xlsで作った シート名 「あいうえ」と置き換え、
さらに新シート「さしすせ」もＢ．xlsからコピーして追加する。

　　普通にシート名 「あいうえ」や　新シート「さしすせ」を選択し
　コピーを行うと　Ｂ．xls　からリンクを張った状態でコピーされて
　しまう。たとえば　シート「あいうえ」セルＡ１の中身を見ると
　=[B.xls]あいうえ!A1*2+1　といったように　[B.xls]が付いて
　コピーされる。
　これを回避して、単にそのまま丸ごと（Ａ．xlsの中だけで編集したかのように）
シートをコピーするにはどうしたら　よいのでしょうか？

　長くなってすいません。
　よろしくおねがいいたします。

回答No.9

mt2008

ANo.8の補足です。
「'=」にしてしまうと、式に戻すとき、一気に置換できなくて面倒でしたね。
「=」→「#」辺りに置換して文字列にして下さい。
また、試してみて判りましたが、Excel2003の場合、セル内の式が912文字以上の場合、置換も出来ない様です。
もし、B.xls内のセル式が912文字以上の場合は１つ１つ文字列に変換してやる必要が有ります。

回答No.8

mt2008

ANo.5です。
セル式の文字数制限に引っかかるんですね。
それなら、B.xlsの追加したいシート上のセル式を「=」→「'=」の置換で一旦文字列にしてしまっては如何でしょう。ただし、文字列であってもシートのコピーでは255バイトで切られてしまいます（Excel2003の場合）ので、文字列にしたうえでシートの移動でA.xlsに持っていきます。
その後、置換をしなおしてセル式に戻します。

なお、B.xlsからシートを移動しますので、B.xlsはあらかじめコピーを取って置きます。

回答No.7

ASIMOV

この質問は、
[B.xls]を付けずにコピーする方法．．．ではなく
[B.xls]が付くとセルの文字数制限（２５５文字でしたっけ？）に引っかかって、式が壊れるのを回避する方法．．．な訳ですよね

さて、シートのコピーを逆にしたらどうなりますか？
B.xlsにA.xlsの参照される方のシートをコピーするのです
これで問題（式が壊れるなど）なければ
B.xlsの現在参照されているシートを削除します
そうすると参照している式には #REF! が付きます
　あいうえ!A1*2+1　->　#REF!A1*2+1
エラーにはなりますが、文字数が増えなければ式は壊れないはずです
そこで、「置換」を使って
　#REF!　->　新シート名!
に置換えてはどうでしょうか？
（新シート名は短い名前に変えておいた方が良いかも）

回答No.6

ookami1969

面倒くさいから Ｃ.xlsに値で貼り付けて編集しては？

補足

アドバイスありがとうございます。

それも考えたのですが、しかしそうもいかないので
質問させていただいたしだいです。

ありがとうございました。

回答No.5

mt2008

こんな手はどうでしょう。
質問の5まで行ってあるとします。
1.B.xlsを終了して一時的に名前を変えます。例：B.xls→BB.xls
2.[B.xls]が付いてしまっているA.xlsを名前をつけて保存でB.xlsとして保存終了します。
3.上記2で保存したB.xls(元A.xls)を開く。
これで[B.xls]が消えているはずです。後は正しい名前A.xlsで保存しなおし、BB.xlsを元のB.xlsに戻して完了。

補足

回答ありがとうございます。
返信が遅れ申し訳ありません。

結果として下記理由からＮＧですが、
しかし、こんな単純な発想があるのに、
思いつかないものですねぇ・・・
自分の頭の固さが身にしみました。

NO3までの補足でも示しているとおり

１つのセルに入っている計算式があまりに長く
加えて、パス名とファイル名が式の中のそれぞれの参照セル名の
頭に付加されるととんでもなく長い式となり、「長すぎます」との
エラーで式がすべて壊れてしまいます。

つまり Ｂ．xlsからＡ．xlsにコピーした時点で
リンクが張っていない状態でないと、だめなのです。

気持ちのよい回答ありがとうございます。

回答No.4

mitarashi

アクティブブックの他ブックへのリンクを、アクティブブック自身へのリンクに書き換える（実質削除）マクロですが、お役に立たないでしょうか？
Sub test()
Dim alinks As Variant
Dim i As Long

alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(alinks) Then
For i = 1 To UBound(alinks)
ActiveWorkbook.ChangeLink Name:=alinks(i), NewName:=ActiveWorkbook.Name, Type:=xlExcelLinks
Next i
End If
End Sub

補足

ご意見ありがとうございます。
返信が遅れ申し訳ありません。

NO3までの補足でも示しているとおり

１つのセルに入っている計算式があまりに長く
加えて、パス名とファイル名が式の中のそれぞれの参照セル名の
頭に付加されるととんでもなく長い式となり、「長すぎます」との
エラーで式がすべて壊れてしまいます。

つまり Ｂ．xlsからＡ．xlsにコピーした時点で
リンクが張っていない状態でないと、だめなのです。

しかし、参考にはなりました。
ありがとうございます。

回答No.3

gyouda1114

質問が良く理解できませんが

シートを別のブックにコピーする方法です。
この方法だとリンクは張られないはずですが
http://ii.cmt.kpu-m.ac.jp/~asano/kiso/topics/excel/kihon/top.html#d5

「Ａ．xls　」が開いていれば移動先ブック名に「Ａ．xls　」が表示されます

補足

それはすでにやりましたが
どうしても
元は　=あいうえ!A1*2+1　なのに
=[B.xls]あいうえ!A1*2+1　といったように　[B.xls]にリンクが
張られてコピーされてしまいます。

回答No.2

ASIMOV

[B.xls]を「置換」で削除すればどうでしょう

補足

１セルに入っている計算式が
とてつもなく長いため
[B.xls]がいくつも付いてしまい
式が壊れたり
または
「長すぎます」とのことで置換もききません。
もちろん一度保存してからでも同じです。

たとえばＥ１２のセルには
=IF(あああ!$F6>SUMPRODUCT((いいい!O$4:O$203<>"")*(いいい!P$4:P$203=$C$7)*(いいい!N$4:N$203=$B$8)*(いいい!K$4:K$203<>"t")*(いいい!K$4:K$203<>"T")*(いいい!K$4:K$203<>"ｔ")*(いいい!K$4:K$203<>"Ｔ")*(いいい!I$4:I$203=$B$5)),"",INDEX(INDIRECT($AR$3&"!"&$C$5&"$1:"&$C$5&"203"),SMALL(INDEX(SUBSTITUTE((いいい!O$4:O$203<>"")*(いいい!P$4:P$203=$C$7)*(いいい!N$4:N$203=$B$8)*(いいい!K$4:K$203<>"t")*(いいい!K$4:K$203<>"T")*(いいい!K$4:K$203<>"ｔ")*(いいい!K$4:K$203<>"Ｔ")*(いいい!I$4:I$203=$B$5),0,10^6)*ROW(いいい!$M$4:いいい!$M$203),),あああ!$F6)))

という式が入っており（「あああ」と「いいい」は実際には異なる名称）
それが１シーに４０～１００セルにもわたって入っており
ブック自体の要領は５Ｍほどにもなっています。

このそれぞれのセル位置の前にいちいち[B.xls]（実際にはもっと長いファイル名とパス名が入ってしまう）が挿入されめちゃめちゃに
なってしまう。

回答No.1

fujillin

とりあえずそのままコピーしてしまってから、検索・置換で[B.xls]　をnull（=空白）に一括置換すればよろしいかと。

補足

１セルに入っている計算式が
とてつもなく長いため
[B.xls]がいくつも付いてしまい
式が壊れたり
または
「長すぎます」とのことで置換もききません。
もちろん一度保存してからでも同じです。