Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
12571 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【9849】BOOK間で値のコピーを繰り返しって？ みい 03/12/18(木) 20:02 質問
【9852】Re:BOOK間で値のコピーを繰り返しって？ INA 03/12/19(金) 9:25 回答
【9854】Re:BOOK間で値のコピーを繰り返しって？ みい 03/12/19(金) 11:44 お礼
【9857】Re:BOOK間で値のコピーを繰り返しって？ INA 03/12/19(金) 13:25 回答
【9860】Re:BOOK間で値のコピーを繰り返しって？ みい 03/12/19(金) 14:25 質問
【9865】Re:BOOK間で値のコピーを繰り返しって？ Jaka 03/12/19(金) 15:26 回答
【9867】Re:BOOK間で値のコピーを繰り返しって？ INA 03/12/19(金) 15:31 回答
【9869】Re:BOOK間で値のコピーを繰り返しって？ みい 03/12/19(金) 15:38 お礼
【9849】BOOK間で値のコピーを繰り返しって？
質問  みい  - 03/12/18(木) 20:02 -
引用なし
パスワード
   こんにちは、はじめまして。
VBA初心者なのですがご指導願います。
BOOK1.xlsでopentextを使ってho.lst（←テキストデータ）を開いたあとに、
ho.lstのB1,B2・・・とB列の値を元々開いているBOOK1.xlsのAB18,AB19・・・にコピーしようとマクロの記録で作ってみたのですが、ここからの改良がわかりません。
改良したいポイントは
1：B列の値が空白になったらコピーはおわり。
(データによってはB列の数は異なるため。）
2：opentextで開くデータのファイル名は*.lst
（今回はho.lstというBOOKになりましたが、いつも*部分ファイル名は異なるものを扱いたい。）
3：できれば開いたtextデータのwindowは表示されない。
（常にBOOK1.xlsを表示していたい。・・・マクロの記録ではwindowがころころ切り替わってしまいます。）
4：コピーが終わったらコピー元のBOOKは終了したい。
とりあえずは上記4項目です。

今までのレスや他HPなど参考にしながら作成奮闘中です（汗）
皆さんのご指導が頂ければと思います。
どうぞ宜しくお願い致します。
【9852】Re:BOOK間で値のコピーを繰り返しって？
回答  INA  - 03/12/19(金) 9:25 -
引用なし
パスワード
   >1：B列の値が空白になったらコピーはおわり。
>(データによってはB列の数は異なるため。）
Dim i As long

If Cells(i,2).Value = "" Then
　Msgbox "Ｂ列は" & i & " 行目で空白になりました。"
　Exit Sub
Else
　'処理
End if


>2：opentextで開くデータのファイル名は*.lst
>（今回はho.lstというBOOKになりましたが、
>いつも*部分ファイル名は異なるものを扱いたい。）
その都度、ファイルをユーザーが指定するということであれば、
GetOpenFilename メソッド
が使えると思います。

>3：できれば開いたtextデータのwindowは表示されない。
>（常にBOOK1.xlsを表示していたい。・・・
>マクロの記録ではwindowがころころ切り替わってしまいます。）
ScreenUpdating プロパティで画面の表示を更新させなくするか、
もしくは、Visible プロパティでブックのウィンドウや、
EXCEL自体を非表示にしては如何でしょうか？

>4：コピーが終わったらコピー元のBOOKは終了したい。
>とりあえずは上記4項目です。
Close メソッドの使用例
次の使用例は、Book1.xls のブックを閉じます。内容の変更は保存しません。
Workbooks("BOOK1.XLS").Close SaveChanges:=False
でしょうか？
【9854】Re:BOOK間で値のコピーを繰り返しって？
お礼  みい  - 03/12/19(金) 11:44 -
引用なし
パスワード
   INAさん、ご回答ありがとうございます。
勉強不足でわからない事だらけなのですが、
さらに詳しく教えてください。

>Dim i As long
>
>If Cells(i,2).Value = "" Then
>　Msgbox "Ｂ列は" & i & " 行目で空白になりました。"
>　Exit Sub
>Else
>　'処理
>End if
自分はDO LOOPを使って繰り返しするつもりでしたが、ちょっと試してみます。
DO LOOPを使って"B列が空白になるまでBOOK1.xlsのA列に順番にコピーしなさい。”という感じの書式にしたいのですが、この場合はどのような書式になるのでしょうか？

>>2：opentextで開くデータのファイル名は*.lst
>>（今回はho.lstというBOOKになりましたが、
>>いつも*部分ファイル名は異なるものを扱いたい。）
>その都度、ファイルをユーザーが指定するということであれば、
>GetOpenFilename メソッド
>が使えると思います。
とりあえずは下記書式にしてみました。
　　Dim FileN As String
　　FileN = Application.GetOpenFilename("リストファイル(*.lst),*.lst")
If FileN = "False" Then
　 End
End If
　　Workbooks.OpenText Filename:=FileN, StartRow:=34, DataType _
　　　　:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:= True, Semicolon:=False, Comma:=False, Space:=True, Other:=True, _
　　　　OtherChar:="|", FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 9), Array(5, 9), Array(6, 9), Array(7, 9), Array(8, 9))

現状はこれでリストのファイルは新しいBOOKになって開かれるのですが、
このあと”開いたBOOKのB列をもともと開いているBOOKのA列に順序良くコピーする”
という事を行ないたいのですが、どの様にすれば良いのでしょうか？


