• ベストアンサー
  • 暇なときにでも

VBAで Set wb = Sheets(1).Copyができないわけ?

つい先ほどの質問 4150169 は掲示したコードが抜けておりましたので無視して、こちらにご回答ください。 ほんと抜けた話です。すみません。 以下のマクロtest01はエラーになります。 Sub test01() Dim wb As Workbook Set wb = Sheets(1).Copy 'エラー「オブジェクトが必要です」 End Sub もちろん Dim wb As Workbook Sheets(1).Copy Set wb = ActiveWorkbook と修正すればエラーにならないことは存じていますが、ふと疑問がわきました。 Sheets(1).Copyの段階であたらしいWorkbookが誕生していますよね。 ならば、そのWorkbookはオブジェクトではないのでしょうか? Workbooks.Add で誕生したWorkbookは Set wb = Workbooks.Add と変数wbにSetできるのに Set wb = Sheets(1).Copy ができないのが不思議です。 Set wb = ActiveWorkbook としないでもSheets(1).CopyをwbにSetする書き方はないのでしょうか?

共感・応援の気持ちを伝えよう!

  • 回答数6
  • 閲覧数5196
  • ありがとう数6

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

  • ベストアンサー
  • 回答No.6
  • Wendy02
  • ベストアンサー率57% (3570/6232)

