【マクロ】まったり解説ExcelでCSV取込(１)

記事

IT・テクノロジー

mikihisa

2021/04/26 17:06

この数年で、オンラインで自分の商品を売買する方が増えてきたように思います。家にいながら何でも売れちゃうし、買えちゃう。インターネットって、すごい。

BASEもその一つで、香取慎吾さんやMattさんがCMに出ていらっしゃる有名なサービスです。

今回は、「WEB上に登録した商品一覧のCSVを修正して、BASEにNew商品一覧として登録する流れを効率化したい」といった内容のご依頼をいただきました。

Excelの相談に乗ります

コレクターズ・トイショップ様、ご依頼ありがとうございました！

本来ならリンクを貼ってご紹介させていただきたかったのですが、現在リニューアルオープン準備中とのことでしたのでお名前だけで失礼いたします。

リニューアルオープンに向けての業務効率化に、少しでもお役に立てたなら幸いです。応援しております！

ナレッジの共有

今回のご依頼で思ったのですが、CSVって若干ハードルが高い。気がする。

ネットサービスやアプリケーションに登録したデータを一括ダウンロードすると、意外とこのCSVというファイル形式で出てくることが多いです。

Comma Separated Value（＝カンマ（,）で区切った値）

そしてこのCSV、PCで開く時のデフォルト設定がExcelになっている割に

Excelで開くとたまに文字化けするんですよね。なんなの？

ネットで検索すると

「CSVはメモ帳やテキストで開く」

「Excelのパワークエリで文字コードを変換」

「Googleスプレッドシートで取り込むことができる」

など色々対処法が出てきます。うーん一回ならこれで良いんですが…もっと効率的に！と悩まれてる方は多いのかな、と感じています。

ということで今回のブログは、

・定期的にCSVの取込・出力をするため出来るところは効率化したい。

・仕組みを作っておけば、引継ぎしなきゃいけないときも楽ちん！

・マクロを作ってみたいけど、基礎をすっ飛ばして実技をやってみたい。

・ぶっちゃけ完成したコードだけ欲しい。

という方向けの記事を書きたいと思います。

よろしければ読んでやってください。

※Windowsユーザー向けです

※コードは一番下にあります

CSVをExcelに取り込むマクロを作りましょう。２STEPです。

STEP1：マクロの記録

Excelを開いて、マクロの記録ボタンを押します。

マクロの記録ボタンを押した後、CSVを取り込みます。

取り込んだ後、もう一回マクロの記録ボタンを押します。

STEP1終わり(*^▽^*)

…というのはさすがに雑過ぎるので、このあと図解を入れて詳しく説明します。STEP2へすぐ行きたい！という方はスクロールで飛ばしてください。

【STEP1詳細】

「マクロの記録」というのは、ユーザーの動きをそっくりそのまま記録してくれる便利な機能です。ボタンを押してから再度ボタンが押されるまでを「マクロ」という形で記録します。

では、まずはマクロの記録ボタンを押しましょう。画面下側に録画ボタンのようなものがありませんか？

見つからない場合は開発タブで「マクロの記録」ボタンを探してください。

そもそも開発タブがない場合は「開発タブ　出し方」でググりましょう。

ボタンをクリックしたら、記録したい動作を実際に行います。

今回はCSVの取り込みなので…

データタブ→テキストまたはCSVから　の順にクリックします。

上図と違う名前の場合は「テキストファイル」に近いものを押してください。

すると、下の図のようなデータの取り込み画面が出てきます。

取り込むCSVを一つ選んで、右下の「開く（またはインポート）」ボタンを押しましょう。するとデータが…

見事に文字化けしました( ;∀;)

そんな時は魔法の言葉「UTF-8」に助けてもらいましょう。

シフトJISで日本語部分が文字化けした場合は、大体UTF-8にしてあげればいい感じになります。(念のためCSVファイルの文字コードは確認してください)

ちゃんとデータが表示されたら、読み込みを押します。

これでExcelにCSVのデータがインポートされました！

マクロの記録ボタンを押して、記録を終了します。

STEP2：マクロの調整

さて、マクロの記録が無事にできました。が、前述の通りマクロはユーザーの動きをそっくりそのまま記録してくれる便利な機能です。ファイルの名前も、ファイルを置いたフォルダの名前もすべてそのまま記録されています。

次に取り込むCSVがまったく同じファイルの場合は問題ないのですが、そうでないことの方が多いですよね。

STEP2では、記録したマクロを汎用的にするための調整を行います。

前置きが長くなりますので、コードだけ知りたい方はスクロールで飛ばしてくださいね。

まず、マクロの中身を見てみましょう。

Excelを立ち上げたまま、AltキーとF11キーを同時に押します。

Alt＋F11で、マクロを作成するためのコードを記述する画面「VBA」が開かれます。玄人っぽい雰囲気を出せるので、このショートカットは覚えておいて損はないです。

記録したマクロは標準モジュールのModule1の中にあります。

クリックすると、右側に英数字の羅列がズラッと出てきました。

この羅列の中をちょっといじれば、ファイルの名前が変わっても取り込めるようになります。ではSTEP2、始めましょう！

コードの長さに心が折れそうになる方もいらっしゃるかもしれませんが、

ブロック分けして見てみましょう(-"-)

Sub Macro1()
'
'Macro1 Macro
'

ActiveWorkbook.Queries.Add Name:="test", Formula:= "let" & Chr(13) & "" & Chr(10) & " ソース = Csv.Document(File.Contents(""C:\Desktop\新しいフォルダー\test.csv""),[Delimiter="","", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & " 昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " 変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{ {""title1"", Int64.Type}, {""title2"", type text}, {""title3"", " & "type text}, {""title4"", type text}, {""title5"", type text} })" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & ""

ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test;Extended Properties=""""" , Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [test]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "test"
.Refresh BackgroundQuery:=False
End With