Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
6517 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【44727】新規ブックの処理について tomo 06/11/30(木) 11:19 質問[未読]
【44728】Re:新規ブックの処理について Jaka 06/11/30(木) 13:41 発言[未読]
【44751】Re:新規ブックの処理について tomo 06/11/30(木) 17:12 お礼[未読]
【44739】Re:新規ブックの処理について Kein 06/11/30(木) 15:45 回答[未読]
【44757】Re:新規ブックの処理について tomo 06/11/30(木) 18:38 質問[未読]
【44794】Re:新規ブックの処理について Kein 06/12/1(金) 15:38 発言[未読]
【44801】Re:新規ブックの処理について tomo 06/12/1(金) 16:58 お礼[未読]
【44727】新規ブックの処理について
質問  tomo  - 06/11/30(木) 11:19 -
引用なし
パスワード
   マスターとなるブック（"base.xls"とします）に数値データがあり，あるブック（"output.xls"とします）が存在する場合は，このブックを開き，ない場合は，新規ブックを開き，"aaa.xls"又は新規ブックに"base.xls"の数値データをコピーするマクロを作成しています。
以下のようにしました。

With Application.FileSearch
　　　　　　　　.Filename = "output.xls"
　 If .Execute > 0 Then
　　　Workbooks.Open Filename:="output.xls"
　 Else
　　　Workbooks.Add
　　　ActiveWorkbook.SaveAs Filename:="output.xls"
　 End If
End With

そして，次のようにして，コピーを行わせてます。

Windows("base.xls").Activate
Sheets("sheet1").Select
range("A1:A50").select
Selection.Copy
Windows("output.xls").Activate
Sheets("sheet1").Select
range("A1").select
ActiveSheet.Paste
（Sheet2についても同様にしてコピー作業を行い，
　以降のSheetも繰り返し行わせています。）

この後，コード化は省略しますが（Call文等があったり長くなるので･･･），貼付した"output.xls"のデータを利用して，さらに別ブック（例えば"result.xls"）に必要なデータだけを貼り付けて，別ブックで最終計算を行わせます。
最後にKill "output.xls"として，"output.xls"自体消去させ，マクロが終了します。
（直接，"base.xls"から別Sheetに貼り付ければ等と指摘を受けそうですが，今回は無視してください）

今回は新規ブックを"output.xls"と名前を付けて保存させましたが，仮に名前の変更を行わない場合（ActiveWorkbook.SaveAs Filename:="output.xls"を省略），新規ブック名は"Book1.xls"となると思います。
（自動マクロで新規ブックを開いてコピーをさせる作業を記録させて確認した結果，　勝手に"Book1.xls"となっていました。）
ただし，この場合，すでに"Book1.xls"が開いていた場合，"Book2.xls"となってしまい，Excelの使用条件によって，一定の名前にならないことが考えられます。

ここで質問なのですが，上記から，新規ブックに特定の名前を付けて保存しておかなければ，いかなる条件においても正常にマクロが作動しないのではと思い，上記のようにコード化したのですが，新規ブック名の変更作業を表すコードは必要なものなのでしょうか？

マクロとしては正常に流れるので問題はないのですが，最後に"Book1.xls"自体も消去させるので，ファイル名の変更作業がなければ，よりベストと考えています。
簡潔なコードがあれば是非ご教示願います。

よろしくお願いします。
【44728】Re:新規ブックの処理について
発言  Jaka  - 06/11/30(木) 13:41 -
引用なし
パスワード
   こんな感じにしたらどうでしょうか？

Dim Wb As Workbook
On Error Resume Next
Set Wb = Workbooks.Open("output.xls")
On Error GoTo 0
If Wb Is Nothing Then
　 Set Wb = Workbooks.Add
　 Wb.SaveAs Filename:="output.xls"
End If
Wb.Sheets(1).Range("a1").Value = 123

最後にセットしたものを解放
Set Wb = Nothing
【44739】Re:新規ブックの処理について
回答  Kein  - 06/11/30(木) 15:45 -
引用なし
パスワード
   >最後にKill "output.xls"として，"output.xls"自体消去させ，マクロが終了
ということは、最初は "output.xls" が存在するかも知れないが、２回目の実行以後
は必ず新規ブックを作成することになりますよね ? ならば初めから新規ブックを
作業用に作れば、無駄に特定のブックを探す必要はなくなると思います。
例えば・・・