順番的には
”テキストファイルを開く”（質問項目2）
”開いたBOOK2のB列を空白になるまで、もともと開いているBOOK1のA列に順番にコピー。”（質問項目1)
”コピー元であるBOOK2は閉じる。”(質問項目4)
としたいです。
質問の順番が悪く、判りにくい形になってしまい申し訳ありませんでした。
ご指導の程、宜しくお願い致します。
【9857】Re:BOOK間で値のコピーを繰り返しって？
回答  INA  - 03/12/19(金) 13:25 -
引用なし
パスワード
   >DO LOOPを使って"B列が空白になるまでBOOK1.xlsのA列に順番にコピーしなさい。"
>という感じの書式にしたいのですが、この場合はどのような書式になるのでしょうか？
DO 〜 LOOP だと行の指定に i をカウントさせる必要がありますので、
数値として、行をループ処理できるFor 〜 Next の方が、分かり易いですよ。

Dim i As Long
　i=1
Do　
　If Cells(i,2).value = "" then　Exit Do '空白ならループを抜ける。　　　　　　　　

　'処理
　i = i + 1
Loop

もしくは

Dim i As Long
　i=1
Do until Cells(i,2).value = ""
　'処理
　i = i + 1
Loop


>現状はこれでリストのファイルは新しいBOOKになって開かれるのですが、
>このあと”開いたBOOKのB列をもともと開いているBOOKのA列に順序良くコピーする”
>という事を行ないたいのですが、どの様にすれば良いのでしょうか？
開かれたブックは、アクティブになっているので、
そのときに対象ブックを変数に格納しておくと、
後々の指定が簡単になります。

Dim newWb As WorkBook

Set newWb = ActiveWorkBook

'これで新しいブックのＢ列が指定できます。
newWb.Worksheets("Sheet1").Cells(i,2).Copy

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

書式などが要らない場合、セルの値のみコピーするのであれば、
.Value プロパティで受け渡しできます。

ThisWorkBook.Worksheets("Sheet1").Cells(i,2).Value = _
newWb.Worksheets("Sheet1").Cells(i,2).Value


また列の一定の範囲をコピーするのであれば、
Endプロパティを使えば、ループしなくても出来ます。

Dim LastRow As Long

with newWb.Worksheets("Sheet1")
　　
　　LastRow = .Range("B65536").End(Xlup).Row '最終行の取得

　　.Range(.Cells(1,2) , .Cells(LastRow,2)).Copy
【9860】Re:BOOK間で値のコピーを繰り返しって？
質問  みい  - 03/12/19(金) 14:25 -
引用なし
パスワード
   INAさん、ありがとうございます。
早速、教えて頂いたものを反映してみました。
opentextでファイルを開いたあとに追加してみましたが
　 newWb.Worksheets("Sheet1").Cells(i, 2).Copy
のところで「インデックスが有効範囲にありません」とエラーが出てしまいます。
何故なのでしょうか？

↓追加した構文です。
Dim newWb As Workbook
Set newWb = ActiveWorkbook
Dim i As Long
　i = 1
Do Until Cells(i, 2).Value = ""
　newWb.Worksheets("Sheet1").Cells(i, 2).Copy
　Windows("BOOK1.xls").Activate
　ActiveSheet.Cells(i, 1).Paste
　i = i + 1
Loop

私自身VBAの基礎が出来ていない中、質問してしまい
何度も申し訳ありませんがご指導願います。
【9865】Re:BOOK間で値のコピーを繰り返しって？
回答  Jaka  - 03/12/19(金) 15:26 -
引用なし
パスワード
   これだとどうですか？
遅いかもしんないけど。

Dim FileN As String, AcShn As String, Cbn As String, CpBn As String
Dim BEr As Long
FileN = Application.GetOpenFilename("リストファイル(*.lst),*.lst")
If FileN = "False" Then
　 End
End If
AcShn = ActiveSheet.Name
Workbooks.OpenText FileName:=FileN, StartRow:=34, DataType _
　　:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other:=True, _
　　OtherChar:="|", FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 9), Array(5, 9), Array(6, 9), Array(7, 9), Array(8, 9))
Cbn = Dir(FileN)
ThisWorkbook.Activate
CpBn = Left(Cbn, Len(Cbn) - 4)
BEr = Workbooks(Cbn).Sheets(CpBn).Range("B1").End(xlDown).Row
ThisWorkbook.Sheets(AcShn).Range("A1:A" & BEr).Value = Workbooks(Cbn).Sheets(CpBn).Range("B1:B" & BEr).Value
【9867】Re:BOOK間で値のコピーを繰り返しって？
回答  INA  - 03/12/19(金) 15:31 -
引用なし
パスワード
   シート名はあっていますか？

>　newWb.Worksheets("Sheet1").Cells(i, 2).Copy
>　Windows("BOOK1.xls").Activate
>　ActiveSheet.Cells(i, 1).Paste　　
　　↓
'シートインデックスで指定する方法に変更。
newWb.Worksheets(1).Cells(i, 2).Copy _
Destination:=ThisWorkbook.ActiveSheet.Cells(i, 1)

copyメソッドは、引数にDestinationとして、コピー先を指定できます。
対象をアクティブにしないで済むので、この方が処理が速いです。　


>私自身VBAの基礎が出来ていない中、質問してしまい
>何度も申し訳ありませんがご指導願います。
ここは質問する掲示板なので、そんなに気にしないで下さい。
【9869】Re:BOOK間で値のコピーを繰り返しって？
お礼  みい  - 03/12/19(金) 15:38 -
引用なし
パスワード
   JAKAさん、INAさん、ありがとうございます！！
早速やってみますね。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
12571 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free