Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
別ブックのデータをコピペしたい
投稿日時: 22/06/18 08:48:59
投稿者: シナモンジンジャー

別のブックの各データを取り込むマクロリストを作成しましたが、取込先のそれぞれのシートのデータ行数が変化するので変数を使ったのですが、途中で"中断モードでコードを実行することができません"と表示されてストップしてしまいます。
下のコードの何処が不具合を起しているのか分からないので教えてください。
 
Sub 別ブックデータの取込()
  
    Dim SetFile As String
    Dim Moto, Saki As Workbook
    Dim Lastrow As Long
  
    Set Moto = ActiveWorkbook
    Application.DisplayAlerts = False            '警告非表示  
    SetFile = "F:\MO1\02EXCEL\エクセルデータ\04_委員報酬テスト.xlsm" '取込先パス
    Workbooks.Open Filename:=SetFile, ReadOnly:=True, UpdateLinks:=0    '読取専用で開く
 
    Set Saki = Workbooks.Open(SetFile)        '開いたブックをワークシート変数にセット
    Lastrow = Saki.Worksheets("5月分").Range("O2").Value '取込先のデータ終端値取得
    Saki.Worksheets("5月分").Range(Cells(5, 2), Cells(Lastrow, 13)).Copy '取込先のデータの範囲をコピー
 
    Moto.Worksheets("集計表").Range("U5").PasteSpecial xlPastValues '元ブック"集計表"のU5〜に値のみペースト
    Application.CutCopyMode = False        'コピーモードを解除
    Saki.Close False                '取込先ファイルを閉じる
    Application.DisplayAlerts = True
  
End Sub
 
よろしくお願いします。

回答
投稿日時: 22/06/18 09:23:01
投稿者: hatena
投稿者のウェブサイトに移動

まず、
 

Dim Moto, Saki As Workbook

これだと、Moto はVariant型になります。
これでも動きますが、きちんとデータ型を指定した方がいいでしょう。
 
Dim Moto As Workbook, Saki As Workbook

 
次に、下記の部分、
    Workbooks.Open(Filename:=SetFile, ReadOnly:=True, UpdateLinks:=0)    '読取専用で開く

    Set Saki = Workbooks.Open(SetFile)        '開いたブックをワークシート変数にセット

同じファイル(SetFile)を2回開いてます。下記のように1行にまとめましょう。
   Set Saki =  Workbooks.Open Filename:=SetFile, ReadOnly:=True, UpdateLinks:=0    '読取専用で開きワークシート変数にセット

 
下記はどういう意味でしょうか。
   Lastrow = Saki.Worksheets("5月分").Range("O2").Value '取込先のデータ終端値取得

O2セルには何が格納されているのでしょうか。
データ範囲の最終行番号でしょうか。
それはいつどのように格納していますか。
 
データをすべてコピーするなら、最終行番号を取得する必要がありますが、
通常、最終行番号は下記のようなコードで取得できます。
   Lastrow = Saki.Worksheets("5月分").Cells(Rows.Count, 2).End(xlUp) '取込先のデータ最終行番号

 
とりあえず、上記の点の修正、確認をしてみてください。

投稿日時: 22/06/18 10:26:41
投稿者: シナモンジンジャー

hatena 様
早々のご教示ありがとうございます。
シートの定義づけは、2つともワークブックの定義をしました。 よかったです。
開いたブックを Saki に代入するコードは、下のように書き換えましたら、"構文エラー"と表示されてしまい、
1行にまとめることができませんでした。
Set Saki = Workbooks.Open Filename:=SetFile, ReadOnly:=True, UpdateLinks:=0
 
Lastrowは、取込先ファイルの必要データをカウントしている数値で、(計算結果"")以外のCOUNTA数合計がセルO2に算出されています。
なので.Cells(Rows.Count, 2).End(xlUp) でもうまくいきそうだと思いましたが、デバグが出ます。
空白行でなくすべて数式が入っていて計算結果が "" というだけなので、カウントする必要があると思ってセルO2に合計を算出しているところです。
 
取込先ファイルのセルO2の値(.Value)を終端行に設定する方法を間違っているような気がします。
 
 

回答
投稿日時: 22/06/18 13:48:51
投稿者: simple

Bookオブジェクト変数の作成部分は、

   Set Saki = Workbooks.Open(Filename:=SetFile, ReadOnly:=True, UpdateLinks:=0)
としてください。
 
最終行の件。
引用:
Lastrowは、取込先ファイルの必要データをカウントしている数値で、(計算結果"")以外のCOUNTA数合計がセルO2に算出されています。
なので.Cells(Rows.Count, 2).End(xlUp) でもうまくいきそうだと思いましたが、デバグが出ます。
空白行でなくすべて数式が入っていて計算結果が "" というだけなので、カウントする必要があると思ってセルO2に合計を算出しているところです。

