[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Linkを解除したいのです。』（Taku）
環境：Excel2002 Windows XP シートを新規のブックにコピー、ペーストした後、新しいブックのシートのLinkを 解除したいと思い、マクロ記録をして、下のようなマクロができました。
ActiveWorkbook.BreakLink Name:="E:\調整担当1017\物品請求\物品請求統合1015.xls",
Type:= xlExcelLinks
Range("B1:C2").Select
ActiveCell.FormulaR1C1 = "「3F わくわくひろば」平成20年10月分物品請求書"
この中で="E:\〜物品請求書.xlsと"「3F わくわくひろば」〜"の二つの部分を取得 する方法を教えて下さい。
このブックは、下のコードにより作成されます。
Private Sub CommandButton3_Click()
Dim ws As Worksheet
Dim wb As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets(Array("A", "B", "C", "D", _
"E", "F", "G", "H", "I", "J", "K", "L", "M", "N","O"))
If ws.Range("b8") <> "" Then
If wb Is Nothing Then
ws.Copy
Set wb = ActiveWorkbook
Else
ws.Copy after:=wb.Sheets(wb.Sheets.Count)
End If
End If
Next
MyFName = Format(Now(), "ggge\年m\月d\日HH\時MM\分SS\秒") & "の物品請求書"
FNameGet = Application.GetSaveAsFilename( _
InitialFileName:=MyFName, _
FileFilter:="Microsoft Excel ブック (*.xls) , *.xls", _
Title:="保存先の決定 ")
If wb Is Nothing Then Exit Sub
If TypeName(FNameGet) = "Boolean" Then Exit Sub
wb.SaveAs Filename:=FNameGet
Set wb = Nothing
Application.DisplayAlerts = True
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
よろしくお願いいたします。
> この中で="E:\〜物品請求書.xlsと"「3F わくわくひろば」〜"の二つの部分を取得する方法
何が分かっていて、何が分かっていないのかを明確にする必要があります。
１．"E:\〜物品請求書.xls" マクロの記録をした時、『"E:\〜物品請求書.xls』が選べた根拠は何でしょうか？
例 リンクファイルは一つしかない リンク元は、必ず『物品請求統合1015.xls』と云うブックで一つしかない リンク元は、必ず『物品請求統合****.xls』と云うブックで一つしかない
２．"3F わくわくひろば」〜" これは、手で入力したのですよね？
ご自分が知っていることではないですか？ 知らないとしたら、その情報にたどり着けるルート(手掛かり)はなんですか？ そのご説明がなければ、誰も分からないと思います。
(半平太)
>ActiveWorkbook.BreakLink Name:="E:\調整担当1017\物品請求\物品請求統合1015.xls",
↑
もしかして、この10をここに入れるだけ？
↓
「3F わくわく・・10月分・・書」
(半平太)
半平太さん、お世話になります。 1.のご質問ですが､ブックを保存するとき､MyFname=Format()〜で名前は決定されます。 そして、保存先はGetSaveAsFilenameで開くダイアログボックスで選択して決定されます。 2.のご質問ですが、入力シートでリストボックスから選択されたものとやはりリストボックス で選択された日付から作っております。実はこれがリンクされております。 この部分を他のセルで計算し､表示しておき､コピーしたら？とも考えたのですが､リンクを 解除する方法もあることを知り､チャレンジしてみることにしました。 上に書きましたように、ブックを作成した日時からブック名の数字の部分を作成しており、その 数字の前の部分は､月を意味しております。 従って､「もしかして、この10をここに入れるだけ？」ご推察のとおりです。
悪戦苦闘中です。
下のようなコードを組み込み、セーブした物品請求書を開けるようにし、リンクの
解除をしようとしたのですが、エラーです。
MyFName = Format(Now(), "ggge\年m\月d\日HH\月") & "物品請求書"
FNameGet = Application.GetSaveAsFilename( _
InitialFileName:=MyFName, _
FileFilter:="Microsoft Excel ブック (*.xls) , *.xls", _
Title:="保存先の決定 ")
If wb Is Nothing Then Exit Sub
If TypeName(FNameGet) = "Boolean" Then Exit Sub
wb.SaveAs Filename:=FNameGet
Set wb = Nothing
Application.DisplayAlerts = True
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Dim OpenFileName As String
OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls")
If OpenFileName <> "False" Then
MsgBox OpenFileName
Workbooks.Open OpenFileName
End If
ココから上は動いている
ココから下がエラーの元
ActiveWorkbook.BreakLink Name:= _
"C:\Documents and Settings\デスクトップ\調整担当\物品請求\物品請求.xls", _
Type:=xlExcelLinks
どなたか、教えて下さ〜い。
ｐPOYさんが作られたリンクを解除するプログラムをネット上で見つけ それを組み込みました。正常に動作しているようです。 これで当面の問題、リンクを解除することはできましたが、ブックを保存したあと の処理、保存したブックを開かずにリンクを解除する（保存をするためのボタンを 押したら、保存したブックのリンクを解除する。）迄、進みたいと思います。 どなたか、ご存知でしたら教えて下さい。 とりあえず、pPOYさん、半平太さん、有難うございました。
On Error GoTo Err_myChangeLink
Dim strLinks As Variant
Dim i As Long, j As Long, cnt As Long, rc As Integer
Dim myLinkName As String, stDocName As String
' Excelリンクのみ対象とする
strLinks = _
ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
j = 0
If Not IsEmpty(strLinks) Then
cnt = UBound(strLinks)
For i = 1 To cnt
myLinkName = strLinks(i)
stDocName = "「" & myLinkName & _
"」へのリンクがあります。値に変換しますか？" & vbCrLf & _
"それともリンク先を自ブックに変更しますか？" _
& vbCrLf & vbCrLf & _
"(はい=自ブックに変換、いいえ=値に変換、キャンセル=そのまま)"
rc = MsgBox(stDocName, vbYesNoCancel)
If rc = vbYes Then
' アクティブブックのリンク先変更
ActiveWorkbook.ChangeLink _
Name:=myLinkName, _
NewName:=ActiveWorkbook.FullName
j = j + 1
ElseIf rc = vbNo Then '※Excel97,2000では不可
' アクティブブックのリンク解除
ActiveWorkbook.BreakLink _
Name:=myLinkName, _
Type:=xlLinkTypeExcelLinks
j = j + 1
End If
Next i
MsgBox cnt & " 件のリンクの内、" & j & " 件の処理をしました。"
Else
MsgBox "外部Excelブックへのリンクはありませんでした。"
End If
Exit Sub
Err_myChangeLink:
If Err.Number = 1004 Then
MsgBox "リンク先を自ブックに変更できません。" & vbCrLf & _
"値に変更か、キャンセルを選択してください。", vbOKOnly
Else
MsgBox Err.Description
End If
(Taku)
すごく最初の疑問なのですが １．そのブックには何でリンクが有るのですか？
ちなみに ＞マクロ記録をして、下のようなマクロができました。 ２．この時の記録手順を教えてもらえますか？
それから ＞新しいブックのシートのLinkを解除したい は ３．「自ブックへのリンクし直し」と言う事ですか？
半平太さんの質問と重複する部分もありますが ４．リンクブックは一つ以上ありますか？
もう一つ ５．コピーした全てのシートにリンクがありますか？
ついでに ６．○○のセルには必ずリンクがある なんてセルが有るのでしょうか？
(HANA)
HANAさん、ありがとうございます。 入力用シートのセルに日付と何処の部門の請求なのかを合わせて請求書の頭書を作成 しております。この頭書を入力シートのあるブックのシート１５枚は参照しております。 そして、それが、各シートの頭書になります。 入力シートの操作により、これらのシートに部門から請求された品名、値段、注文コ ード等が入ります。入力シートのあるブックはかなり大きいので、請求書のみを保存し ます。すると頭書の部分が、入力シートとリンクされた状態で保存されてしまいます。 次に、入力シートの日付と何処の部門かが入っているセルの値を変更してしまえば、 当然の事ですが、作成し、保存したシートの値も変更されてしまいます。そこでシート を作成、保存した後に、それを呼び出し、リンクを解除し、値にすると言う作業を保存 用のボタンを押した時に同時にやってしまう、それもナイショでと考えたのです。 ブック１ 入力シートと１５枚の業者名が入ったシート 入力シートに業者名をリストから選択すると、注文できる品物がリストボックスに表示 される。その中から選択すると、品名、単価、注文数量、注文コード等が決定される。 それを業者名シートに転記する。 データの入った業者名シートだけを名前をつけて一旦保存する。この時、リンクが設定 されてしまう。 保存したブックを開き、リンクを解除して、セーブする。 これをやらないと保存した業者名シートのリンクが切れ、リンクにより表示されていた 頭書の部分が空白になってしまう。（入力シートは、入力が終わると、データをクリアし て閉じるため）
以上で、HANAさんのご質問の答えになっておりますでしょうか?
ご説明を読むと 「リンクされているセル番地は決まっている」 様に見えますが、違うのですかね？
＞この頭書を入力シートのあるブックのシート１５枚は参照しております。 シート１５枚のどのセルが参照しているのですか？ 「入力シートに○×商事宛の請求データを３件入力した際」 を例に、○×商事宛の請求書シートはどの様に成るのか サンプルデータを使って教えて下さい。
この様に表示されて このセルには この様な数式が入っている 等。
その際、嫌と言うほど説明書きも付けて下さい。 そちらでご使用中のシートが こちらでも作れるだけ 充分なご説明をお願いします。
(HANA)
HANAさん、ありがとうございます。そもそもがこのプログラムは、先輩から受け継いだ物 で、使い勝手の悪いところを改良しようと言うことではじめました。自分でもよく理解でき ていない部分があるのですが、なかなかの苦心作のようであり、完成させたいと思いチャレ ンジしております。
業者名シートの概略図 （業者名は、シートの見出しから引っ張り出す。） 入力シートでは、この業者名でオートフィルタをかけ抽出し、業者名にデータを書き込む） データは、下のようなものとなっております。
○○部××課 １１月分物品請求書（これは入力シートのリストボックスから 入力された○○部と××課と矢張りリスト から入力された年、月、日から作られます。）
納入期限 ○○年○○月○○日 発注先業者名 ○○商事株式会社
品名 単価 希望数量 金額 注文コード カタログ名等 ↑ ↑ 手入力 ↑ ↑ 入力シートで選択 品名によりVLOOKUP関数で入る 単価に同じ 単価に同じ 単価に同じ これらのデータ並びと入力シートの並びは品名の前にフィルターをかける為の業者名を 選択するリストボックスが配置されている以外は、ほぼ同じです。
入力シートの概略図 業者名 品名 色･サイズ等規格 希望数量 単価 金額 注文コード カタログ名等 ↑ ↑ 手入力 計算 矢印はリストボックスから選択、業者名を選択するとその業者から買うことのできる品名が リストのソースになるように設定。品名を選ぶと、手入力、計算と書かれた以外のデータは VLOOKUP関数で入力される。 ボタン１を押すと選択されている業者名によりフィルターをかけ、業者名シートにデータを 転記する仕組みになっております。 この中でリンクしているのは、日付と請求した部署名だけです。しかし、リンクが切れてし まうと何処の部署から何時請求された物かがわからなくなってしまい、請求としての役を果 たさなくなってしまうので困っており、自動的にリンクを解除したいと考えております。 (Taku)
ご説明、有り難う御座います。 少し分かってきたような気がします。
一つ教えて頂きたい事があります。
＞この中でリンクしているのは、日付と請求した部署名だけです。 のセル番地を教えて下さい。
シートをコピーした段階で コピー先の該当セル番地の内容を 値化して仕舞えばよい様に思います。
(HANA)
HANAさん、おはよう御座います。 お答えするのが遅くなりました。御免なさい。 各業者名のシートのB1〜C2（セルの結合をしております。） 因みに、次の数式が書き込まれております。 IF(入力シート!B3="","","「" & 入力シートB3 & "」" & TEXT(Date(入力シート!E2,入力シート!E3, 入力シート!E4),"ggge年m月分) & "物品請求書") この式を入力シートのセルに設定し､振り分けるときにそこから業者名シートにCopy&Pasteをすればとも 考えたのですが､定番品で無い物を請求するときの場合を考え非定番入力シートがあり、それとの関係で余 りにも複雑になってしまうことを考えてやった、とのお話があり､この方式を選びました。 現在､「保存」､「開く」、「リンクの解除」、「保存」は出来ているのですが､「保存」､「開く」の時 にダイアログが開き､チョットうるさい感じなのです。保存､開く、リンクを解除、保存を内緒でしたいの です。宜しくお願い致します。
(Taku)
条件に合ったシート（B8セルに入力がある）
を、新しいブックにコピーする部分が有りますよね。
実際のコードですと
If wb Is Nothing Then
ws.Copy
Set wb = ActiveWorkbook
Else
ws.Copy after:=wb.Sheets(wb.Sheets.Count)
End If
この部分です。
この下に、一行追加して下さい。
wb.Sheets(ws.Name).Range("B1").Value = wb.Sheets(ws.Name).Range("B1").Value
このコードは、wbブック（新しく出来たブック）の wsシート（その時コピーしたシート）のB1セルに入っているものを、値に変更します。 ワークシート操作では B1セルをコピー → 右クリック → 形式を選択して貼付(S) → ●値(V) としたのと同じです。
数式が入っていて、他のブックへリンクしてしまう様なセルが 各シートのB1(:C2結合)セルにしかないので有れば これで充分ではないかと思います。
上手く行きましたら、引き続きその下のご質問についても 詳しく教えて下さい。
・・・いや ＞="E:\〜物品請求書.xlsと"「3F わくわくひろば」〜"の ＞二つの部分を取得する方法を教えて下さい。 これは、二つの質問ではなくて B1(:C2結合)セルに数式で"「3F わくわくひろば」〜"が表示されている と言う事だったのですかね・・・？
でしたら、文字はそのまま維持されるので 解決でしょうか・・・。
(HANA)
HANAさん、本当に有難う御座いました。 あぁ！ ヤッタ！ 出来た！ です。 質問の仕方が悪かったのですね。リンクを解除しなければ！！！の一念でした。 えぇ､こういうやり方もあるの？ 状態です。これから理屈は参考書で調べます。 有難う御座いました。
新たに立てられていた質問をこちらへ移動させます。(HANA) ↓↓ ここから ↓↓
『Linkの解除2』（Taku） 先日､HANAさんに教えていただき、解決したのですが､ シートが、P の場合だけあるセルを参照する設定になっております。 そこで、教えていただいたコードに変更を加え､If ws.name= p then 以下（~~~がある部分） を加えたのですが､イケマセンデシタ。linkの解除が出来ません。 スミマセン！ どなたか教えてください。宜しくお願い致します。
Private Sub CommandButton3_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim Rv As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets(Array("A", "B", "C", "D", _
"E", "F", "G", "H", "I", "J", "K", "L", "M", _
"N", "O", "P"))
If ws.Range("b8") <> "" Then
If wb Is Nothing Then
ws.Copy
Set wb = ActiveWorkbook
wb.Sheets(ws.Name).Range("B1").Value = wb.Sheets(ws.Name).Range("B1").Value
wb.Sheets(ws.Name).Range("C4").Value = wb.Sheets(ws.Name).Range("C4").Value
' if ws.name = P then
~~~~~~~~~~~~~~~~~~~~
wb.Sheets(ws.Name).Range("C5").Value = wb.Sheets(ws.Name).Range("C5").Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
end if
~~~~~~
Else
ws.Copy after:=wb.Sheets(wb.Sheets.Count)
wb.Sheets(ws.Name).Range("B1").Value = wb.Sheets(ws.Name).Range("B1").Value
wb.Sheets(ws.Name).Range("C4").Value = wb.Sheets(ws.Name).Range("C4").Value
'テスト中 wb.Sheets(ws.Name).Range("C5").Value = wb.Sheets(ws.Name).Range("C5").Value
End If
End If
Next
えっとですね・・・
＞wb.Sheets(ws.Name).Range("B1").Value = wb.Sheets(ws.Name).Range("B1").Value
は、
× リンクの解除 ×
をしているのではなく
●形式を選択して値貼り付け●
をしています。
B1:C5の範囲に 「値になっては困る数式が入ったセル」 は有りませんよね。 でしたら「Pのシートの場合は・・・」なんて 考える必要も無いと思いますが。
ちなみに、このスレだけだと話が全く分からないと思いますので 前のスレに続けて書いていただいた方が良かったのではないかと思います。 （或いは、事の経緯を最初から説明する とか。）
↓その１ 『Linkを解除したいのです。』（Taku）
(HANA)
HANAさん、お世話になります。 前のスレッド、探したのですが､見つからず新しく書き込んでしまいました。ご指摘を受け､見 に行ってきたのですが､長すぎて､何処から何処まで説明してよいか判らないので、ここを閉じ、 前のスレッドに続けて書き込ませていただきます。有難う御座いました。 (Taku)
↑↑ ここまで ↑↑ 新たに立てられていた質問をこちらへ移動させました。(HANA)
HANAさん、スミマセンでした。宜しくお願い致します。
Linkの解除と書き込んでしまったのは､全くの間違いです。教えていただいたこと、ネットや参考書でしらべま
した。
質問ですが､
シートPのC5には、=IF(非定番入力!C5),"","".非定番入力!C5)
その他のシートのC5には、
MID(CELL("filename"<$A$1),FIND("{".CELL("filename",$A&1))+1,LEN(CELL("filename",$A$1))-FIND
("]",CELL("filename",$A$1)))
と書き込まれおります。シートPのC5は外部ファイルの参照、その他シートのC5は自シート見出し名。
この関係からか、「あちら立てればこちらが立たず」状態になってしまいます。
これを解消するために考えたのですが､素人の浅知恵、駄目だった！！！です。
解決方法を教えてください。宜しくお願い致します。
他のシートのC5セルには 消えてはいけない数式が 入っていたのですね。
では、Pのシートだけは違う処理にした方が良さそうですね。
取りあえず「P」はシート名（文字列）ですから「""」で囲ってやってみて下さい。
if ws.name = "P" then
~~~~~
ちなみに、入れる場所は、先の御質問終了時に
「この下に、一行追加して下さい。」
で
If wb Is Nothing Then
ws.Copy
Set wb = ActiveWorkbook
Else
ws.Copy after:=wb.Sheets(wb.Sheets.Count)
End If
wb.Sheets(ws.Name).Range("B1").Value = wb.Sheets(ws.Name).Range("B1").Value
となったと思いますが、その下に入れるので良いと思います。
＞何処から何処まで説明してよいか判らないので すみません、そんなに大層な事ではなかったのですよ・・・。 最終的には「値化」で落ち着いたので 事の経緯をまとめてもらった後 （コピー前のシートのどのセルにどんな数式が入っているか。 そのシートを複製したら、その数式がコピー元を参照するので 値化して保存する事にした。） 今回のご説明 （各シートのC5セルにも数式が入っているがPシートは値化し Pシート以外は数式を残しておきたい＋それぞれ入っている数式 ＋コード） を書いてもらえば、分かりやすいと思ったのです・・・。
コードの方は「""」の追加で上手く動くようになると良いですが。
(HANA)
HANAさん、ご返事遅くなり失礼いたしました。 おかげさまでエラーも無く動くようになりました。このエクセルシートを 使って、請求をなさる方々に使い方を説明して歩きました。 使っていただき、予期せぬエラーが出たらどうしょうとビクビクしております。 もう2度と手出しはしまい! と言うのが現在の心境です。 それにしても、HANAさんには、徹底的にお付き合いいただき、教えていただき ありがとうございました。 エラーが出て、対応できない時には宜しくお願いいたします。
(Taku)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.