質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.61%

  • VBA

    1727questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1470questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

エクセルシートの中に既に値が入力されているか、複数のエクセルブックについて一気に確かめたい

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 694
退会済みユーザー

退会済みユーザー

前提・実現したいこと

一枚のエクセルシートに、複数の見積書のエクセルブックの情報を取り込みました。
しかし、一部エクセルブックについて、取り込みを取りこぼしていることに気づきました。
取り込み先のエクセルシートには、見積書番号を転記しており、
取り込み元のブックをひとつひとつ開いて、取り込み先に見積書番号があるか検索していましたが、
残り1000ブックぐらいあるため、
VBAか何かを使って一気に、取り込みができていないエクセルブックがどれか確認する方法があれば知りたいです。

発生している問題

どのように一気に複数のブックについて、転記先のシートと照合確認したらよいのかわからない。

試したこと

ネットで検索しましたが、検索用語が間違っているのか、情報が見つかりませんでした。

補足情報(言語/FW/ツール等のバージョンなど)

エクセル2016を使用しています。

どうぞよろしくお願いいたします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

おそらく前回の質問のマクロで取り込まれたものと推測します。
※今回の質問は「前回の質問の関連」とも「新たな別の質問」とも取れますが、皆が前回の質問を読んでいるわけではないので今回のように関連記事もコード記載もない質問だと丸投げととられてしまいますよ。


前回作成したマクロでは、転記元ブックがActiveWorkbook固定となっていました。
この部分を少し改造して、対象ファイルを順次開いていくようにしてあげればよいと思います。
この際、対象ファイルはマクロで処理しやすいよう、ひとつのフォルダにまとめておくとよいでしょう。

そのうえで前回マクロの改造ですが、必要な改修は以下の3つです。

①対象フォルダにあるExcelブックを順次読み込む

②転記元は①で開いたブックの先頭シートとする ※シート名が決まったものであればシート名指定でも可。
※必要であればここで転記元シートの見積書番号で転記済みかどうか判断する

③転記が終わったら①で開いたブックを閉じ、①の処理へ戻る。
(フォルダ内のすべてのファイルに対し①~③を繰り返す)

②で「必要であれば」条件破断すると書きましたが、もしもう一度全部転記しなおすのであればこの条件判断は不要です。

見積書番号の重複を判断する方法としては、ExcelVBAerさんのアドバイスが参考になると思います。


以下、前回コードをベースとしたファイル連続取り込みするサンプルコードです(一部省略)

Sub Macro1()
    '転記元ファイルの取得用変数
    Const FILE_PATH = "C:\Tera\XLS"
    Dim sFileName As String

    '転記元のシートの変数
    Dim copyWb As Excel.WorkBook
    Dim copyWs As Excel.Worksheet
    '転記先のシートの変数
    Dim pasteWs As Excel.Worksheet
    '出力行
    Dim iPasteRow As Integer

    '転記先のシートを取得
    'マクロが書かれているブックの、アクティブなシート
    Set pasteWs = Excel.ThisWorkbook.ActiveSheet

    '出力する空白セルの指定
    Dim pasteCell As Excel.Range
    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "A").End(xlUp).Offset(1)

    '出力行を取得(先頭行)
    iPasteRow = pasteCell.Row

    '対象フォルダからExcelファイル名を取得
    sFileName = Dir(FILE_PATH & "\*.xls*")
    If sFileName = "" Then
        'フォルダにExcelファイルが1つもない場合は処理終了
        Exit Sub
    End If

    '対象フォルダ内のすべてのExcelファイルをループ処理
    Do
        '転記元ブックをオープン
        Set copyWb = Workbooks.Open Filename:=FILE_PATH & "\" & sFileName
        '転記元シートを取得
        Set copyWs = copyWb.WorkSheets(0)   '先頭シート ※シート名が固定ならシート名指定でも可。

        '除外するものがあればここで条件を指定して転記処理に入れない
        If TRUE = TRUE Then 
            '除外するもの以外は転記処理
            '~省略~

            '~省略~

            '転記したら出力行を1行進める
            iPasteRow = iPasteRow + 1
        End If

        '転記元ブックを閉じる
        copyWb.Close SaveChanges:=False

        '次のファイル名を取得
        sFileName = Dir

    Loop Until sFileName = ""    'ファイル名が取得できなくなるまで繰り返す

