EXCEL2007での「リンクの編集」について

質問

EXCEL2007&WindowsXPを使用しております。

月ごとにブックを分けていて、5月のブックの5月1日の行には、「200805フォルダ」の「0501ファイル.xls」の「A1」にリンクしています。
6月のブックを作成してリンク先を「200806フォルダ」の「0601ファイル.xls」の「A1」にリンクを変更したいと思っております。

その場合、ダイアログで順次行っても良いのですが、1つのファイルに1000以上の同様のリンクが貼ってあり、手作業では時間がかかってしまいます。

そこで、置換で「200805」を「2000806」、「0501」を「0601」に仕様と思ったのですが、データの容量が多いためか、置換が出来ずに途中で止まってしまいます。

何か良い方法はないものでしょうか？よろしくお願い申し上げます。

回答No.5

hallo-2007 · ベストアンサー

すみません。No4のマクロミスがありました。
自分なりにもう少しまとめてみましたので以下参考にしてください
新しいブックの　最初のシートのA1にフォルダのパスを入れる

Sub ブック結合()
Dim buf As String, i As Long
Dim j
buf = Dir(Range("A1").Value & "\*.xls")
Do While buf <> ""
i = i + 1
Workbooks.Open Worksheets(1).Range("A1").Value & "\" & buf
For j = 1 To Worksheets.Count
ThisWorkbook.Worksheets(1).Cells(i + 1, j + 1) = Sheets(j).Name
Worksheets(j).Copy After:=ThisWorkbook.Worksheets(1)
Workbooks(buf).Activate1 'この行忘れてました。
Next
ThisWorkbook.Worksheets(1).Cells(i + 1, 1) = buf
Workbooks(buf).Close SaveChanges:=False
buf = Dir()
Loop
End Sub

Sub シート結合()
Dim Sh1
Set Sh1 = Worksheets(1)
Dim i
For i = 2 To Worksheets.Count
With Worksheets(i)
.Rows(1).Resize(.Cells.SpecialCells(xlLastCell).Row).Copy _
Destination:=Sh1.Rows(Sh1.Cells.SpecialCells(xlLastCell).Row + 1)
End With
Next i
End Sub

回答No.4

hallo-2007

まだスレが空いていましたので、参考までに
指定したフォルダ内のエクセルのファイルをひとつにまとめるマクロ
です。アクセスにデータまとめるにしても、ひとつの方が便利では。
役に立つようであれば、使ってください。

新しいブックを作成して、VBエディターに以下コピィ
Sampl　１は
フォルダ内にエクセルの全てのファイルを開いて全てのシートをコピィしてひとつにします。
Sub Sample1()
Dim buf As String, i As Long, j As Long
buf = Dir("D:\○○○\*.xls")
Do While buf <> ""
i = i + 1
Workbooks.Open "D:\○○○\" & buf
ENDSH = Worksheets.Count
For j = 1 To ENDSH
Worksheets(j).Copy After:=ThisWorkbook.Worksheets("Sheet1")
Next
Worksheets("Sheet1").Cells(i, 1) = buf
Workbooks(buf).Close SaveChanges:=False
buf = Dir()
Loop
End Sub

以下は、それぞれのシートのファイルをひとつのシートにまとめます。
但し、A列に必ずデータがあること、1シートMAX1000行です。
また、日付なども列に入れておかないと意味が無いと思います。

Sub Sample2()
ENDSH = Worksheets.Count
For i = 2 To ENDSH
Sheets(i).Rows("1:1000").Copy '1シートの行数増やす場合はここを増やす
Sheets(1).Range("A65536").End(xlUp).Offset(2, 0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Next
End Sub

回答No.3

hallo-2007

少し状況が理解できました。
毎月ごとにフォルダがあり、フォルダ内に1日ごとにファイルがあるのですね。
1日のデータは何行なのでしょうか？正直いって、もっとも効率の悪いデータの入力の仕方です。
1日のデータ数にもよりますが、データは1枚のシートに日付の列を加えて、ひたすら縦方向へ入力していくべきです。
ひとつのファイルが非常に大きくなるようであれば、アクセスを利用します。

以下、参考になればと思います、新しいブックで確認してください。
　　A　　　B　　　C
1　5月1日
2　5月2日
・・・
A列には、日付がシリアル値で入っているとして
B1に　
="C:\"&TEXT(A1,"yyyymm")&"\"&TEXT(A1,"mmdd")&".xls"
式入れて、下フィルしてみてください。
ご希望のリンク先が文字列で表示されます。

実際のファイルでは、INDIRECT関数と組み合わせて応用してみてください。

お礼

遅くなりましたが、ご返答ありがとう御座いました。

大変参考になりました。

現在の状況は、列が足りなくなったため1つのシートに2つの表があったり、EXCELを多少かじった事がある私でも正直疑問に思っていました。
容量が大きく、リンクも複雑のためローカルで作業する場合でもファイルを開くのに5分ぐらいかかります。

ご指摘頂いたように、EXCELではなくACCESSにすべきだと考えております。

いずれにしても大変参考になりました。ありがとう御座いました。

回答No.2

hallo-2007

すみません。No1です。少し勘違いしていたかもしれません。
>1つのファイルに1000以上の同様のリンクが
が問題なのですね。
例えば A列にのリンクが記述してあるとして
適当なセルに（仮にB2に)　'C:\200806\0601\Sheet1'!A1とか文字列で入れておいて
リンクのセルには
=INDIRECT($B$2) で貼り付けておけば、次からはB2セルを変更するだけですむと思います。

お礼

はじめまして。ご回答ありがとう御座いました。

私の質問の仕方が良くなく分かりづらくてすみません。

5月のブックが　5月1日の行が\200805\0501.xlsのいくつかのセルにリンクされています。5月2日の行が\200805\0502.xlsのいくつかのセルにリンクされています。そして5月31日まで続いています。
この5月のブックをコピーして6月のブックにします。
これまで\200805\0501.xlsにリンクされていたものを\200806\0601.xlsにリンクを変更したいのです。指定するシート・セルは同じでファイル名だけを変えたいのです。勿論、6月2日～30日も同様です。

置換を使って、「\200805\05」→「\200806\06」としようと思ったのですが、非常に処理が重く途中でエラーになってしまいます。そこで何か良い方法はないものかと考えております。

※私なりにがんばりましたが、ご返答頂いた内容がよく理解できない状況です。勉強不足で申し訳ございません。もし、頂いた内容であっていればもっと調べてみたいのですが・・・

どうぞよろしくお願い申し上げます。

回答No.1

hallo-2007

質問がうまく理解できていないかもしれません。
>6月のブックを作成してリンク先を「200806フォルダ」の「0601ファイル.xls」の「A1」にリンクを変更したいと思っております。
6月.xlsとかのファイルがあって、それぞれのセルに[\200806\0601.xls]Sheet1!$A$1
とか入っているのでしょうか？
役に立つかどうかわかりませんが、6月.xlsの適当なセルに
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
=MID(CELL("filename",A2),FIND("[",CELL("filename",A2))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-5)
を貼り付けてみてください。
セルに6月.xlsのパスとファイル名(6月?)が表示されると思います。
後は、INDIRECT関数を利用してリンクするファイルを指定できないでしょうか。
一度、この方法でブックを作成しておけば、新しいブックを作成して、名前をつけて、保存した時点でリンク先が自動で変更されると思います。