Sub MyData_Copy()
　 Dim DefS As Integer, SCnt As Integer, i As Integer
　 Dim MyB As Workbook

　 SCnt = ThisWorkbook.Worksheets.Count
　 With Application
　　　DefS = .SheetsInNewWorkbook
　　　.SheetsInNewWorkbook = SCnt
　　　.ScreenUpdating = False
　 End With
　 Set MyB = Workbooks.Add
　 For i = 1 To SCnt
　　　MyB.Worksheets(i).Range("A1:A50").Value = _
　　　ThisWorkbook.Worksheets(i).Range("A1:A50").Value
　 Next i

　 'ここへ 「データを利用して，さらに別ブック（例えば"result.xls"）に
　 '必要なデータだけを貼り付けて，別ブックで最終計算」するコードを入れる。

　 MyB.Close False: Set MyB = Nothing
　 With Application
　　　.SheetsInNewWorkbook = DefS
　　　.ScreenUpdating = True
　 End With
End Sub

というコードを、マスターとなるブック("base.xls")に入れて実行すれば
良いと思います。新規作成したブックは、保存せずに閉じると当然、
"初めから存在しなかった"ことになるので、探したり削除したりする必要が
なくなります。
　
【44751】Re:新規ブックの処理について
お礼  tomo  - 06/11/30(木) 17:12 -
引用なし
パスワード
   ▼Jaka さん：
>On Error Resume Next
>Set Wb = Workbooks.Open("output.xls")
>On Error GoTo 0

On Error ステートメントを今まで使用したことがなく，大変勉強になりました。
（存在自体も知りませんでした･･･）
ありがとうございました。
【44757】Re:新規ブックの処理について
質問  tomo  - 06/11/30(木) 18:38 -
引用なし
パスワード
   ▼Kein さん：
回答ありがとうございます。
Set ステートメントを用いることで，名前付き仮保管を行うわけですね。
コピー作業のコードも自動記録にて作成したものをそのまま流用していたため，何かまわりくどい書き方だなと思っていたため，簡潔な表し方を示していただき，大変勉強になりました。

ついでで申し訳ないのですが，もう一つ質問させてください。
>　　　MyB.Worksheets(i).Range("A1:A50").Value = _
>　　　ThisWorkbook.Worksheets(i).Range("A1:A50").Value
と簡潔に表していただきましたが，これがThisWorkbookではない場合について教えて下さい。
実際にはマスターブックはマクロ専用ブックであり，コピー元となるデータ自体，このブックには書かれていません。作成中のマクロの大まかな流れを下記に示します。

Sub MainPgm()
　　　：
　Call test()　'下記参照
　　　：
'この区間に解答いただいたコードを挿入することになります。
　MyB.Worksheets(i).Range("A1:A50").Value ･･･
　　　：
　Call test3() 'ここで最終計算を実施。オブジェクトはMainPgm()と異なる。
　　　：
End Sub
Sub test() 'オブジェクトはMainPgmとは異なる
　Call test2() 'これによって"sample.out"が作成されます。
　Open "sample.out" For Output Shared As #1
　Workbooks.OpenText Filename:="sample.txt", _
　　　：　　　　　　'条件付でファイルを開きます
End Sub

OpenTextによって開いたブックがデータ元ブックとなるのですが，このデータをMyBに貼り付けていくにはどのようにすればいいのでしょうか？
賢くないやり方としては，
>　　　MyB.Worksheets(i).Range("A1:A50").Value = _
>　　　ThisWorkbook.Worksheets(i).Range("A1:A50").Value
前の行に
　 Windows("sample.out").Activate
と記入しておけば，
　 MyB.Worksheets(i).Range("A1:A50").Value = _
　 ActiveWorkbook.Worksheets(i).Range("A1:A50").Value
と置換えることが可能なのでしょうが･･･
【44794】Re:新規ブックの処理について
発言  Kein  - 06/12/1(金) 15:38 -
引用なし
パスワード
   ども。返事が遅くなってすいません。
>Set ステートメントを用いることで，名前付き仮保管
んー・・分からないでもないのですが、その解釈は少し正確さに欠けます。
"仮"というなら、ブックやシートを新規作成したとき Excelが勝手に付けている
名前。それだけが正しく"仮の名"と言えます。保存は仮にもしていません。
で、Setステートメントを用いて何をするかと言うと「生成したオブジェクトの
インスタンスを変数に格納」しているのです。つまり、あとあと扱いやすいように
変数に入れているだけです。
もし変数に入れないコードにするなら、Workbooks.Add の直後から「一切、他の
ブックをアクティブにせず」ActiveWorkbook を、その新規ブックを参照する
ものとして使います。そーすると ActiveWorkbook.Close した時点で自動的に
ThisWorkbook がアクティブになるはずです。実際にそーいうコードもよく組み
ますが、参考書の例文などは圧倒的に変数へ格納するやり方が多いので、私も
そうしてみたというわけです。

