Excel.Applicationへのシートコピー

質問

Public xlApp As Excel.Application
Public xlBook As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
'非表示・画面更新無・アラート非表示
xlApp.Visible = False
xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Add
xlBook.SaveAs "test123.xls"

'※ここでエラー
Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlApp.Workbooks(xlBook).Sheets(3)

Set xlApp = Nothing
Set xlBook = Nothing

環境：WindowXP SP2,Excel2003 SP3
以上のようにオブジェクト変数のEXCELに対してのシートコピーを行いたいのですが、コンパイルを行うとエラーがなく、実行すると「コンパイルエラー　修正候補：区切り記号　または（」とエラーが発生します。色々書き方を試してみたのですが、どれもうまくいきませんでした。
宜しくお願いします。

回答No.3

venzou · ベストアンサー

＞xlApp.Visible = Falseの場合、この表示さえなく行うことができるので別アプリで行っておりました。
＞自アプリ上では、この処理は無理なのでしょうか。

No.1の回答の２の方法で可能ですが、1つ問題があります。

新規のアプリケーションは、ファイルからTEST.xlsを開くため、
未保存のデータは反映されません。

予めTEST.xlsを保存しておく必要があります。

Sub test2()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set xlApp = CreateObject("Excel.Application")
'非表示・画面更新無・アラート非表示
xlApp.Visible = False
xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Add

Workbooks("TEST.xls").Save '保存する

xlApp.Workbooks.Open Workbooks("TEST.xls").FullName '保存したファイルを開く

xlApp.Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)

xlBook.SaveAs "c:\temp\test123.xls"
xlApp.Quit

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

お礼

venzou　様
重ね重ねご回答ありがとうございます。
なるほど、コピー元とコピー先を新アプリのほうで扱うんですね。
ちなみにvenzou様のを参考に実行してみたのですが
作成されたtest123.xlsはシートが一個もないBookになってしまいました。

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlClone As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
'非表示・画面更新中止・アラート表示中止
xlApp.Visible = True
xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Add
Set xlClone = xlApp.Workbooks.Open("test.xls")

xlClone.Worksheets(TEST_1).Copy After:=xlBook.Sheets(3)
xlBook.SaveAs "test123.xls"
xlApp.Quit

この関数が走る前にtest.xlsは変更されないのでセーブはしてません。
またvenzou様が書いたopen文だとエラーになったのでこちらにしました。

補足

今確認したところ、一度test.xlsを閉じた上で、test123.xlsを開けば正常に開くことが分かりました。
test.xlsを開いたままtest123.xlsを起動するとアプリのみ起動してシートが何もない状態になるようです。これはtest123.xlsを閉じて再度開けば正常にはなるのですが・・・

回答No.2

venzou

＞Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlApp.Sheets(3)
＞Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)
＞としたのですが、今度は「Worksheetクラスのcopyメソッドに失敗しました」とエラーが出ました。

このxlAppとxlBookは新規のアプリケーションですよね？

新規のアプリケーションと、TEST.xlsを開いているアプリケーションとは、
別のアプリケーションになります。

アプリケーションが異なるので、コピーは出来ないと思います。

＞Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)
＿＿↑TEST.xlsを開いているアプリケーション＿＿＿＿＿＿↑新規のアプリケーション

-------------------------------------------
No.1の回答の１ですが、

Set xlBook = Workbooks.Add

この部分では、TEST.xlsを開いているアプリケーションと同じ
アプリケーション上にブックを作ります。

Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)

このコピーは同じアプリケーション上でのコピーになります。
こちらの環境（Excel2000）ではエラーは出ませんでした。

-------------------------------------------
＞コピーして作成されたBookを開かずにおいていたかったからです。

Application.ScreenUpdating = false
では、ダメなのかな？（＾＾；

Sub test()
Dim xlBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set xlBook = Workbooks.Add

Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)

xlBook.SaveAs "c:\temp\test123.xls"
xlBook.Close

Set xlBook = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

お礼

venzou 様
再度のご回答ありがとうございます。
別アプリの場合は、コピー自体できないのですね。そこからして間違ってました。

venzou様のサンプル通り実行したところ無事作成コピーと行うことができました。ただ一瞬ですがタスクバーに、作成コピーしているBookが表示されてしまいますよね？
xlApp.Visible = Falseの場合、この表示さえなく行うことができるので別アプリで行っておりました。
自アプリ上では、この処理は無理なのでしょうか。

回答No.1

venzou

このプログラムは何処で実行される物でしょうか？

１：TEST.xls上で実行されるものでしょうか？
２：それとも、全く別のワークブックやVBなど？

これにより回答が異なります。
--------------------------------------------------------
１の場合、新規のアプリケーションを立ち上げる必要はない。

Set xlBook = Workbooks.Add

Application.DisplayAlerts = False

Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)
xlBook.SaveAs "c:\temp\test123.xls"
xlBook.Close
Set xlBook = Nothing

Application.DisplayAlerts = True

--------------------------------------------------------
２の場合、TEST.xlsを開く必要がある

Set xlApp = CreateObject("Excel.Application")
'非表示・画面更新無・アラート非表示
xlApp.Visible = flse
xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Add

xlApp.Workbooks.Open "c:\temp\TEST.xls" 'ここでファイルを開く

xlApp.Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)

xlBook.SaveAs "c:\temp\test123.xls"
xlApp.Quit

こんな感じでどうでしょう？

お礼

venzou様

ご回答ありがとうございます。
実行する場所は１．のTEST.xls自身となっております。
なぜ１．のケースで新規のアプリケーションとしているかと言いますと
コピーして作成されたBookを開かずにおいていたかったからです。
ですからxlApp.visible = falseとしました。

また
Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlApp.Sheets(3)
Workbooks("TEST.xls").Sheets("TEST_1").Copy After:=xlBook.Sheets(3)
としたのですが、今度は「Worksheetクラスのcopyメソッドに失敗しました」とエラーが出ました。