行番号を得るなら、
Lastrow = Saki.Worksheets("5月分").Cells(Rows.Count, 2).End(xlUp).Row
でしょうけど、これだと""もデータと考えた最終行となります。
実際に(""以外の)データが入った最終行ということなら、求めるものとはなりません。
O2セルの計算式が示されていませんが、
> (計算結果"")以外のCOUNTA数合計がセルO2に算出されています。
ということであれば、それを使うことでよいと思います。

投稿日時: 22/06/18 16:19:24
投稿者: シナモンジンジャー

simple様
ご教示ありがとうございます。
 Sakiに取込先のファイルを代入するコードを1行にまとめるのはうまくいきました。
 ありがとうございます。
 しかし、前にも書かせていただいたとおり、Lastrowの取得コードが間違っているのではないかとと考えており、今回の修正でも、途中で止まってしまいました。
 
 Lastrow = Saki.Worksheets("5月分").Range("O2").Value  '取込先のデータ終端値取得
取込先のRange("O2")には =COUNTA(B5:B307)+4 という数式が入っており、委員報酬を支払った人数+4の算出結果が表示されていますので、Valueプロパティで代入できそうな気がするのですが、このコードで"中断モードでコードを実行することができません"というデバグが出ます。
 
この代入値を取得できないので、次の
 Saki.Worksheets("5月分").Range(Cells(5, 2), Cells(Lastrow, 13)).Copy '取込みの範囲をコピー
へ進めないのではないかと思っています。
 
よろしくお願いします。

回答
投稿日時: 22/06/18 17:02:23
投稿者: simple

確認です
(1)
B列はどこまで数式が入っているのですか?
=COUNTA(B5:B307)+4 
であれば、数式が入っていれば、それが""を返すものであっても
COUNTAは1カウントされますから、数式が入っている所までの行番号を返すはずです。
つまり、実際に""以外の数値なりが入っているかどうかの判断はその式ではできないはずです。
 
(そうすれば良いかは上記の回答をもらってから提示します。)
(2)
>"中断モードでコードを実行することができません"というデバグが出ます。
の原因が思い当たりません。
どのように実行させているのか、改めて説明願えませんか?
・なにかのプロシージャを実行して止まった時に、
 別のプロシージャを実行させるボタンを押したとかいうことなら納得なんです。
・単一のプロシージャを実行させるだけなら、そういう警告は出ないはずなんです。
(3)
Saki.Worksheets("5月分").Range(Cells(5, 2), Cells(Lastrow, 13)).Copy
はエラーになる可能性が高いです。
Cells(5, 2)と
Cells(Lastrow, 13)は
現在アクティブなブックの、アクティブシートの、そのセル
と指定したことになるからです。

    Dim ws As Worksheet
    Set ws = Saki.Worksheets("5月分")
    ws.Range(ws.Cells(5, 2), ws.Cells(Lastrow, 13)).Copy
などとする必要があります。

投稿日時: 22/06/18 19:43:22
投稿者: シナモンジンジャー

simple様
再度のご教示恐縮です。丁寧にありがとうございます。
無事に取込先ブックの取込先シートの必要範囲を元ブックの元シートの指定したセルにコピペできました。ありがとうございました。
(1) B列はどこまで数式が入っているかという点は、空行と支払いした委員氏名がまだらに入っている列になります。
 
(2) 先のコードをVBエディターウインドウのF8キーで順番に実行しているだけで、"中断モードでコードを実行することができません"と表示されるので、どのコードが中途なのかは分かりません。
 
(3) ご指摘のとおり取込先のシートなのか、元のファイルのシートなのか定義づけをしていなくて、ドットでつないでいた点が問題でした。
今回特に勉強になったのは、2つのブックのデータをやりとりするためには、代入する変数が、ブックだけでなくシートも定義づけが必要だということでした。
下が成功したコード表です。実際のファイルは項目が多く、共有フォルダに入力される部署も多いのでこのコード表ですごい時短になります。本当にありがとうございました。
 
Sub 別ブックデータ取込01()
  
  Dim SetFile As String
  Dim Moto As Workbook, Saki As Workbook
  Dim Ms As Worksheet, Ss As Worksheet
  Dim Lastrow As Long
    Set Moto = ActiveWorkbook
    Application.DisplayAlerts = False
        
    SetFile = "F:\MO1\02EXCEL\エクセルデータ\04_委員報酬テスト.xlsm" '取込先ファイルパスを代入
    Set Saki = Workbooks.Open(Filename:=SetFile, ReadOnly:=True, UpdateLinks:=0) '取込先ファイルを読取専用で
    Lastrow = Saki.Worksheets("5月分").Range("O2").Value '取込先ブックのデータ終端値取得
    
    Set Ss = Saki.Worksheets("5月分")             '取込先ブックの取込先シートをSsに代入
    Ss.Range(Ss.Cells(5, 2), Ss.Cells(Lastrow, 13)).Copy '変数範囲をコピー
    
    Set Ms = Moto.Worksheets("集計表")         '元ブックの取込元シートをMsに代入
    Ms.Range("U5").PasteSpecial Paste:=xlPasteValues  '指定のセル〜に値をペースト
    
    Application.CutCopyMode = False
    Saki.Close False
    Application.DisplayAlerts = True
 End Sub