Excelで、異なるブック間での同一シートの共有？

質問

こちらのカテゴリーでは初めてお世話になるかと思います、
r_belと申します。どうぞよろしくお願いいたします。

フォルダA
│
├─ フォルダ1
│├─ ExcelBook1.xls
│├─ linked_email1.msg
│└─ linked_doc1.pdf
│
├─ フォルダ2
│├─ ExcelBook2.xls
│├─ linked_email2.msg
│└─ linked_doc2.pdf
│
├─ フォルダ3
│├─ ExcelBook3.xls
│├─ linked_email3.msg
│└─ linked_doc3.pdf
│
└ SummaryBook.xls

このような構成のファイルとフォルダがあります。

それぞれのフォルダの中には、その中にある
エクセルファイル（例：ExcelBook1.xls）上から
ハイパーリンク機能を使って、相対パスでemailやpptやpdfなどの
参考資料を指定してあるので、このように整理しています。
（相対パスだと各フォルダを動かしたり、
名前を変更してもリンクに支障がでないので。）

このとき、ExcelBook1.xls ～ ExcelBook3.xls
を一つずつ開くのではなく、SummaryBook.xlsの中で
まとめて閲覧するなんてことはできますでしょうか？
初心者なので、できればVBAやマクロは使わずにすめば大変ありがたいのですが・・・。

ExcelBook1.xls ～ ExcelBook3.xlsは結構頻繁に更新するものなので、
シート全体をリンク貼り付けする、あるいは複数のブックで
同一のシートを共有するようなイメージでいるのですが、
このようなことは可能でしょうか？

どうぞよろしくお願いいたします。

回答No.2

x0000x · ベストアンサー

#1です。

>基本的にシートの構成はみな同じなので、
>セル参照などとVBAを組み合わせればきっと可能なのでしょうが、

ご希望のイメージと違うかも知れませんが、
VBAなら以下の通りです。

<処理の条件>
SummaryBook.xlsをOPENする際、SummaryBook.xlsのSheet1に
ExcelBook1.xls ～ ExcelBook3.xlsの各Sheet1を順に読込ます。
この読込の際、HyperLinkのアドレスを変更します。
（ただし、HyperLinkが格納されている列は”C列”にしてあります。）

以下コードです。
SummaryBook.xlsのマクロとして登録します。
Alt+F11で表示させ、ThisWorkbookを開き、コードを貼り付けます。

”★”コメントを任意に変更した上で、お試しを！！

'WorkbookのOpenEvent
Private Sub Workbook_Open()

'Summary Bookの一覧表シートをSheet１に設定します。
Dim summarySheet As Worksheet
Set summarySheet = ActiveWorkbook.Worksheets("Sheet1") '★Summrybookのシート名

'一覧表をクリアする。
summarySheet.Cells.Select
Selection.ClearContents

'セルをCopyする
Dim setRowIndex As Long

