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

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

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

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

Q&A

解決済

2回答

2418閲覧

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

退会済みユーザー

退会済みユーザー

総合スコア0

VBA

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

0グッド

0クリップ

投稿2017/12/25 06:36

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

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

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

###補足情報(言語/FW/ツール等のバージョンなど)
エクセル2016を使用しています。

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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


前回作成したマクロでは、転記元ブックが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/26 05:30

編集2017/12/26 07:00
jawa

総合スコア3013

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/12/27 06:16

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

退会済みユーザー

2018/01/09 06:27

度々の質問を失礼いたします。 マクロを実行してみているのですが、 ループ処理の次の '転記元ブックをオープン Set copyWb = Workbooks.Open Filename:=FILE_PATH & "\" & sFileName がコンパイルエラー(構文エラー)と出てしまい、 タイポなどないか何度も確認しているのですが、自力で解決に難航しています。 どのように検証したらよいか、アドバイスいただけましたら幸いです。 Const FILE_PATH = "フォルダ場所を入れています" sFileName = Dir(FILE_PATH & "*.xlsx*")と入れています どうぞよろしくお願いいたします。
jawa

2018/01/09 07:09

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

退会済みユーザー

2018/01/10 01:10

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

2018/01/10 02:16 編集

自分のコーディングミスを元に解説するのも変な話ですが、今回の修正のポイントは以下の3点です(^-^; ①「:=」での引数指定は「名前付き引数」という書き方です。 ⇒「VBA 名前付き引数」などでググると、その利点や使い方の解説がいろいろでてきます。 ②VBAの文法では、戻り値を受け取るときは括弧で括ります。 ⇒括弧で括らないのは戻り値を受け取らない場合の書き方で、これが原因でコンパイルエラーとなっていました。 今回はブックをオープンした際、開いたブックを戻り値として取得したいので括弧つきの書き方が正解です。 ③先頭シート(一番左のシート)はWorksheets(0)ではなく(1) ⇒これは単純にシートのインデックスは0から始まるものと勘違いしていました。 以上、参考になれば幸いです。 がんばってください。
退会済みユーザー

退会済みユーザー

2018/01/10 06:56

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

退会済みユーザー

2018/01/12 02: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 04:11

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

2018/01/12 05: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 05: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が出て、変更したコードが黄色くなってしまいました。 違うところを変更してみるとよいでしょうか? 理解がなかなか進まず大変恐縮です、ご指導いただけますと幸いです。 よろしくお願いいたします。
jawa

2018/01/12 08: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 08:29

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

0

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

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

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

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

投稿2017/12/25 07:41

ExcelVBAer

総合スコア1175

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問