みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

Excel VBA 非表示の別ブックへシートコピー

Excel2010のVBAで、別のExcelブックを非表示で開いて、
シートをコピーすると、
「実行時エラー'1004':WorksheetクラスのCopyメソッドが失敗しました。」
というエラーが出て、正しくシートをコピーすることができません。

(1)のように自分のブックへはシートをコピーすることはできるのですが、
(2)のように別のExcelブック上でシートをコピーする場合と
(3)のように別のExcelブック上にシートをコピーする場合の
いずれも同様のエラーになります。

どのように記述すれば(2)と(3)でもコピーすることができるのでしょうか。

-------------------------------------------------------------
Sub test()

 Dim newEx As Excel.Workbook
 Dim newFile As String

 newFile = ThisWorkbook.Path & "\New_Book.xlsx"
 Set newEx = Workbooks.Open(newFile, UpdateLinks:=0)

 Application.Windows("New_Book.xlsx").Visible = False

 '(1)New_BookのSheet3を自分のブックにコピーする (正常)
 newEx.Worksheets("Sheet3").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

 '(2)New_BookのSheet3をNew_Bookにコピーする (エラー)
 newEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

 '(3)自分のブックのSheet3をNew_Bookにコピーする (エラー)
 ThisWorkbook.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

 Application.Windows("New_Book.xlsx").Visible = True

 Application.DisplayAlerts = False
 newEx.Save
 newEx.Close
 Application.DisplayAlerts = True
 Set newEx = Nothing

End Sub
-------------------------------------------------------------

投稿日時 - 2015-11-18 16:29:00

QNo.9082259

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

こんにちは
タスクバーのちらつきを抑えるなら別インスタンスで開けばいいのですが、
(1)、(3)がエラーになってしまうので、シートを追加してセルをコピーにしてみました。
ダメでしょうか?
Sub test1()
Dim newEx As Workbook
Dim newFile As String
Dim objEX As Object
Dim tmpS As Worksheet

Set objEX = CreateObject("Excel.Application")

newFile = ThisWorkbook.Path & "\New_Book.xlsx"
Set newEx = objEX.Workbooks.Open(newFile, UpdateLinks:=0)

'(1)New_BookのSheet3を自分のブックにコピーする ()
Set tmpS = ThisWorkbook.Worksheets.Add
tmpS.Move , ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count)
newEx.Worksheets("Sheet3").Cells.Copy
tmpS.Paste
Set tmpS = Nothing

'(2)New_BookのSheet3をNew_Bookにコピーする ()
newEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

'(3)自分のブックのSheet3をNew_Bookにコピーする ()
Set tmpS = newEx.Worksheets.Add
tmpS.Move , newEx.Worksheets(newEx.Sheets.Count)
ThisWorkbook.Worksheets("Sheet3").Cells.Copy
tmpS.Paste
Set tmpS = Nothing

Application.DisplayAlerts = False
newEx.Save
newEx.Close
objEX.Quit
Application.DisplayAlerts = True
Set newEx = Nothing
Set objEX = Nothing

End Sub

投稿日時 - 2015-11-19 11:03:23

補足

こんにちは。

教えていただきました方法で、
タスクバーのちらつきもなく、
正しくコピーすることができました。

いろいろな検討していただきまして
ありがとうございました。


別インスタンスで開くと、(1)、(3)が
エラーになってしまうのは、おそらく、
Excelアプリケーションを1つ起動して、
この中で開いた2つのブック間では、
シートのコピーはできるのですが、
Excelアプリケーションを2つ起動して、
異なるExcelアプリケーションのブック間では、
シートのコピーができないことが
原因なのではないかと思われます。

また、今回、教えていただきました別インスタンスで
開く方法で、いろいろ調べていたのですが
自分のブックと別ブックのシートのコピーではなく、
別ブックと別ブックでのシートのコピーを行ってみると、
(1)(2)(3)すべて正しく行えることが分かりました。
(下記 Test2)

であるならば、最初の例の(2)(3)でも、
正しく行えるように思われるのですが、
なぜ駄目なのかこの原因が知りたいです。