それはさておき、肝心のマクロ全体(横道にそれますが、Sub〜End Sub までを
本来"プロシージャ"と呼び、それらをひとまとめにして意味のある一連の処理
をさせるものを"マクロ"と呼びます。それがMicrosoft社の定義ですが、今は
単一のプロシージャで処理が完結する場合、それもマクロと呼んでます。)
は、プロシージャの呼び出しが入れ子になっていて、たいへん見通しが悪い、
と感じます。できればMainPgmからの呼び出しは、最後の計算処理部分だけに
すると良いと思います。で、コピー元ブックは実際には
>OpenTextによって開いたブックがデータ元
なら、当然シートは１枚のみですよね ? テキストファイルをエクセルで開いても
複数シートになることは絶対ありません。だから新規ブックにコピーするにしても
そこから他のブックへデータをコピーするにしても「シートをループする」こと
自体があり得ない処理となります。なので
>Workbooks.OpenText Filename:="sample.txt",
としたら
MyB.Worksheets(1).Range("A1:A50").Value =
ActiveWorkbook.Worksheets(1).Range("A1:A50").Value
と、一回だけコピーして終わりになるはずです。そしてコピー元がシート１枚なら
新規ブックもシート１枚でいいので、SheetsInNewWorkbook でシート数を取得
したり設定するコードや変数は不要になり、

Set MyB = Workbooks.Add(xlWBATWorksheet)

とするだけで"ワークシート１枚のみを含む"ブックが作成されますから、
Worksheets(1) にコピーするコードを続けます。
いずれにせよ、どのような理由があるか知りませんが、やたらといろんなブック
を開いたり作ったりするのは、混乱の元でもあるしメモリーの消費量も多くなって
不安定になるので、そのへんの整理から考えた方がいいと思いますけどね・・。
【44801】Re:新規ブックの処理について
お礼  tomo  - 06/12/1(金) 16:58 -
引用なし
パスワード
   ▼Kein さん：
ご親切な解答を頂き，誠にありがとうございます。
>で、Setステートメントを用いて何をするかと言うと「生成したオブジェクトの
>インスタンスを変数に格納」しているのです。つまり、あとあと扱いやすいように
>変数に入れているだけです。
「変数という名の架空の箱に仮置きする」という理解から，名前付き仮保管と書きましたが，あやふやな表現のままにしておくと，後々，間違った解釈に繋がる可能性もあるため，「格納」と言う表現をしっかり覚えます。

>すると良いと思います。で、コピー元ブックは実際には
>>OpenTextによって開いたブックがデータ元
>なら、当然シートは１枚のみですよね ? テキストファイルをエクセルで開いても
>複数シートになることは絶対ありません。だから新規ブックにコピーするにしても
正確な情報でなくて申し訳ありませんでした。実のところ，
>>Sub MainPgm()
>>　　　：
>>　Call test()　'下記参照
　　Ca11 test1a()
　　Ca11 test1b()
　　Ca11 test1c()
>>　　　：
>>'この区間に解答いただいたコードを挿入することになります。
>>　MyB.Worksheets(i).Range("A1:A50").Value ･･･
>>　　　：
>>　Call test3() 'ここで最終計算を実施。オブジェクトはMainPgm()と異なる。
>>　　　：
>>End Sub
>>Sub test() 'オブジェクトはMainPgmとは異なる
>>　Call test2() 'これによって"sample.out"が作成されます。
>>　Open "sample.out" For Output Shared As #1
>>　Workbooks.OpenText Filename:="sample.txt", _
>>　　　：　　　　　　'条件付でファイルを開きます
>>End Sub
とCallが4回続いています。このため，ループ処理が続くことになるのです。

確かに，ご指摘いただいた通り，やたらといろんなブックを開かせようとしています。
"base.xls"から2段階に分けてブック作成するのではなく，直接作成する等の努力が必要なことが分かりました。
もっと勉強して何とか整理してみます。
ありがとうございました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
6517 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free