2015.09.07みなさん、こんにちは!このところひたすらVBAと戯れていますタカハシ(@ntakahashi0505)です。エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズもいよいよ今回で8回目です。前回はこちらの記事で【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVLookupメソッドでデータを取得してくる方法についてお伝えしていきます。tonari-it.com2015.08.26メンバーが入力ミスをしている経費精算書でも、ファイル名とマスタを使って正しくデータを蓄積する対策についてお伝えしました。今回は、一連のシリーズの最終回。重複チェックとデータの上書きの機能について追加をしていきたいと思います。前回完成したプログラムがこちらです。Sub各メンバーから受け取った経費精算書について、以下図のように「経費データ」シートに蓄積をしていきます。前回はファイル名objFile.Nameから、Val,Right,Replaceを使って社員番号を抜き出すそれをキーにしてWorksheetFunction.VLookupにて社員マスタから部署名、社員の氏名を取得というお話でした。今回ですが、例えば同じファイルを誤って複数回取り込んでしまったらどうなるか?ということなのですが、実際にやってみるとこのように、重複してどんどんデータが追加されてしまいます。これは危険ですね...。これを防ぐために、既に同じ対象月の同じ社員のデータが入っていた場合は、それをまず削除してしまうという処理を追加したいと思います。ある重複データを削除する処理の追加方針まず考え方をまとめてみましょう。4))この後に入れる形になります。削除の処理は「重複削除」という名前でSubプロシージャを用意して、そのプロシージャに対象月と社員番号を渡して処理をしてもらうということにしましょう。1今はプログラムの冒頭に実行するようになっていますが、行の削除をした場合は行数が変更になりますから、重複削除プロシージャの後に場所移動します。'「経費データ」シートを入れるオブジェクト変数は重複削除プロシージャでも使うので、プロシージャの外に出してPublic宣言をします。Subプロシージャの呼び出しとPublic変数についてはこちらの記事も合わせてご覧ください。Permanentlytonari-it.comDim変数を渡す場合のCallによるプロシージャ呼び出しプロシージャ内でDim宣言した変数は宣言したプロシージャ内でしか使えないので、他のプロシージャでも使いたい場合は変数を渡す必要があります。プロシージャ名(変数1,変数2,...)今回の場合は対象月と社員番号を渡しますが、strPeriodは6桁の文字列型でDate型ではありませんので、対象月を表すDate型に変換してあげる必要がありますね。1)この上で、対象月datePeriodと社員番号numStaffを重複削除プロシージャに渡せばOKです。numStaff)重複削除プロシージャ重複削除プロシージャは、このように書きます。プロシージャ名(変数1,変数2,...)と書きます。変数名は渡されたときと別の名前にすることができますが、わかりやすさのために同じ名前にしています。While~Loopで「経費データ」シートの2行目から最後尾までを順番に見ていきます。If文ですが、今対象となっている行の対象月がdatePiriodと同じでかつ社員番号がnumStaffと等しければ、その行を削除します。行の削除はもうご想像できていると思いますがRows(行番号).Deleteと書きます。While~Loopに入っても問題ありません。If文の条件式にマッチしていなければ、カウント変数iを加算して次の行に移動します。まとめ同じ経費精算書データを取り込む際のデータ重複を防ぐ重複削除プロシージャを作成しました。元のデータを削除して新たなデータを追加するわけですから、データに修正を加えたいときにも有効に使えますね。最後に完成したプログラムを載せておきますね。Subではまた別のシリーズでお会いしましょう!連載目次:エクセルVBAで経費データをデータベースに集約する請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。プランノーツ代表、コミュニティ「ノンプロ研」主宰。ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ★フォロー頂ければ嬉しいです。「コミュニティ+講座」形式で継続性&教え合える環境も提供。!ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。【エクセルVBA】シートの開始行や行数の変更があってもちゃんと動く繰り返し文の書き方エクセルではワークシートの運用上、行の挿入や行の追加が発生することが多々あります。今回はエクセルVBAでワークシートの開始行や行数の変更があっても繰り返し処理がちゃんと動くプログラムの作り方です。2016.05.09Excel・VBA活用術エクセルVBAでピボットテーブルを操作するための第一歩~ピボットテーブルオブジェクト変数~エクセルのピボットテーブル操作(作成、更新など)をVBAで自動化の第一歩、オブジェクト変数へ操作対象のピボットテーブルをセットする方法を、「オブジェクトとは?」というところからご紹介しています。ピボットテーブルの操作が自動化できれば、データ加工からピボットテーブル完成まで完全自動化だってできますよ!2017.01.06Excel・VBA活用術エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度についてエクセルVBAでAccessデータベースを操作する方法について、今回はADODBレコードセットのFind、Update、MoveFirstを使って更新する方法について、またその実行速度についてです。簡単!エクセルVBAでシート上のグラフをコピーしてPowerPointにペーストするエクセルVBAでPowerPointを操作する方法についてお伝えしています。今回はエクセルVBAでエクセル上のグラフをコピーしてPowerPointにペーストをする方法です。VBA】NewMailExイベントで受信したメールの内容をエクセルシートに書き出す方法OutlookのNewMailExイベントを使って、対象のメールを受信したら、メールの内容を指定のエクセルシートに書き出す、という方法をご紹介しています。日々のメールの内容を控えておく必要があったりする場合に、自動的にメールの内容をエクセルシートに書き出しておいてくれるので便利ですよ。【エクセルVBA】VBEのツールバーを自分好みにカスタマイズする方法VBEにあるユーザー設定で、ユーザー好みのツールバーを作成する方法をご紹介しています。コメントアウトやコンパイルなど、よく使うコマンドをツールバーにセットして画面に表示しておけば、すぐに使えて便利ですよ。コマンドのショートカットキーを忘れてしまった!なんていうときも、ツールバーにセットしておけば安心です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Apps2PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント109セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Apps2PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント109セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。開発者プログラムとは、またどうして期限切れになったのか2020.02.222020.02.22ノンプロ研の福岡で初イベント「ノンプロ研無料体験@福岡」を開催しました!2020.02.14GASのV8ランタイムとRhinoランタイムのログと実行のUIの違いについて比較&解説2020.02.092020.02.09新構文をさっそく試そう!GASのプロジェクトをV8ランタイムに切り替える方法2020.02.082020.02.12Facebookページいつも隣にitのお仕事タイトルとURLをコピーしました。