エクセルVBA練習帳、CSVファイルの活用、カンマ区切りデータベース書出しと読み込み。For…Nextを入れ子で使う、Split関数の使い方などを練習します

エクセルVBA
この記事は約6分で読めます。

CSVファイルの活用

筆者の前職は事業所給食サービスの会社で調理師をしていました。仕事柄献立表やレシピ作成のさい食品栄養成分データを扱うことがありました。
具体的には、会社が提供する献立作成システムから献立データをCSVファイルで受け取り、エクセルで加工して食堂内に掲示する献立表やプライスカードなどに表示する栄養情報を自動的に印刷するなどしていました。もちろん、VBAで自動化することで、作業を効率化していたのは言うまでもありません。

食品栄養成分表とは、
メニューのレシピを作成するとき、その献立の栄養成分やカロリーを計算するときに必ず用いられるもので一行の成分項目数は60以上で食品数は2000行位の割と大きな表で構成されています。
(栄養成分表は文部科学省のホームページから八訂Excel版が入手できます。)

栄養成分を計算するソフトなどは会社で用意するシステムではレシピ作成から食材発注、納品、在庫管理など一連の食堂運営を一元的に管理するシステムになっていましたが、全国に数百ある事業所単位では現場のニーズに合わせた細かいデータ処理は各事業所単位に加工できるようCSVファイルで提供するようになっているようでした。

掲示用週間献立表やプライスカードにはその献立のカロリー表示等、主要栄養成分値の表示などを入れ込む必要がありました。また、各事業所管内の保健所への各種提出書類などにも栄養項目のデータを入れ込む必要もあった為、社内システムから吐き出されるCSVファイルを活用することは必須だったわけです。VBAのスキルを活かして自分の仕事環境をより効率的に構築できるのですからぜひとも皆さん方にも挑戦していただきたいと思います。

 

 

練習帳のダウンロード

 

データベースを作成

テスト用データベースを作成する

最初に、ダウンロードしたファイルのワークシートには何もデータがありませんから、練習用のデータを書き込みします(データベースを作成)。

メッセージボックスでOKボタンを押して下記の様な簡単な(なんの意味もない)データベースを作ってみました。

 

データを書き込んだら一旦、保存してファイルを閉じます。

CSVファイルに書き出す

ファイルを開きます。

ダイアログが開いたらデータベースを発見した旨メッセージを確認できます。

書出しボタンを押すと瞬時に作業が終わります。

書き出しましたのメッセージが出るのでOKボタンを押します。

メッセージにはファイルが書き出されたフォルダー名が書いてあります。

(設定ではデスクトップのtesutfoludrに格納するようになってます)

一応確認してみましょう。

上図の様にtestfoldrを開くとファイル名CSVdata.csvがあるので、それを開くと下図のようになっていました。(書き出す前のシート上のセルのデータがカンマを区切りにして書き出されているのがわかりますね。)

CSVファイルに書き出すメイン処理(入れ子のFor…Next)

フォームの書出しボタン で呼び出されるプロシージャの解説になります。

注目は行番号29行目の For  i= 1 To DBr と、
行番号30行目の For j = 1 To DBcです。そして、31行目 txt = kanmaText(Cells(g + i, r + j)) の
Cells(g + i, r + j)でワークシートのセルのデータを取り出しています。カンマの左側はセルの行位置で右側が列位置ですから、最初のForでiに1が入ってくるのでシートのデータベースの一行目を意味します。
そして、次の入れ子のForで、一行目の列の位置を順次入れ替えてデータを取り出しています。列のすべてのデータを取り終わったら最初のForに戻って次にiに2が入って2行目のデータの取り出しをするという具合です。DBrDBcはワークシート上のデータベースの大きさで最大行数と最大列数を表しています。更に、ワークシート上のデータベースの始まる位置情報をプロシージャの引数として受け取ってこれをセルの行と列にそれぞれ加算しています。そして、取り出したデータをkanmaText()関数に送って。データ中にカンマが含まれていた時はこれを取り除いています。
CSVファイルでは、数値表示のなどでカンマを桁区切りにしている場合、(1,200円)等
価格表示で、桁区切りのカンマがあるとデータの区切りのカンマとして扱ってしまうため取り除く必要があります。

 

保存済みCSVファイルをワークシートに読み込む

前項で書き出したCSVファイルをワークシートに読み込みします。

UserForm1の読込みボタンを押します。

その時点でワークシート上にあるデータベースは削除しますから、OKボタンを押します。

続いて、読込むファイルを選択するダイアログが表示されるので、その中からCSVdata.csvを選択して開きます。(OKボタンを押します)

ほぼ、瞬時にワークシートにCSVファイルの内容がワークシートに張り付きます。

ここからは、フォームの読込みボタンので呼び出されるプロシージャの解説になります。

読込み処理の要、Do…Loopを使った繰り返し処理

今までは主にFor…Nextの繰り返し処理を使ってきましたが、ここではDo…Loopを使います。For…Nextは繰り返す回数がわかっているときに使いますが、今回のようなCSVファイルは前もって行数を知ることができないので、その場合は
Do Until EOF()を使います。

Until EOF(intFree)の意味は、
行番号19行目のOpen varFileName For Input As #intFreeで開いたintFree番目のファイルを指定してそのファイルの終端(EOF)に達するまで繰り返しを実行するということです。

次に、
行番号24行目でファイルの一行分を取り出して

行番号27行目でその一行分のデータをカンマで区切って配列に格納します。

そして、今度は入れ子のFor…Nextを使って、一行分のデータが入った配列のデータをワークシートのセルに書き出しています。

行番号29行目 For j = 0 To UBound(strSplit) の UBound(strSplit)は引数になっている配列の要素数を返す関数ですから、その要素数だけ繰り返します。

Do…Loopの繰り返しの条件にはUntilとWhileがあります。

Do [While 条件式]
  ・・処理・・
Loop

または、

Do [Until 条件式]
  ・・処理・・
Loop

Whileは、条件を満たしている間(Trueの間)・・処理・・を繰り返します。
Untilは、条件を満たすまで(Falseの間)・・処理・・を繰り返します。

または、

Do 
  ・・処理・・
If 条件式 Then Exit Do
Loop
の様な使い方もできます。

いずれにしても何らかの条件式を使って、繰り返しを終了させないと、無限ループになってしまいますから注意が必要です。

 

メインのコード

UserFormコード

 

Moduleコード

 

コメント

タイトルとURLをコピーしました