End Sub


参考になれば幸いです。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/27 15:16

    早速のアドバイスありがとうございます。
    質問マナーから至っておらず大変恐縮です、
    具体的に考え方?の流れもわかりやすくアドバイスくださり、ありがとうございます。
    まだ、自分でいただいたアドバイスを元に、うまく計算や実行ができていないので、試行錯誤してみます。
    またわからないことがあれば、質問させていただくと思いますが、
    どうぞ今後ともよろしくお願いいたします。

    キャンセル

  • 2018/01/09 15:27

    度々の質問を失礼いたします。
    マクロを実行してみているのですが、
    ループ処理の次の

    '転記元ブックをオープン
    Set copyWb = Workbooks.Open Filename:=FILE_PATH & "\" & sFileName

    がコンパイルエラー(構文エラー)と出てしまい、
    タイポなどないか何度も確認しているのですが、自力で解決に難航しています。

    どのように検証したらよいか、アドバイスいただけましたら幸いです。

    Const FILE_PATH = "フォルダ場所を入れています"
    sFileName = Dir(FILE_PATH & "\*.xlsx*")と入れています

    どうぞよろしくお願いいたします。

    キャンセル

  • 2018/01/09 16:09

    すみません、こちらで動作確認したものと提供したコードが少し違ってたようです。
    以下2文を修正してみてください。
    ```
    '転記元ブックをオープン
    Set copyWb = Workbooks.Open(FILE_PATH & "\" & sFileName)
    '転記元シートを取得
    Set copyWs = copyWb.Worksheets(1) '先頭シート ※シート名が固定ならシート名指定でも可。
    ```

    キャンセル

  • 2018/01/10 10:10

    早速のアドバイス誠にありがとうございます!
    うまく実行できました!あんなに動かなかったのが、動いてとても不思議で感激です!
    どうして動くようになったのか調べてみて、また自力で煮詰まってしまったらご質問させていただくかもしれませんが、どうぞよろしくお願いします!

    キャンセル

  • 2018/01/10 11:15 編集

    自分のコーディングミスを元に解説するのも変な話ですが、今回の修正のポイントは以下の3点です(^-^;

    ①「:=」での引数指定は「名前付き引数」という書き方です。
    ⇒「VBA 名前付き引数」などでググると、その利点や使い方の解説がいろいろでてきます。

    ②VBAの文法では、戻り値を受け取るときは括弧で括ります。
    ⇒括弧で括らないのは戻り値を受け取らない場合の書き方で、これが原因でコンパイルエラーとなっていました。
    今回はブックをオープンした際、開いたブックを戻り値として取得したいので括弧つきの書き方が正解です。

    ③先頭シート(一番左のシート)はWorksheets(0)ではなく(1)
    ⇒これは単純にシートのインデックスは0から始まるものと勘違いしていました。

    以上、参考になれば幸いです。
    がんばってください。

    キャンセル

  • 2018/01/10 15:56

    ご丁寧なご解説賜り、大変ありがとうございます!
    まだ用語などさっぱりなところがあるので、よい機会に掘り下げて調べてみたいと思います!

    キャンセル

  • 2018/01/12 11:57

    度々の質問を、誠に恐縮です。
    懲りず、何卒アドバイスをいただけますと幸甚です。

    「出力する空白セルの指定」に、いきづまってしまいました。

    困っている状況は、
    1つ目の転記元のデータがうまく転記先のテーブル(A2-I17。厳密には、D列だけ値が17行目まで転記され、その他は2行目以外転記元にデータがなかったので空白セルになっています。)
    にはいりましたが、
    2つ目の転記元のデータをペースト実行されると、1つ目のデータの2行目から上書きされてしまいます。(A3-I6まで2つめの転記元データが入り、2行目と7行目以降は、1個目のデータが残っている状態。)

    最大数の行にデータが入るD列の一番最下セルを探し、offset(1)でその下の行から次の転記元データを入れると理解しておりまして、
    '出力する空白セルの指定
    Dim pasteCell As Excel.Range
    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "D").End(xlUp).Offset(1)

    Dim pasteCell As Excel.Range
    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "D").End(xlLeft).Offset(1)

    Dim pasteCell As Excel.Range
    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "D").End(xlUp).End(xlLeft).Offset(1)
    と、試してみたりしてみましたが、
    そうすると実行ができずにエラー表示が出てデバッグの画面になってしまいます。
    何卒またお力添えいただけると幸いです。
    よろしくお願いいたします。

    キャンセル

  • 2018/01/12 13:11

    表示されるエラー記載失念しておりました。
    「実行時エラー6 オーバーフローしました」です。
    デバッグの画面には、
    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "D").End(xlUp).Offset(1)
    の行が黄色くハイライトされています。
    どうぞよろしくお願いいたします。

    '出力行を取得(先頭行)
    iPasteRow = pasteCell.Row

    キャンセル

  • 2018/01/12 14:10

    まず、提示したコードは、複数のファイルを順次オープンしていくループに入る前に、「A列の最初の空白セル」を探して貼り付け開始行を取得しています。
    その後、ファイルをループしていく中では、1行出力したら次の出力位置を+1していきます。
    1ファイル処理が終わって次のファイルを読んだとしても、出力行はリセットしていないので前回出力した次の行に続けて出力される流れになっています。

    ここまではマクロを1回実行したときの動きです。
    マクロを2回起動した場合、2回目のマクロの開始時点でもう一度「A列の最初の空白セル」を探して貼り付け開始行とすることになります。

    「A列の最初の空白セルを出力開始位置にする」という前提で処理を行っていますので、「A列の最初の空白セル」を出力開始行にできないケースとなってしまったことで意図しない動きをしているのではないでしょうか。


    手っ取り早い回避策としては、あらかじめ同一フォルダにファイルをまとめてからマクロを1回だけ実行するという方法があります。
    1回のマクロ実行で全てのファイルを処理するのであれば、出力行が戻ってしまうこともないと思います。

    それができない場合、「A列の最初の空白セルを出力開始位置にする」という部分を見直す必要があります。
    例えばA列だけでなく出力される全ての列を対象に最終行を判断すれば、追記位置を間違えることはなくなると思います。

    ---
    エラーについて

    `Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "D").End(xlUp).Offset(1)`
    でオーバーフローが発生したとのことですが、このコードでエラーが発生する原因が思い当りません。
    エラー発生時のコードは別のものだったとかないでしょうか?

    例えば`Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "D").End(xlToLeft).Offset(1)`
    は最終行からShiftしてさらに1つ下のセルを参照しようとするのでオーバーフローすると思います。

    キャンセル

  • 2018/01/12 14:44

    早速のご返信と考え方のヒントをありがとうございます!

    手っ取り早い回避策としては、あらかじめ同一フォルダにファイルをまとめてからマクロを1回だけ実行するという方法があります。
    >>一つのフォルダ内に転記元のデータをまとめていましたので、
    いただいたヒントを元に、転記先のデータも転記元と同じフォルダに入れて実行してみているのですが、
    とんちんかんなことしてますでしょうか??

    それができない場合、「A列の最初の空白セルを出力開始位置にする」という部分を見直す必要があります。
    例えばA列だけでなく出力される全ての列を対象に最終行を判断すれば、追記位置を間違えることはなくなると思います。
    => !なるほどです!自分ひとりでは、解決策のアイデア浮かばなかったので大変助かりました!

    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "A:I").End(xlUp).Offset(1)

    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "A:I").End(xlRight).End(xlUp).Offset(1)
    と変更してみましたところ、エラー1004が出て、変更したコードが黄色くなってしまいました。
    違うところを変更してみるとよいでしょうか?

    理解がなかなか進まず大変恐縮です、ご指導いただけますと幸いです。
    よろしくお願いいたします。

    キャンセル

  • 2018/01/12 17:11

    少し言葉が足らなかったようで申し訳ありません。

    まず私の推測として、今回の現象(D17まで出力されているのにA3に出力される)は、1回のマクロ処理では発生しないのでは?と思っています。
    提示したコードの処理の流れでは、A列から空白セルを探すのはループ前の1回だけで、ループの途中で出力行を戻す処理は存在しないからです。

    なので想定外の動作とは、1回目のマクロ実行の結果D17セルまで出力され、その状態からさらに2回目のマクロを実行するとA18セルから出力してほしいのにA3セルから出力される、という状態ではないかと推測していました。
    ここまでが実際の操作と違うようであればご指摘ください。

    上記であれば、マクロの実行を2回にわけず、1回で処理する(あらかじめ対象ファイルを1フォルダにまとめる)ことで回避可能と考えた次第です。
    もし1回のマクロ実行で出力位置がおかしくなるようでしたら、一度ソースを提示していただいた方がよいかもしれません。

    ---
    #Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "A:I").End(xlUp).Offset(1)
    #や
    #Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "A:I").End(xlRight).End(xlUp).Offset(1)
    #と変更してみましたところ、エラー1004が出て、変更したコードが黄色くなってしまいました。

    これについては単純に構文の問題です。
    変更されたのはCells関数の第2引数の部分ですが、これは列番号を指定する引数で、列範囲を指定することはできません。
    そもそもCells関数というのは単一セルを返す関数ですので、範囲指定はできません。

    「複数列で最終行をとる」というのは簡単そうでいて実は割と面倒です。
    その中でも一番確実な方法は、A列の最終セル、B列の最終セル・・・と対象列をひとつずつ確認していって、一番大きな行番号を求める方法です。
    今単純にA列だけで探しているものを、列数分ループする感じです。

    また、別の方法としてはCurrentRegionを利用する方法もあります。
    `Cells(2, "A").CurrentRegion`
    のように対象範囲の左上セルに対してCurrentRegionを取得すると、指定セルから連続してデータが入力されている範囲が取得されます。
    この中で最終セルの行番号を取得します。
    ```
    Dim r As Range
    Set r = Cells(2, "A").CurrentRegion
    MsgBox r.Cells(r.Cells.Count).Row
    ```

    対象列を下から探す`.End(xlUp)`と違い、連続するデータ範囲を取得しますので、途中に完全な空行とか、空列があるとそれ以降は範囲外となってしまいますが、今回は利用できるのではないでしょうか?

    お試しください。

    キャンセル

  • 2018/01/12 17:29

    度々の質問にもご丁寧にアドバイスいただき、大変感謝いたします!
    週末を使って、いただいたアドバイスを元に、試してみます。

    キャンセル

+1

1000以上もあるブックから情報を手作業で集める時点でVBAを考えた方が良かったのでは?
手作業でするから、漏れが生じたと考えるのが自然ですよね?

ここはコードを書いてもらう場所ではないので、ヒントだけ。

1.シート上の見積書番号をDictionaryに格納
2.FSOでフォルダの中のファイルを回してブックを開いていく
3.開いたブックから見積書番号を取得し、1のDictionaryで存在チェック
4.3で存在してない見積書番号だったら、あるフォルダに集めておく
5.開いたブックを閉じる
6.処理が終わったら、集めたフォルダを開いて、手作業でブックを開いてデータ収集
※6自体をVBAでしたいとこですが、恐らく見積書の体裁がバラバラになっているのでしょう。。。

少し難しいですが、初心者でも頑張ればできる範囲ではあります。
がんばってください。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • VBA

    1727questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1470questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • トップ
  • VBAに関する質問
  • エクセルシートの中に既に値が入力されているか、複数のエクセルブックについて一気に確かめたい