-PR-
解決済み

Excel VBAで参照先の変換

  • 困ってます
  • 質問No.9342994
  • 閲覧数66
  • ありがとう数2
  • 気になる数0
  • 回答数1
  • コメント数0

お礼率 70% (17/24)

ExcelのVBAでBook1.xlsxのSheet2とSheet2を参照しているSheet3をBook2.xlsxにコピーするプログラムを作成しているのですが、Sheet3のコピーが思うように行きません。
具体的にはSheet3が参照しているSheet2をBook1.xlsxからBook2.xlsxに変換する所です。

下記のようなプログラムを作成しました。
strDirectory = ThisWorkbook.Path
strSrcBook = "Book1.xlsx"
Workbooks.Open Filename:=strDirectory & "\" & strSrcBook, ReadOnly:=True
Set wsSrc = Workbooks(strSrcBook).Worksheets("Sheet2")
Set wsDst = ThisWorkbook.Worksheets("Sheet1")
wsSrc.Copy After:=wsDst
Set wsSrc = Workbooks(strSrcBook).Worksheets("Sheet3")
Set wsDst = ThisWorkbook.Worksheets("Sheet2")
wsSrc.Copy After:=wsDst
wsDst.Range("D:D").Replace "[*]", ""
Workbooks(strSrcBook).Close SaveChanges:=False

Sheet3のD列がSheet2を参照しているのですが、上記を実行すると開くファイルの選択を要求され、キャンセルし続けると実行が完了しますが、コピーされたSheet3の参照先が正しく変換されずエラー表示となってしまいます。
「wsDst.Range("D:D").Replace "[*]", ""」が完了する前に「Workbooks(strSrcBook).Close SaveChanges:=False」が実行されてしまうのが原因と思われるのですが、実際のプログラムでは開くBookは1つではなくSheet1に記載したリストを順番に開いてコピーするという事を行っている為、作業が完了したBookは閉じるようにしたいです。
どの様に修正すれば「wsDst.Range("D:D").Replace "[*]", ""」が完了するのを待って、「Workbooks(strSrcBook).Close SaveChanges:=False」が実行されるように出来るのでしょうか?
通報する
  • 回答数1
  • 気になる
    質問をブックマークします。
    マイページでまとめて確認できます。

質問者が選んだベストアンサー

  • 回答No.1
レベル11

ベストアンサー率 82% (178/215)

他カテゴリのカテゴリマスター
こんにちは。

理解が至っているか判りませんが、
常に2つのシートを、丸々シートごと、
自ブックにコピーする作業を繰り返す、ということだとして。

コピー先で外部ブックへの参照名を置換で消したとしても、
シート名がソースブックとデストブックで
必ず一致するとは限りませんよね。
 "Sheet2" が既存であれば、"Sheet2 (2)" とか。
必ずしも、この点が原因でうまく行っていないという事でも
無いかも知れませんが、不確定要素を無理に抱えるような
処理方法という印象です。

シートふたつを纏めてコピーすれば、
シート間の参照関係もそのまま(自己完結するように)コピーできます。
なので、コピー後に参照先を変換する必要はないのでは?と。

ふたつを纏めてコピー。例えば、以下のように。

' ' //
' Dim colWsSrc As Sheets
  strDirectory = ThisWorkbook.Path
  strSrcBook = "Book1.xlsx"
  Workbooks.Open Filename:=strDirectory & "\" & strSrcBook, ReadOnly:=True
  Set colWsSrc = Workbooks(strSrcBook).Worksheets(Array("Sheet2", "Sheet3"))
  Set wsDst = ThisWorkbook.Worksheets("Sheet1")
  colWsSrc.Copy After:=ThisWorkbook.Worksheets("Sheet1")
  Workbooks(strSrcBook).Close SaveChanges:=False
' ' //
このQ&Aのテーマ
このQ&Aで解決しましたか?
関連するQ&A
このやり方知ってる!同じこと困ったことある。経験を教えて!
このQ&Aにはまだコメントがありません。
あなたの思ったこと、知っていることをここにコメントしてみましょう。

その他の関連するQ&A、テーマをキーワードで探す

キーワードでQ&A、テーマを検索する
-PR-

特集


開業・独立という夢を持つ人へ向けた情報満載!

ピックアップ

ページ先頭へ