こんばんは。 少し、全体的に考えさせてもらいました。ご質問の最終的な終着点がどういうところにあるのかはあまりはっきりしませんし、私が書いたところで、この手の話は、あまり分かってもらえるという自信はありません。merlionXXさん、ご自身のVBAのコーディングの問題であって、個々の問題ではないからでもあります。 今回の質問は、merlionXX さん自身が、解答した中で考えたことでしょうし、まさか、サブルーチンや特殊なコードで解決をしようと思ったわけではないと思います。 実際のコードとして、  Set wb = Sheets(1).Copy というのは出来ないのはご存知なわけですが、なぜ「そうしたいと思った」か、ということがポイントではないでしょうか。出来ないのは、VBAの製作者の恣意的な設計だったのか、それとも、他の要因があったのかと思います。それを、私たちユーザーレベルでは分かりません。 あえて理由を言えば、もともと、Copy メソッドには、戻り値にオブジェクトではない、ということです。 実際に、 ret = Sheets(1).Copy で返るのは、True です。失敗すれば、実行時エラーが起こります。 質問は、前回のコード( http://oshiete1.goo.ne.jp/qa4138541.html ) から、思いついた事だとは思っていますが、Sheets(1).Copy の時点で、Application側の暗黙的な Workbookのインスタンスの生成を与えてしまっているわけで、そこで、ActiveWorkbook で、オブジェクトを取得することに、なにかしら不自然さを感じていたとしたら、その感覚は正しいのではないかと思います。私も同じように思います。 しかし、その方法で、ブックオブジェクトを取得する方法は、ActiveWorkbook をオブジェクトとして取る方法以外には、新たに Copy メソッド自体から手を加えるぐらいしか、実現する方法はないような気がします。そうすると、その親オブジェクトのSheet のClass 設定から、手を入れなくてはなりません。それは、遥か上級の話だろうし、VBAレベルで可能かどうかさえ分かりません。それに、そんなに難しいコードが、この種のもので必要だと思えません。また、一行、二行の問題で、特別なワザもなく、わざわざFunction プロシージャで取り出すというのは、理屈ではあっても、実務的にはまず考えられません。 VBAは、合理的に出来ているようでいて、はっきりとしないものがいくつもあり、「やって見なければ分からない」としかいいようがありません。本当に単純なコードでも、VBに出来て、VBAには出来ないものがあります。自分は、プロだと豪語している人に限って、こういうところでね凡ミスをするのが常です。VBAのコーディングは、よほど初歩的なこと以外は、戻り値を想定するというような「推測」で済まされるようなことはできません。 ただ、私は全体を通してみて、もし、もう一度、考える余裕があるなら、個々のメソッドやそれに関わるワザよりも、全体のプロセス自体に手を付けたほうがよいのではないか、と思います。 前のコードを、私自身で考えてみました。これは、定番のコードではあるので、私のアレンジあるものの、それ自体がオリジナルというわけではありません。シートモジュールにあるマクロコードをコピーしたいという特例の場合は、以下のコードを少し変更して可能です。これは一例ですが、こういうものは自力で編み出したわけではなく、VBAの入門時に、教わっているものです。6年経っても、そのとき覚えたものは忘れていません。 http://oshiete1.goo.ne.jp/qa4148672.html ---------------------------------------- Sub 保存3()   Dim orgShCnt As Integer '  Dim shName As String   Dim fn As Variant   'ブックのシートの規定数   orgShCnt = Application.SheetsInNewWorkbook   Application.SheetsInNewWorkbook = 1   With Worksheets("Sheet1") '    shName = .Name     .Cells.Copy   End With   With Workbooks.Add  '※     .ActiveSheet.Paste     Range("A1").Select     Application.CutCopyMode = False     '規定数を戻す     Application.SheetsInNewWorkbook = orgShCnt     'shName → は、"" になっている     fn = Application.GetSaveAsFilename("", "Excelブック(*.xls),*.xls", 1, "保存")     If VarType(fn) = vbBoolean Then       .Close False       MsgBox "キャンセルしました。", 48       Exit Sub     End If     .SaveAs fn     .Close False   End With   MsgBox "終了しました。", 64 End Sub ---------------------------------------- ※ Workbooks.Add というメソッドで、明示的なブックを生成していればこそ、そのオブジェクトは確実に取得できるわけです。Application の NewBook イベントで、ブックオブジェクトは取得は出来ますが、それは、ひとつのプロセスの中にはありません。 はたして、本当に、Sheets(1).Copy でオブジェクトを捕らえることが必要なのか、と思います。私は、今まで、VBAのコードを2~3千件書いていますが、ほとんど、その方法は、記録マクロを写した程度にしか、そのような方法は出てこないように思います。 失礼な言い方があったら、お許しください。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

Wendy02さま、ご丁寧な解説を有難うございます。 また、わたしのこれまでの回答までご覧になっていただき、その上での適切なご回答、とても勉強になりました。 これからもご指導くださいますようお願い申し上げます。

関連するQ&A

  • VBA 実行時エラーで、"プロパティまたはメソッド

    ・Sheet1(コード) Private Sub CommandButton1_Click() Call aaa End Sub ・Module1(コード) Sub aaa() Dim wb As Workbook Dim ws As Worksheet Workbooks.Open ("c:\test.xls") Set wb = Workbooks("test.xls") Set ws = wb.Worksheets("Sheet1") wb.ws.Range("A2").Value = "CCC" End Sub wb.ws.Range("A2").Value = "CCC"の部分で 以下の実行エラーが出ます。 ------------------------------------------------------------------------ 実行時エラー'438': オブジェクトは、このプロパティまたはメソッドをサポートしていません。 ------------------------------------------------------------------------ Set wb = Workbooks("test.xls") Set ws = wb.Worksheets("Sheet1") の部分で特にエラーも出ないので、オブジェクトの取得は成功していると 思うのですが、WorkSheetオブジェクトのwsからRangeメソッドを呼ぶことが できません。 動かない原因と対策を教えてください!!

  • エクセルvba

    エクセルvbaなのですが Sub test() Dim xlApp As Object Dim xlBook As Object Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(ActiveWorkbook.FullName) 'コード・・・ Set xlApp = Nothing Set xlBook = Nothing End Sub これだと Set xlBook = xlApp.Workbooks.Open(ActiveWorkbook.FullName) の部分で、エラーになります。 実行時エラー1004です。 自身ファイルをオブジェクトに格納して操作したいのですがどうすればいいでしょうか?

  • エクセルVBAで作成した別ブックにVBAを記述したい

    VBAで別ファイルの作成は下記で出来ているのですが、出来上がったファイルにVBAを記述する方法がわかりません。 具体的には一番下のSub TEST()を新しいブックの標準モジュールに記述したいのと、sheet1に Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "ChangeTEST" End Sub を入れたいです。 また Private Sub Workbook_Open() MsgBox "OpenTEST" End Sub も入れたいのです。 どうぞご教示ください。 Sub 複製() Dim wb As Workbook, sc As Integer sc = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Set wb = Workbooks.Add Application.SheetsInNewWorkbook = sc wb.Sheets("Sheet1").Select ThisWorkbook.Sheets("Sheet1").Cells.Copy wb.Sheets("Sheet1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Buttons.Add(123, 195, 68.25, 15).Select Selection.OnAction = "TEST" Selection.Characters.Text = "TEST" ActiveWorkbook.Close ThisWorkbook.Activate Sheets("Sheet1").Select End Sub Sub TEST() MsgBox "TEST!!" End Sub よろしくお願いします。

その他の回答 (5)

  • 回答No.5
  • rexfan
  • ベストアンサー率42% (3/7)

No4です。 訂正です。 Set wb = Workbooks.Add(ActiveWorkbook.Sheets(1).Copy) という形でTemplateに指定します。 ただし「Sheet1」というブックが何故か余分にできてしまい 理由がわからないので完全な回答になっていません。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます。 今、自宅の2003で試しましたら#3のコードも#4のコードもエラーになりませんね でも両方とも余分なBookができてしまうのは困りものです。 やはり、素直にそういうものなんだと覚えることにします。

  • 回答No.4
  • rexfan
  • ベストアンサー率42% (3/7)

こんにちは。 こんなコードは絶対エラーになると思って 試したことがありませんでしたが投稿者さんの 疑問ももっともですね。 コピー先の指定が無いとSheets(1).Copy の前に 「Workbooks.Add」が動作してるのでしょう。 そしてブックの中身を指定したシートのみに入れ替えています。 次のように明示的に指定してみると動作しました。 Set wb = Workbooks.Add(Sheets(1).Copy) ただしブックの名前が「Sheet1」になりますが・・・。

共感・感謝の気持ちを伝えよう!

  • 回答No.3
  • keirika
  • ベストアンサー率42% (279/658)

多少無理のある説明かもしれませんが・・・・ 同じプロシージャでもSubとFunctionがあり、Subは値を返しませんが Function(関数)は値を返します。 つまり、SubをFunctionに変更しても同じ結果が得られますがSubとFunctionはやはり別物です。 結果としてオブジェクトが生成されるとしても、値を返すものと返さな いものがあり、返されないものを格納は出来ないのでエラーになります。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます。 そういうものだと覚えておくしかないようですね。

  • 回答No.2
  • imogasi
  • ベストアンサー率27% (4562/16328)

Sub test01() Dim wb As Workbook Set wb = Sheets(1).Copy 'エラー「オブジェクトが必要です End Sub は何をしたいのか。 ワークブックを増やして、増えたブックの第1シートをコピーしたいのか。 シートを増やせば、とかコピーすれば、ブックが増えるという発想がわからない。 それにしても Dim Newbook As Workbook Set Newbook = Workbooks.Add Newbook.SaveAs FileName:="新規ブック" または Workbooks.Add.SaveAs Filename:="新規ブック.xls" がないのはどうしてか。 >Sheets(1).Copyの段階であたらしいWorkbookが誕生していますよね >Set wb = Sheets(1).Copy >Set wb = Sheets(1).Copyができないのが不思議です ができないのが不思議です。 コピーしてもオブジェクトが新しく作られるとか、既存オブジェクトが捉えられないのは、Copyメソッドはそういうものじゃ無いのだから当たり前。 なんか独りよがり的な点が見えて、聞いていることが理解しにくい。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

いえ、別にしたいことがあるわけじゃないんです。 ただ、目の前に新しいBookが出来たのに「オブジェクトが必要です」とエクセルから叱られると釈然としなかったもので・・・・・。 Copyメソッドはそういうものじゃ無いと言われてしまえばそれまでの話なんですが。

  • 回答No.1
  • bonaron
  • ベストアンサー率64% (482/745)

> Workbooks.Add で誕生したWorkbookは > Set wb = Workbooks.Add > と変数wbにSetできるのに ------------------------------------------------------------ ヘルプより Workbooks オブジェクトの Add メソッド オブジェクトを返すメソッドです。新しいブックを作成します。新しいブックが作業中のブックになります。ブック (Workbook オブジェクト) を返します。 ------------------------------------------------------------ > Set wb = Sheets(1).Copy > ができないのが不思議です。 ------------------------------------------------------------ Chart、Charts、Sheets、Worksheet、および Worksheets オブジェクトの Copy メソッド シートをブック内の他の場所にコピーします。 ------------------------------------------------------------ Copy メソッド はオブジェクトを返さないので 右辺には、wb に代入すべきオブジェクトがありません。 で、「オブジェクトが必要です」 と言われます。 仮に Copy メソッド がオブジェクトを返したとしても おそらく Workbook ではなく、Worksheet でしょうね。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

さっそくありがとうございます。 > Copy メソッド > シートをブック内の他の場所にコピーします。 Sheets(1).Copy Before:=Sheets(1) のように書けばその通りですが、例えばBook1で、Sheets(1).Copy だけで実行すると、Book2が出来てしまいます。このBook2はWorkbookでオブジェクトですよね?

質問者からの補足

いま試しましたが、 Sub test() Sheets(1).Copy MsgBox Workbooks.Count End Sub これを実行するたびにWorkbookの数は一つずつ増えていきます。

関連するQ&A

  • VBAでエラーが出ます

    セルの値を変数に格納して、それをブック保存時に使いたいのですが上手くいきません。 Sub サンプルブック保存() Dim myfile As String myfile = workbooks("サンプル").Worksheets("sheet1").Cells(2, 9) workbooks.Add ActiveWorkbook.SaveAs Filename:="\\C:\フォルダ\" & myFile & ".xls" End Sub エラーメッセージは、 実行時エラー'1004': SaveAs'メソッドは失敗しました'_Workbook'オブジェクト と出ます。 非常に困ってます。 教えてくだされば幸いです。

  • VBAに関して

    VBA初心者のものです。あるプログラムを作成するために後で簡単に呼び出すことができるよう、始めにいくつかのブックのパス名をべつのものに置き換えたいのですが、例えば Dim i As Integer, v As Integer i = Application.InputBox("コピーしたいファイル数を記入してください", Type:=1) For v = 1 To i Dim wb(v) As Workbook OpenFilename = Application.GetOpenFilename("Microsoft Excelブック,*.xls") Workbooks.Open fileName:=OpenFilename Set wb(v) = ActiveWorkbook wb(v).Close Next v こんな風に書くとwb()が配列なのでエラーになってしまいます。分かりにくい説明ですが、何か良い方法はないでしょうか?

  • エクセルVBA 3/26夜の補足です

    おはようございます お忙しいに申し訳ありません 春から私用で使うための自作ソフトを作成したおります ご迷惑おかけします 指摘のサンプルです わたしなりに作成したものです Sub sheetcopy1() ThisWorkbook.Sheets("sheet1").Select Range("B2:K100").Select Selection.Copy Dim wb As Workbook Set wb = Workbooks.Add ThisWorkbook.Sheets("sheet1").Copy before:= _ wb.Worksheets(1) Set wb = Nothing End Sub これですと、新しいBOOKが起動しシートすべての項目が張り付きます シートの一部を(B2からK100)を移したいと思っています ワンクリックとはフォームのコマンドボタンで移動できればと思っています よろしければお知恵をおかりしたくおもいます よろしくお願いします

  • VBAについて質問です。

    VBAについて質問です。 現在、ExcelにてWorkbookを自動で作成するモジュールを作成しています。 モジュールを記載しているWorkbookを [wbSorce] 自動で作成されるWorkbookを [wbNew] とします。 [wbSorce] でモジュールを実行すると、 [wbNew] を新規に作成し、 データを入力して保存するのですが、保存する際に [wbNew] のイベントハンドラ [Workbook_Open] に 保存された [wbNew] を開いた時の挙動を記載するには どうすればよいでしょうか? サンプルは下記になります。 ****[wbSorce]のモジュール**** Sub wbNew_Sakusei()   Dim wb As Workbook   Dim wb2 As Workbook   Dim i As Integer   Set wb = ThisWorkbook   Set wb2 = Workbooks.Add   For i = 1 To 5     wb2.Sheets(1).Cells(1, i) = wb.Sheets(1).Cells(1, i)   Next   wb2.SaveAs Filename:="wbNew" End Sub ****[wbNew]に記述したいモジュール**** Private Sub Workbook_Open()   ActiveWindow.ScrollRow = 1   MsgBox "Workbook_Openイベントが発生しました。" End Sub

  • Word VBA

    ※行頭の下線はプログラムに関係ありません、インデントと解釈ください。 Excel VBAで既に開いているワークブックの一覧取得は Sub test() __Dim wb As Workbook __For Each wb In Workbooks ____Debug.Print wb.Name __Next wb End Sub で成功したのですが、 Word VBAで Sub test() __Dim wb As Object __For Each wb In CreateObject("Excel.Application").Workbooks ____Debug.Print wb.Name __Next wb End Sub では何も表示されません。 問題解決のための知識を与えてくださる方、宜しくお願いします。

  • SETを使ったほうがよい?

    accwessからエクセルファイルを開きたいのですが、 App.Workbooks.Open と、 Set xlBook = xlApp.Workbooks.Open とどちらを使った方がいいのでしょうか? ////////////////////////////////////////////////////////// Private Sub ファイル1_Click() Dim App As Object Dim MyFileName As String MyFileName = "D:\My Documents\test.xls" Set App = CreateObject("Excel.Application") App.Workbooks.Open FileName:=MyFileName App.Visible = True End Sub でも Private Sub ファイル2_Click() Dim xlApp As Object Dim xlBook As Object Dim FileName As String Const FolderName = "D:\My Documents\test.xls" Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(FolderName & FileName) xlApp.Visible = True Set xlApp = Nothing Set xlBook = Nothing End Sub ////////////////////////////////////////////////////////// でも開けました。 多分、SETを使うかどうかの違いだと思うのですが VBAでコードを作る際、どちらのコードを使った方がいいか教えてください。

  • VBAであるBOOKの「sample」というシートを別BOOKに

    VBAであるBOOKの「sample」というシートを別BOOKに 別Bookにコピーしたいのですが、 ネットを参考にして下記のようにするとエラーになります。 実行時エラー'9' 「インデックスが有効範囲にありません」 と表示されます。 どこが間違っているでしょうか? どなたか教えてください。 ---------------------------------------- Dim NewBook As Workbook Dim fName As String Set NewBook = Workbooks.Add fName = ThisWorkbook.Path & "cp.xls" NewBook.SaveAs Filename:=fName Workbooks("moto.xls").Sheets("ピッキング").Cells.Copy Workbooks(fName).Sheets("Sheet1").Paste Destination:=Cells(1, 1) '<---ここでエラー Workbooks(fName).Save ----------------------------------------

  • 「 VBA の 宣言 」 がない場合の問題点は ?

    下記例で、 「 宣言 」 なしでも、現在のところ、問題は発生してませんが、 今後、「 宣言 」 がなかった場合の 「 問題点の例 」 を教えて下さいませ。 ------------------------------- Sub ブックA*の全シートをコピー() Dim Wb As Workbook '宣言 For Each Wb In Workbooks If Wb.Name Like "ブックA*.xls" Then With Workbooks("ブックB.xls") Wb.Worksheets _ .Copy after:=.Sheets(.Sheets.Count) End With End If Next Worksheets(Worksheets.Count).Activate MsgBox ActiveSheet.Index Worksheets("Sheet1").Select End Sub

  • VBAでブック名の拡張子を除去してシートにコピー

    VBA初心者でコード作成で困っております。 下記の通りコードを組みましたが、シート名をブック名に変更して 保存したいのですが、このコードですと拡張子までついてしまいます。 拡張子を除去するためにはどうすればよいでしょうか? アドバイス宜しくお願い致します。 Sub test() 'シート名の変更 Dim MyPath As String Dim MyFile As String Dim Wb As Workbook MyPath = "C:\TEST\" MyFile = Dir(MyPath & "*.xlsx") Do While MyFile <> "" Set Wb = Workbooks.Open(MyPath & MyFile) ActiveSheet.Name = ActiveWorkbook.Name Application.DisplayAlerts = False Wb.Save Application.DisplayAlerts = True Wb.Close (False) MyFile = Dir() Loop End Sub

  • エクセルVBAでのエラー

    おはようございます。 昨日ここでいろいろ教えていただき、300のエクセルファイルから特定の範囲のデータ抽出方法を書いてみたのですが、セルが多すぎて実行できません、というエラーがでてしまいます。 これはどのように解消すればよろしいのでしょうか? Sub Test() Dim FPath1 As String, FPath2 As String Dim FName As String, myBook As String Const startROW As Long = 14, lastROW As Long = 20 Const startCOL As Long = 8, lastCOL As Long = 10 Const shtNAME As String = "sheet1" Application.ScreenUpdating = False FPath1 = "D:\MR5567\" FPath2 = "D:\New Microsoft Excel Worksheet\" Workbooks.Add myBook = ActiveWorkbook.Name FName = Dir$(FPath1 & "*.xls") Do While FName <> "" Workbooks.Open Filename:=FPath1 & FName ActiveWorkbook.Sheets.Select Sheets(1).Activate Sheets.Copy After:=Workbooks(myBook).Sheets(Sheets.Count) Workbooks(FName).Activate Application.DisplayAlerts = False ActiveWorkbook.Close FName = Dir$ Loop ActiveWorkbook.SaveAs Filename:=FPath2 & "Renketsu.xls", FileFormat:=xlNormal ActiveWorkbook.Close Application.ScreenUpdating = True End Sub