'★以下のcopyCells()の引数を変更。順番は、対象ブックのPATH、ブック名、シート名、読込開始行、格納行、summarybookのシート
setRowIndex = copyCells("フォルダ1\", "ExcelBook1.xls", "Sheet1", 1, 1, summarySheet)
setRowIndex = copyCells("フォルダ2\", "ExcelBook2.xls", "Sheet1", 1, setRowIndex, summarySheet)
setRowIndex = copyCells("フォルダ3\", "ExcelBook3.xls", "Sheet1", 1, setRowIndex, summarySheet)

'終了
Set summarySheet = Nothing
End Sub

Private Function copyCells(ByVal path As String _
, ByVal bookNM As String _
, ByVal sheetNM As String _
, ByVal CopyRowIndex As Long _
, ByVal setRowIndex As Long _
, ByVal listSheet As Worksheet) As Long

'指定されたブックの指定シートを一覧シートにコピーする。
'Args path : Copy元の格納PATH　…文字列の最後は"\"で終了
' bookNM : Copy元のブック名 … Path + ブック名で指定
' sheetNM : Copy元のシート名
' CopyRowIndex : Copy元の開始行　　…　最初は見出し行を指定、2回目からはデータの先頭を指定
' setRowIndex : 一覧に格納する開始行位置
' listSheet : 一覧表シート

'画面更新を止める
Application.ScreenUpdating = False

const colHyperlink as String = "C" '★HyperLink列　確認
Dim srcBK As Workbook
Dim srcST As Worksheet
Set srcBK = Workbooks.Open(path & bookNM)
Set srcST = srcBK.Sheets(sheetNM)

If setRowIndex = 1 Then

'最初のCopyでは、列幅もCopy元にあわせる
srcST.Cells.Copy Destination:=listSheet.Range("A" & setRowIndex)

Else

'2回目以降は行単位でCopyする
Dim maxRowIndex As Long
maxRowIndex = srcST.Rows(srcST.Rows.Count).End(xlUp).row '格納最終行を求める

srcST.Rows(CopyRowIndex & ":" & maxRowIndex).Copy Destination:=listSheet.Range("A" & setRowIndex)

End If

'一覧表内で次に格納可能な行を戻す。
copyCells = listSheet.Rows(srcST.Rows.Count).End(xlUp).row + 1

Dim row As Long
For row = setRowIndex To copyCells - 1 '格納最大まで
If listSheet.Cells(row, colHyperlink).Hyperlinks.Count > 0 Then
'リンクが存在した場合にアドレスを変更する
Dim cnt As Integer
For cnt = 1 To listSheet.Cells(row, colHyperlink).Hyperlinks.Count

'HyperlinkのアドレスにPATHを付加し、Summarybook.xlsからの参照を可能にする
listSheet.Cells(row, colHyperlink ).Hyperlinks(cnt).Address _
= path & listSheet.Cells(row, colHyperlink).Hyperlinks(cnt).Address

Next
End If
Next

'ブックを閉じる(更新なし）
srcBK.Close SaveChanges:=False

Set srcST = Nothing
Set srcBK = Nothing

'画面更新を復活
Application.ScreenUpdating = True

End Function

お礼

結果報告です。

'★以下のcopyCells()の引数を変更。順番は、対象ブックのPATH、ブック名、シート名、読込開始行、格納行、summarybookのシート
setRowIndex = copyCells("ex1\", "ex1.xls", "happysheet", 1, 1, summarySheet)

とセットし、保存してファイルを開こうとしますと、

実行時エラー '1004':
'ex1\ex1.xls'が見つかりません。
ファイル名およびファイルの保存場所が正しいかどうか確認してください。

というエラーが出てしまい、デバッグすると
下記の行がハイライトされてしまいます。

Set srcBK = Workbooks.Open(path & bookNM)

フォルダもファイルもきちんと存在していますし、
上記の位置（SummaryBook.xlsと同じ階層にフォルダがあり、
ex1\ex1.xlsが相対パスとして正しいと思うのですが、
別のフォルダーを指定してもやはり同じ結果で、
最初に読み込もうとする行でつまいでしまいます。

こんなに書いていただいて本当に申し訳ないのですが、
どうやら私にはやはりこのように長くて複雑なマクロで、
というのはまだまだ難しすぎる作業だと痛感しました・・・。

今回いただいたアドバイスは、ちゃんと保存しておいて、
いつかもっと腕を磨いてからまた挑戦させていただきたいと思います。

本当にありがとうございました！

補足

うう、感激です。本当にありがとうございます。
お返事が遅れ、大変失礼いたしました。
実は今引越し中でネット・パソコン環境がまだ整っていないのですが
近日中に今回作っていただいたコードにじっくりと
取り組んで、勉強させていただき、結果報告をさせていただきます。
本当にありがとうございました！！！

回答No.1

x0000x

こんにちは。

>VBAやマクロは使わずに…
>シート全体をリンク貼り付けする、あるいは複数のブックで同一のシートを共有するようなイメージでいるのですが、
>このようなことは可能でしょうか？

［図のリンク貼り付け］には以下の制限がつきます。
制限事項の3番目により、質問者さんの意図する操作は難しいかも知れませんね。（他のVerで更新できるならOKですが…）

＜制限事項：Excel2003で確認＞
■シート全体の貼り付けは動作的に無理（処理が重い）があると思うので、ExcelBook1.xls ～ ExcelBook3.xlsの対象範囲を明確にした上で
行なう方が良いです。
対象範囲の内側で操作した内容は行追加も含め、リンク先に反映できます。

■リンク元（ExcelBook1.xls ～ ExcelBook3.xls）のPATHが変更された場合には最後に更新したイメージが表示されます。
（再リンクで元に戻ります）

■リンク元（ExcelBook1.xls ～ ExcelBook3.xls）に変更を加えた後、リンク元が閉じている状態でSummaryBook.xlsを開いても、直前の内容で表示できません。SummaryBook.xlsの最後に保存したイメージが表示されます。
（SummaryBook.xlsの該当の図をクリックする事で、リンク元を開くことが可能です。）
（リンク元が表示されている場合には、最新イメージで表示されます。）
■SummaryBook.xlsに表示している、貼り付けた図から直接ハイパーリンク先（emailやpptやpdfなど）は開けません。

上記から、
>このとき、ExcelBook1.xls ～ ExcelBook3.xls
>を一つずつ開くのではなく、SummaryBook.xlsの中でまとめて閲覧する

については、
SummaryBook.xlsを開くことで、ExcelBook1.xls ～ ExcelBook3.xlsを簡単にOPENできます。
また、ExcelBook1.xls ～ ExcelBook3.xlsの更新が無ければ、最終イメージをまとめて閲覧可能です。
ExcelBook1.xls ～ ExcelBook3.xlsの更新を反映させるには、貼り付けた図をクリックしてExcelBook1.xls ～ ExcelBook3.xlsを開く必要があります。

ちなみに、［図のリンク貼り付け］操作は以下です。

１）図として貼り付けたい表を範囲選択
２）［Ctrl］キー＋［C］キーを押す
３）［Shift］キーを押しながら、メニュー［編集］－［図のリンク貼り付け］をクリック

以上、参考まで。

お礼

色々とご丁寧にご指導くださり、ありがとうございました。
実は私も図のリンク貼り付けは最初にやったのですが、
ご指摘のように色々と不都合があり、
今回こちらに投稿させていただいた次第です。

基本的にシートの構成はみな同じなので、
セル参照などとVBAを組み合わせればきっと可能なのでしょうが、
まだ自分にはそこまでスキルが無く・・・。
やはり希望のようなものを作るのは難しいのでしょうか・・・。