------------------------------------------------
Sub test2()

 Dim objEX As Excel.Application
 Dim oldEx As Workbook
 Dim oldFile As String
 Dim newEx As Workbook
 Dim newFile As String

 Set objEX = CreateObject("Excel.Application")

 'objEX.Visible = True '動作確認用

 oldFile = ThisWorkbook.Path & "\Old_Book.xlsx"
 Set oldEx = objEX.Workbooks.Open(oldFile, UpdateLinks:=0)

 newFile = ThisWorkbook.Path & "\New_Book.xlsx"
 Set newEx = objEX.Workbooks.Open(newFile, UpdateLinks:=0)


 '(1)New_BookのSheet3をOld_Bookにコピーする <正常>
 newEx.Worksheets("Sheet3").Copy after:=oldEx.Worksheets(oldEx.Sheets.Count)

 '(2)New_BookのSheet3をNew_Bookにコピーする <正常>
 newEx.Worksheets("Sheet3").Copy after:=newEx.Worksheets(newEx.Sheets.Count)

 '(3)Old_BookのSheet3をNew_Bookにコピーする <正常>
 oldEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

 Application.DisplayAlerts = False
 oldEx.Save
 newEx.Save
 Application.DisplayAlerts = True
 oldEx.Close
 newEx.Close
 objEX.Quit
 Set oldEx = Nothing
 Set newEx = Nothing
 Set objEX = Nothing

End Sub
------------------------------------------------

投稿日時 - 2015-11-19 16:14:02

お礼

教えていただきました方法で検討していましたが、
シートコピーをセルのコピーで行うと、
セルに付けられているフィルタなどが
正しくコピーされないということが分かり
この方法ではちょっとまずいです。

あれから時間が経過していますので、
ここで一旦クローズして、
(2)(3)でなぜ駄目なのかも含めて、
改めてご質問したいと思います。

いろいろな検討していただきまして
ありがとうございました。

投稿日時 - 2015-12-01 10:34:00

ANo.1

こんにちは
Application.Windows("New_Book.xlsx").Visible = False
はやめて、
Application.ScreenUpdating = False
にしてはどうですか?
Sub test()
Dim newEx As Excel.Workbook
Dim newFile As String
Application.ScreenUpdating = False
newFile = ThisWorkbook.Path & "\New_Book.xlsx"
Set newEx = Workbooks.Open(newFile, UpdateLinks:=0)

' Application.Windows("New_Book.xlsx").Visible = False

'(1)New_BookのSheet3を自分のブックにコピーする (正常)
newEx.Worksheets("Sheet3").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

'(2)New_BookのSheet3をNew_Bookにコピーする (エラー)
newEx.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

'(3)自分のブックのSheet3をNew_Bookにコピーする (エラー)
ThisWorkbook.Worksheets("Sheet3").Copy after:=newEx.Sheets(newEx.Sheets.Count)

' Application.Windows("New_Book.xlsx").Visible = True

Application.DisplayAlerts = False
newEx.Save
newEx.Close
Application.DisplayAlerts = True
Set newEx = Nothing
Application.ScreenUpdating = True

End Sub

投稿日時 - 2015-11-19 08:25:15

補足

ushi2015さん。こんにちは。

回答ありがとうございます。

今回ご質問しました部分のコードは、
外部ファイルを操作している部分のみで、
全体のコードでは、最初と(最後)の部分に、
Application.ScreenUpdating = False(True)
は入れてあるのですが、
これだけでは、開いたExcelブックのアイコンが
タスクバーに表示されています。
サンプルでは1つのExcelファイルだけ開いていますが、
実際には、開くExcelファイルも複数で、順番に、
開いたり閉じたりを頻繁に繰り返す処理になっていて、
実行中(数十分間)の間ずっと、
タスクバーの部分がちらちら動いていて、
大変見苦しいので非表示にしたかったのです。

説明不足で申し訳ありません。

よろしくお願いします。

投稿日時 - 2015-11-19 10:00:21

あなたにオススメの質問