※本ブログのページには広告主との提携による広告や宣伝、プロモーションが含まれます。当ブログを経由しての商品の購入や、サービス申し込みが発生すると、それらの提携企業からの成果報酬を受けとる場合があります。

VBA|CSVをエクセルに読み込む・変換するマクロ

仕事や作業で使用するツールでデータを出力する機能をもっているものがあります。なかにはCSVファイルでデータを出力するものが多くみられ、エクセルに取り込むことや変換することで任意の管理方法に加工できたり、数値の分析などに利用できます。

こちらの記事では、CSVファイルをエクセルに読み込みたいとき、CSVファイルをエクセルファイルに変換したいとき、また逆にエクセルファイルをCSVファイルに変換するマクロのサンプルコードを紹介しながら解説していきます。

職場や作業でCSVファイルを取り扱うことがある人はぜひ参考にしてください。

CSVファイルをエクセルに読み込んだり、エクセルをCSVファイルにするマクロを紹介するよ。

PR

PCでスキルアップをしたい・Excelをしっかり学んで社内の評価を高めたい人は必見!
実務をプロから学べる「ユースフル」は講座の動画は永年見放題。安心のQ&A機能で分からないを解決。

CSVファイルについて

CSVファイルを取り込む、変換するマクロについて調べられている人のほとんどがCSVファイルについては既にご存知の上だと考えますので、CSVファイルについての説明は不要だと感じられる人は次の章から読みすすめてください。

CSVファイルとは、Comma-Separated Valuesのそれぞれの単語の頭文字で呼ばれるデータ形式のファイルです。テキストデータを項目ごとにカンマ(,)で区切ったものとなります。

パソコンなどに保存されているCSVファイルの拡張子は.csvです。

エクスプローラーで表示されたCSVファイル
CSVファイルをメモ帳アプリで開いたとき

データは気象庁のWebページよりダウンロードした大阪市の2023年の6月の天気や気温のデータです。

CSVファイルはデータがカンマで区切られたテキストだということがわかるね。

CSVファイルのデータをエクセルに読み込むマクロ

CSVファイルに書きこまれたデータをエクセルファイルに読み込むときにつかえるマクロです。
(※コピーして利用される場合は同じモジュールにペーストしてください。)

Option Explicit

Sub main()

Dim CSV_File As Object

'CSVファイルを取得プロシージャを呼び出す
Call CSVファイルを取得(CSV_File)

'ファンクションプロシージャ(ファイルを取得)の指定でNothingはないので保険的要素
If CSV_File Is Nothing Then
Exit Sub
Else
'イミディエイトウィンドウにファイル名を出力する
Debug.Print CSV_File.Name
End If

Call CSVファイルを開いてデータを取り込む(CSV_File)


End Sub

Function CSVファイルを取得(CSV_File)

'ファイルシステムオブジェクトの使用準備
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim CSV_FilePath

'CSVファイルが選択されるまでループ
Do While CSV_FilePath = False

'GetOpenFilenameメソッドを利用してファイル選択ダイアログを表示する
CSV_FilePath = Application.GetOpenFilename _
(Filefilter:="CSVファイル,*.csv", MultiSelect:=False, Title:="読み込みたいCSVファイルを選択")

'ダイアログでファイルが選択されなかった場合はエラーメッセージを出してループする
If CSV_FilePath = False Then
MsgBox "ファイルを選択してください。", vbCritical + vbOKOnly, "ファイル選択不正通知"
End If

Loop

'ファイルパスからファイルオブジェクトを取得する
Set CSV_File = FSO.GetFile(CSV_FilePath)

'取得したファイルを代入する
CSVファイルを取得 = CSV_File

End Function

Sub CSVファイルを開いてデータを取り込む(CSV_File As Object)

Dim FNum As Integer: FNum = FreeFile
Open CSV_File.Name For Input As #FNum

Dim Row As Long: Row = 0
Dim Col As Long
Dim TString As String
Dim TSplit() As String
Dim WSheet As Worksheet
Dim WSh As Integer

For Each WSheet In ThisWorkbook.Sheets
'シート名が重複しているかを判定
If WSheet.Name Like CSV_File.Name & "*" Then
WSh = WSh + 1 'シート名の末尾につける番号を加算する
End If

Next WSheet

If WSh <= 0 Then 'まだ同じシート名がなければCSVファイルの名前をシート名にする
Worksheets.Add after:=Worksheets(Worksheets.Count), Type:=xlWorksheet
ActiveSheet.Name = CSV_File.Name
Else '既に同じシート名があれば末尾に変数WShをつけたものをシートにする
Worksheets.Add after:=Worksheets(Worksheets.Count), Type:=xlWorksheet
ActiveSheet.Name = CSV_File.Name & WSh
End If

Do Until EOF(FNum)
Line Input #FNum, TString '1行ずつ読み込む
Row = Row + 1 '行数をカウントアップ

TSplit = Split(TString, ",") 'カンマで区切って配列にデータを代入する

For Col = 0 To UBound(TSplit) '配列の最大インデックスまでくり返す
TSplit(Col) = Replace(TSplit(Col), Chr(34), "") 'ダブルクォーテーション除去
ActiveSheet.Cells(Row, Col + 1) = TSplit(Col) 'セルに出力

Next Col
Loop

Close #FNum

End Sub

こちらのマクロは3つのプロシージャで構成しています。

1.Sub main()

他のプロシージャを呼び出すマクロ

2.Function CSVファイルを取得(CSV_File)

CSVファイルを取得するためのファンクションプロシージャ

3.Sub CSVファイルを開いてデータを取り込む(CSV_File As Object)

CSVファイルを開いてデータをエクセルファイルに取り込む

1.他のプロシージャを呼び出す

CSVファイルを受け取る変数を引数にしてファンクションプロシージャを呼び出します。ファンクションプロシージャで処理した結果を変数「CSV_File」で受け取ります。

呼び出したファンクションプロシージャの中でCSVファイルが取得できなかった場合はただちにマクロを終了する条件分岐を指定しています。

CSVファイルを取得でえたファイルを引数にして、CSVファイルを開いてデータを取り込むプロシージャを呼び出します。

ファンクションプロシージャの使い方はこちらの記事で紹介しているよ。

2.CSVファイルを取得するためのファンクションプロシージャ

ファイルをあつかうときに便利なメソッドやプロパティをつかうために、ファイルシステムオブジェクトの使用ができるようにしています。

ファイルやフォルダの操作につかえるオブジェクトで便利なメソッドやプロパティが備わっています。ファイルシステムオブジェクトを詳しく解説した記事は以下をクリックすることで読むことができます。

エクセルに変換したいCSVファイルを選択するためのダイヤログを表示します。このメソッドの引数で他の種類のファイルを選択できないようにCSVファイルだけを表示するように指定します。

CSVファイルを選択するダイヤログで何も選択せずに[キャンセル]や右上の[×]をクリックされる恐れがあるため、CSVファイルが選択されるまでダイヤログを表示し続けます。選択されたCSVファイルのファイルパスは事前に宣言している変数「CSV_FilePath」に代入されます。

ファイルシステムオブジェクトのGetFileメソッドの引数にCSVファイルのファイルパスを指定することで変数「CSV_File」にCSVファイルを代入します。

3.CSVファイルを開いてデータをエクセルファイルに取り込む

こちらのプロシージャでは、CSVファイルのデータを読み込み、エクセルファイルに新しくシートを追加して書きこんでいきます。

Openステートメントを利用してファイルを開きます。引数のCSV_File.Nameにはダイヤログで選択したCSVファイルの名前が指定されているほか、For Inputでは読み込みモードを指定、As #FNumではファイル番号を指定しています。

新しく作成するシートの名前はCSVファイルの名前にします。しかし、既に同じシート名があった場合はエラーが発生しますので、ワイルドカード文字をつかってCSVファイルの名前と同じ名前のシートが存在しないかを判定しています。

既に同じ名前のシートが存在した場合は、変数「WSh」に1を加算します。

シート名の重複判定でCSVファイルと同じ名前のシートは存在しないときとそうでないときの条件分岐をしています。シート名が重複していた場合、内側の処理で末尾に変数「WSh」に代入された数値をつけることでシート名の重複を回避しています。

例えば、data.csvシートが既に存在していた場合は、新たに作成されるシート名がdata.csv1となり、data.csv1も存在していた場合は、新たに作成されるシート名はdata.csv2といったように末尾の番号が加算されていきます。

EOF関数の引数としてファイル番号が入った変数「FNum」を指定します。EOF関数は、指定したファイルの内容を読み込み、末尾に達したときにTrueを含む整数を返します。つまりCSVファイルの末尾になるまで処理を繰り返すように指定しています。

Line Input #ステートメントは、CSVファイルに記入されているデータを1行ずつ読み込み、その行に入力されている内容を文字列型変数の「TString」に代入していきます。

Split関数をつかってカンマで区切られた文字列を分割しています。例えば、テキストデータの1行目が「2023/6/1,曇,27℃,16℃」であれば、「2023/6/1」、「曇」、「27℃」、「16℃」といった4つの配列データに変換します。

Split関数で行の文字列を配列データの分割し、配列変数であるTSplitに代入します。

CSVから取り込んだ行に書かれた内容ををカンマで分割したときのイメージ

CSVファイルの行2023/6/1,曇,27℃,16℃…
配列データ2023/6/127℃16℃
Split関数で行の内容をカンマごとに分割し、データを配列に代入する

Replace関数をつかってCSVデータに含まれるダブルクォーテーションを削除しています。

※Chr関数はASCIIコードの対応した文字列を返します。Chr(34)はダブルクォーテーション(”)となります。

配列データのTSplitの要素を1つずつ、シートに貼り付けていく処理です。

CSVデータを1行ずつ取り込んで、シートにペーストする処理をくり返しているんだね。

CSVをエクセルに変換するマクロ

CSVをエクセルファイルに変換するときにつかえるマクロです。

Option Explicit

Sub CSVをエクセルファイルに変換するマクロ()

'ファイルシステムオブジェクトの使用準備
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

'CSVファイルの格納場所を受けとる変数
Dim CSV_FilePath As Variant

'CSVファイルをオブジェクトとして受けとる変数
Dim CSV_File As Object

'CSVファイルを変換したエクセルファイルの保存先を受けとる変数
Dim NFile_FilePath As String

'CSVファイルが選択されるまでループ
Do While CSV_FilePath = False

'GetOpenFilenameメソッドを利用してファイル選択ダイアログを表示する
CSV_FilePath = Application.GetOpenFilename _
(Filefilter:="CSVファイル,*.csv", MultiSelect:=False, Title:="読み込みたいCSVファイルを選択")

'ダイアログでファイルが選択されなかった場合はエラーメッセージを出してループする
If CSV_FilePath = False Then
MsgBox "ファイルを選択してください。", vbCritical + vbOKOnly, "ファイル選択不正通知"
End If

Loop

'ダイヤログで選択されたCSVファイルを変数に代入する
Set CSV_File = FSO.GetFile(CSV_FilePath)

'変換したエクセルファイルを保存するファイルパスはCSVファイルと同じフォルダ
NFile_FilePath = CSV_File.ParentFolder

'WorkbooksオブジェクトのOpenメソッドでCSVファイルを開く
Workbooks.Open CSV_FilePath

'既に同じ名前のファイルがあり、上書き保存がキャンセルされた場合のエラー処理
On Error GoTo 上書き保存キャンセル

'開いたCSVファイルをエクセル(.xlsx)で保存する
ActiveWorkbook.SaveAs NFile_FilePath & "\" & _
FSO.GetBaseName(CSV_FilePath) & ".xlsx", xlOpenXMLWorkbook

ActiveWorkbook.Close 'ブックを閉じる

Exit Sub

'上書き保存にいいえやキャンセルなら以下の処理を実行する
上書き保存キャンセル:
MsgBox "上書き保存がキャンセルされました。", vbOKOnly + vbCritical, "保存キャンセル通知"
ActiveWorkbook.Close 'ブックを閉じる

End Sub

マクロで処理している内容の大きな流れは以下のとおりです。

  1. ファイルシステムオブジェクトを使用するための準備と必要となる変数の宣言
  2. エクセルに変換するCSVファイルを選択する
  3. CSVファイルを開いて、エクセルファイルとして保存する

それぞれについて詳しくみていきましょう。

1.ファイルシステムオブジェクトを使用するための準備と必要となる変数の宣言

ファイルをあつかうときに便利なメソッドやプロパティをつかうために、ファイルシステムオブジェクトの使用ができるようにしています。

ファイルやフォルダの操作につかえるオブジェクトで便利なメソッドやプロパティが備わっています。ファイルシステムオブジェクトを詳しく解説した記事は以下をクリックすることで読むことができます。

2.エクセルに変換するCSVファイルを選択する

エクセルに変換したいCSVファイルを選択するためのダイヤログを表示します。このメソッドの引数で他の種類のファイルを選択できないようにCSVファイルだけを表示するように指定します。

CSVファイルを選択するダイヤログで何も選択せずに[キャンセル]や右上の[×]をクリックされる恐れがあるため、CSVファイルが選択されるまでダイヤログを表示し続けます。選択されたCSVファイルのファイルパスは事前に宣言している変数「CSV_FilePath」に代入されます。

3.CSVファイルを開いて、エクセルファイルとして保存する

WorkbooksオブジェクトのOpenメソッドによって選択されたCSVファイルを開きます。

CSVを開いたら、Workbook.SaveAsメソッド(サンプルコードではActiveWorkbook)をつかってCSVファイルをエクセルファイルとして保存します。
Workbook.SaveAsメソッドの引数で、新たに保存するエクセルファイルの保存場所は、CSVファイルと同じフォルダの中、ファイルの名前はCSVファイルと同じ名前のエクセルファイルとして保存することを指定しています。

data.csvファイルと同じフォルダの中にdata.xlsxファイルが保存されました。

CSVファイルをエクセルファイルに変換できたね。

エクセルをCSVファイルに変換するマクロ

これまでのマクロではCSVファイルのデータをエクセルのシートに取り込むことや、CSVファイルをエクセルに変換するマクロを見てきましたが、今度はその逆でエクセルをCSVファイルに変換するマクロを紹介します。

Option Explicit

Sub エクセルファイルのデータをCSVファイルに出力()

'最終行を代入する変数に選択したシートの最終行を代入する
Dim Flag As Variant 'メッセージボックスのYes/Noを受けとる変数
Dim ShData As Variant 'シートのデータを配列として代入する変数
Dim i As Long, j As Long, k As Long 'くり返し用変数
Dim Sh As Object 'シートを代入する変数

Set Sh = ThisWorkbook.Sheets("202306_data.csv") 'CSVファイルにするシートを選択する
Flag = MsgBox(Sh.Name & " シートをCSVファイルとして出力しますか?", vbYesNo, "シートの選択")
If Flag = vbYes Then '選択肢にYes
MsgBox "処理を開始します。", vbOKOnly + vbInformation, "処理開始通知"

'シートの最終行を代入する変数
Dim LastR As Long: LastR = Sh.Cells(Rows.Count, 1).End(xlUp).Row

'シートの最終列を代入する変数
Dim LastC As Long

'行数分くり返す
For i = 1 To LastR
If i = 1 Then
'最終列を代入する
LastC = Sh.Cells(i, Columns.Count).End(xlToLeft).Column
ElseIf i > 1 Then
'最終列が今までの行より大きいなら最終列を更新する
If LastC < Sh.Cells(i, Columns.Count).End(xlToLeft).Column Then
LastC = Sh.Cells(i, Columns.Count).End(xlToLeft).Column

'セル範囲から配列データに変換する
ShData = Sh.Range(Cells(1, 1), Cells(LastR, LastC))

'CSVファイルの格納場所はエクセルと同じフォルダの中でファイル名は"シート名.csv"
Dim CSVF As String: CSVF = ActiveWorkbook.Path & "\" & Sh.Name & ".csv"
Dim CSVFN As Integer: CSVFN = FreeFile 'ファイル番号を取得する
Open CSVF For Output As #CSVFN 'Openステートメントでファイルを開く

'CSVファイルへの書きこみ処理
For k = 1 To UBound(ShData, 1) '配列データの一次元の最終インデックスまでくり返す
For j = 1 To UBound(ShData, 2) '配列データのニ次元の最終インデックスまでくり返す
If j <> UBound(ShData, 2) Then 'ニ次元のさいごでないかの判定
Print #CSVFN, ShData(k, j) & ","; 'カンマ
Else
Print #CSVFN, ShData(k, j) & vbCrLf; '改行
End If
Next j
Next k

'ファイルを閉じる
Close #CSVFN

'完了メッセージの表示
MsgBox "処理が完了しました。", vbOKOnly + vbInformation, "処理完了通知"

End If
End If
Next i
Else
'選択肢にNo
MsgBox "Noが選択されました。" & vbCrLf & "処理を終了します。", vbCritical + vbOKOnly, "キャンセル通知"

End If

End Sub
コードをコピーして利用する場合は、Set Sh = ThisWorkbook.Sheets(“data.csv”)のdata.csvの部分をCSVにしたいシートの名前に変更してください。

マクロで処理している内容の大きな流れは以下のとおりです。

  1. 必要となる変数の宣言
  2. エクセルのデータを配列に取り込む
  3. エクセルから取り込んだデータCSVファイルに変換する

それぞれの処理でポイントとなる部分を詳しく解説します。

1.必要となる変数の宣言

こちらのマクロでは、エクセルのデータを二次元配列として読み込みます。指定したセル範囲を取り込むための変数として「ShData」をバリアント型で宣言しています。

2.エクセルのデータを配列に取り込む

エクセルにあるデータの最終列を変数「LastC」に代入する処理です。1行目から最終行まで上から順に列数を判定していき、今までに取得した列数よりも大きい数値であった場合は変数「LastC」を更新しています。

例えば、エクセルのシートに以下の表と同じようなデータであった場合において、最も多い列をつかっている行は2、6行目となります。

CSVファイルに変換したいエクセルのシートのデータ

A B C D E F
1 A1セルの値 B1セルの値 C1セルの値 D1セルの値 E1セルの値
2 A2セルの値 B2セルの値 C2セルの値 D2セルの値 E2セルの値 F2セルの値
3 A3セルの値 B3セルの値 C3セルの値
4 A4セルの値 B4セルの値 C4セルの値 D4セルの値
5 A5セルの値
6 A6セルの値 B6セルの値 C6セルの値 D6セルの値 E6セルの値 F6セルの値

指定したエクセルのシートで1行目がE列まで使われていた場合、変数「LastC」には5が入ります。そのあと、2行目のデータはF列まであるので1行目で得た列数と比較した結果、変数「LastC」を6に書きかえます。この処理をくり返す結果により、最も多くの列数を使っている行の数値を取得しています。

読み込みたいシートにあるデータのセル範囲を指定しています。取得する範囲はA1セルからデータの入力された最終行と最終列までで、この範囲にある二次元データを変数「ShData」に代入しています。

3.エクセルから取り込んだデータをCSVファイルに変換する

CSVファイルは読み込みたいエクセルファイルと同じフォルダに作成します。ActiveWorkbook.Pathによりエクセルファイルの格納場所を取得してします。作成したCSVファイルの名前はエクセルのシート名になります。

FreeFile関数をつかってファイル番号を取得しています。FreeFile関数は1から511の範囲の数値を返すため、返ってきた数値を変数「CSVFN」に代入しています。

Openステートメントをつかってファイルを開きます。引数で出力(書きこみ)モードとファイル番号を指定しています。

変数「j」に代入されている数値が配列「ShData」の二次元の最終インデックスであるかの判定をしています。変数「j」にはくり返し回数が代入されていきますが、判定の結果がTrueとFalseのときで処理を分岐しています。

Print #ステートメントで指定したデータをCSVファイルに書きこむ処理です。変数「j」が配列「ShData」の二次元の最終インデックスでない場合の処理です。つまり、くり返し回数がその行の最終列まで達していない場合は、文字の末尾にカンマ(,)を書きこみます。

Print #ステートメントで指定したデータをCSVファイルに書きこむ処理です。変数「j」が配列「ShData」の二次元の最終インデックスであった場合の処理です。つまり、くり返し回数がその行の最終列まで達した場合は、文字の末尾に改行コード(vbCrLf)を挿入します。

マクロを起動したエクセルファイルと同じフォルダの中にCSVファイルが作成されました。

※マクロで指定したシート名が202306_data.csvなので、ファイルが202306_data.csv.csvとなっています。

変換されたCSVファイルの内容(メモ帳アプリで起動)

ダウンロードした時刻:2024/06/07 17:42:00,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,
,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪
年月日,天気概況(昼:06時~18時),天気概況(昼:06時~18時),天気概況(昼:06時~18時),天気概況(夜:18時~翌日06時),天気概況(夜:18時~翌日06時),天気概況(夜:18時~翌日06時),平均気温(℃),平均気温(℃),平均気温(℃),最高気温(℃),最高気温(℃),最高気温(℃),最低気温(℃),最低気温(℃),最低気温(℃)
,,,,,,,,,,,,,,,
,,品質情報,均質番号,,品質情報,均質番号,,品質情報,均質番号,,品質情報,均質番号,,品質情報,均質番号
2023/06/01,曇,8,1,雨一時曇,8,1,21.4,8,1,27.2,8,1,16.4,8,1
2023/06/02,大雨,8,1,雨後曇,8,1,21.1,8,1,22,8,1,20.5,8,1
2023/06/03,晴,8,1,晴,8,1,21.7,8,1,26.6,8,1,17.9,8,1
2023/06/04,晴後曇,8,1,曇時々晴,8,1,21.6,8,1,26.9,8,1,15.3,8,1
2023/06/05,曇,8,1,曇時々晴,8,1,23.2,8,1,27.6,8,1,18.7,8,1
2023/06/06,曇後雨,8,1,雨後曇,8,1,19.8,8,1,23.6,8,1,16.9,8,1
2023/06/07,曇一時晴,8,1,薄曇一時晴,8,1,21.9,8,1,26.8,8,1,16.7,8,1
2023/06/08,曇時々雨,8,1,大雨後時々曇,8,1,21.3,8,1,25.2,8,1,18.6,8,1
2023/06/09,曇,8,1,曇一時晴,8,1,22.4,8,1,26,8,1,18.7,8,1
2023/06/10,曇,8,1,曇後雨,8,1,23.1,8,1,26.3,8,1,19.9,8,1
2023/06/11,雨後曇,8,1,曇時々雨,8,1,22.2,8,1,25.4,8,1,20.3,8,1
2023/06/12,曇時々雨,8,1,曇一時雨,8,1,22.8,8,1,24.8,8,1,21.2,8,1
2023/06/13,曇,8,1,曇後雨,8,1,24.6,8,1,28.6,8,1,21.3,8,1
2023/06/14,雨時々曇,8,1,曇一時雨,8,1,23.9,8,1,27.8,8,1,22,8,1
2023/06/15,雨時々曇,8,1,晴,8,1,23.8,8,1,26.3,8,1,22.2,8,1
2023/06/16,晴後一時曇,8,1,晴,8,1,23.9,8,1,27.8,8,1,20.4,8,1
2023/06/17,晴,8,1,薄曇時々晴,8,1,25.6,8,1,31.3,8,1,20.1,8,1
2023/06/18,曇後一時晴,8,1,晴時々曇,8,1,24.9,8,1,28.5,8,1,21.9,8,1
2023/06/19,晴,8,1,晴,8,1,25.4,8,1,31.2,8,1,21,8,1
2023/06/20,曇時々晴,8,1,晴時々曇,8,1,24.6,8,1,30.4,8,1,19.6,8,1
2023/06/21,曇後時々雨,8,1,曇後雨,8,1,24.8,8,1,29,8,1,22,8,1
2023/06/22,大雨後曇一時晴,8,1,雨一時晴,8,1,21.5,8,1,24.9,8,1,18.4,8,1
2023/06/23,曇,8,1,曇,8,1,23.4,8,1,27.2,8,1,19.5,8,1
2023/06/24,曇,8,1,曇,8,1,25.2,8,1,29.2,8,1,22.9,8,1
2023/06/25,薄曇,8,1,曇,8,1,25.2,8,1,29.7,8,1,21.5,8,1
2023/06/26,雨時々曇,8,1,曇時々雨,8,1,25.1,8,1,27.5,8,1,23.5,8,1
2023/06/27,薄曇後一時晴,8,1,曇時々晴,8,1,27,8,1,31.8,8,1,23.2,8,1
2023/06/28,曇後時々雨、雷を伴う,8,1,晴時々曇,8,1,26.6,8,1,31.5,8,1,24.3,8,1
2023/06/29,晴一時曇,8,1,曇一時雨,8,1,28,8,1,33.3,8,1,24.4,8,1
2023/06/30,雨時々曇,8,1,大雨,8,1,26.8,8,1,29.9,8,1,23.6,8,1

エクセルをCSVファイルに変換できたね。

エクセルをCSVファイルに変換するマクロ(ADO)

さきほどと同じくエクセルをCSVファイルに変換するマクロを紹介します。ただし、こちらではADOを利用してエクセルに書かれたデータをCSVファイルに変換していきます。

ADOとは、ActiveX Data Objectsの頭文字をとったもので、SQL文でデータの入力、削除などの基本操作を利用可能にするツールのことです。

ADOはデータベースを操作できる外部ライブラリだよ。

こちらのマクロを実行するためには事前に以下の手順を実施しておいてください。


① VBEの画面で[ツール]→[参照設定]を選択する。

② “Microsoft ActiveX Data Objects x.x Library“にチェックをつける。

※x.xバージョン名なので最新のものを選択してください。

③ [OK]をクリックする

画像では「Microsoft ActiveX Data Objects 6.1 Library」を利用しています。
Option Explicit

'ADODB.Streamを使用するマクロ
'ツール→参照設定でMicrosoft ActiveX Data Objects x.x Libraryにチェックしてください。
'x.xバージョン名なので最新のものを選択してください。

Sub エクセルファイルのデータをCSVファイルに出力2()

'ADODB.Streamの使用するための準備
Dim ADO As Object
Set ADO = CreateObject("ADODB.Stream")

'変数を宣言する
Dim i As Long, j As Long
Dim LastR As Long, LastC As Long

'開いているエクセルファイルのパスを取得
Dim FPath As String: FPath = ThisWorkbook.Path

'開いているシート名を取得
Dim SFN As String: SFN = ActiveSheet.Name

'処理を開始するかどうかの返答を受けとる
Dim Flag As String
Flag = MsgBox(SFN & " シートをCSVファイルにしますか?", vbYesNo, "処理開始前通知")

If Flag = vbYes Then

With Sheets(SFN)
'シートで使用している最終行と最終列の取得
LastR = .UsedRange.Rows.Count
LastC = .UsedRange.Columns.Count

'ファイルを開く
ADO.Open

For i = 1 To LastR '最終行までくり返す
For j = 1 To LastC '最終列までくり返す

'最終列ではない間は文字列のあとにカンマ(,)、最終列であれば改行
If j < LastC Then
ADO.WriteText .Cells(i, j) & ","
ElseIf j = LastC Then
ADO.WriteText .Cells(i, j) & vbCrLf
End If

Next j
Next i

'ファイルを保存する(ファイル名はエクセルのシート名,既存ファイルがあれば上書きする)
ADO.SaveToFile FPath & "\" & SFN & ".csv", 2

'ファイルを閉じる
ADO.Close

'完了メッセージを表示する
MsgBox "CSVファイルの作成が完了しました。", vbInformation, "処理完了通知"

End With

ElseIf Flag = vbNo Then

'キャンセルメッセージを表示する
MsgBox "処理がキャンセルされました。", vbInformation, "処理中断通知"
End If

End Sub
コードをコピーして利用すると、開いているシートがCSVファイルに変換されます。

マクロで処理している内容の大きな流れは以下のとおりです。

  1. 必要となる変数の宣言とADOの利用準備
  2. エクセルのデータの最終行と最終列の取得
  3. ADOを利用してエクセルデータをCSVファイルに変換する

それぞれの処理でポイントとなる部分を詳しく解説します。

1.必要となる変数の宣言とADOの利用準備

ADOを利用するためのコードです。ADOにはたくさんのメソッドやプロパティがありますが、このマクロでは、ファイルを開く・閉じる、保存、テキストデータを書きこむ処理に必要なメソッドを使うための準備となります。

2.エクセルのデータの最終行と最終列の取得

WorksheetオブジェクトのUsedRangeプロパティでエクセルで使用されているさいごの行を取得しています。

WorksheetオブジェクトのUsedRangeプロパティでエクセルで使用されているさいごの列を取得しています。

3.ADOを利用してエクセルデータをCSVファイルに変換する

ADOのOpenメソッドでファイルを開きます。

ADOのWriteTextメソッドでエクセルシートのセルに書かれた値をファイルに書きこみます。また直前の条件分岐の指定により、入力した値がさいごの列のものでなければ、値のあとにカンマを挿入します。

※このコードはWithステートメントの内側にあるため、WriteTextメソッドのCellsの前にドット(.)を書くことを忘れないように注意してください。

ADOのWriteTextメソッドでエクセルシートのセルに書かれた値をファイルに書きこみます。また直前の条件分岐の指定により、入力した値がさいごの列のものであれば、値のあとに改行を挿入します。

※このコードはWithステートメントの内側にあるため、WriteTextメソッドのCellsの前にドット(.)を書くことを忘れないように注意してください。

さいごの列の値を書きこんだら改行するんだね。

ADOのSaveToFileメソッドでファイルを保存します。

引数では変数「FPath」と「SFN」をつかってフルパスを指定しています。具体的に言えば、このマクロがあるエクセルファイルと同じフォルダに変換したシート名と同じ名前のCSVファイルが作成されます。

ADOのCloseメソッドでファイルを閉じます。

マクロを起動したエクセルファイルと同じフォルダの中にCSVファイルが作成されました。

※開いていたシート名が202307_data.csvなので、ファイルが202307_data.csv.csvとなっています。

変換されたCSVファイルの内容(メモ帳アプリで起動)

ダウンロードした時刻:2024/06/13 09:28:44,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,
,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪,大阪
年月日,天気概況(昼:06時~18時),天気概況(昼:06時~18時),天気概況(昼:06時~18時),天気概況(夜:18時~翌日06時),天気概況(夜:18時~翌日06時),天気概況(夜:18時~翌日06時),平均気温(℃),平均気温(℃),平均気温(℃),最高気温(℃),最高気温(℃),最高気温(℃),最低気温(℃),最低気温(℃),最低気温(℃)
,,,,,,,,,,,,,,,
,,品質情報,均質番号,,品質情報,均質番号,,品質情報,均質番号,,品質情報,均質番号,,品質情報,均質番号
2023/07/01,雨一時曇,8,1,曇一時雨,8,1,24.3,8,1,25.6,8,1,22.8,8,1
2023/07/02,晴時々曇,8,1,晴時々薄曇,8,1,27.3,8,1,32.4,8,1,23.7,8,1
2023/07/03,曇一時晴,8,1,曇後晴,8,1,27.5,8,1,32.2,8,1,24.3,8,1
2023/07/04,晴後薄曇,8,1,曇,8,1,28.1,8,1,34.4,8,1,23.5,8,1
2023/07/05,曇後雨,8,1,雨後曇,8,1,25.3,8,1,30.3,8,1,23.1,8,1
2023/07/06,晴後一時薄曇,8,1,晴一時薄曇,8,1,28.1,8,1,33,8,1,23.5,8,1
2023/07/07,曇一時晴後一時雨,8,1,曇一時雨,8,1,28.1,8,1,33.8,8,1,24.1,8,1
2023/07/08,曇,8,1,曇後雨,8,1,29.7,8,1,32.7,8,1,27.7,8,1
2023/07/09,曇時々雨,8,1,曇,8,1,27.1,8,1,30.4,8,1,24.6,8,1
2023/07/10,曇後晴一時雨,8,1,晴,8,1,27.4,8,1,32.3,8,1,24.1,8,1
2023/07/11,晴,8,1,晴,8,1,28.9,8,1,34,8,1,25,8,1
2023/07/12,曇時々晴,8,1,曇,8,1,29,8,1,33.3,8,1,26.4,8,1
2023/07/13,曇後雨、雷を伴う,8,1,曇一時雨,8,1,27.4,8,1,30.7,8,1,24.8,8,1
2023/07/14,曇,8,1,曇,8,1,28.2,8,1,32.3,8,1,24.5,8,1
2023/07/15,曇後晴,8,1,晴時々曇,8,1,30.4,8,1,33.9,8,1,28.1,8,1
2023/07/16,晴,8,1,快晴,8,1,30.4,8,1,34.2,8,1,28,8,1
2023/07/17,晴,8,1,快晴,8,1,30.3,8,1,35.5,8,1,26.8,8,1
2023/07/18,薄曇時々晴,8,1,晴,8,1,31.1,8,1,35.8,8,1,26.9,8,1
2023/07/19,曇一時雨,8,1,曇時々雨後晴、雷を伴う,8,1,29,8,1,34.1,8,1,23.3,8,1
2023/07/20,晴,8,1,晴,8,1,27.9,8,1,34,8,1,22.9,8,1
2023/07/21,晴,8,1,晴,8,1,28.8,8,1,34.8,8,1,23.8,8,1
2023/07/22,晴,8,1,晴,8,1,28.9,8,1,35.1,8,1,25.7,8,1
2023/07/23,晴後一時曇,8,1,曇後晴,8,1,29.2,8,1,34.4,8,1,26.4,8,1
2023/07/24,晴,8,1,晴後一時曇,8,1,29.4,8,1,34.2,8,1,25.8,8,1
2023/07/25,晴一時曇,8,1,晴,8,1,30.1,8,1,34.8,8,1,27.3,8,1
2023/07/26,晴,8,1,晴,8,1,31.1,8,1,36.1,8,1,27.5,8,1
2023/07/27,晴後曇,8,1,晴一時雨、雷を伴う,8,1,31.4,8,1,38.1,8,1,27.8,8,1
2023/07/28,晴、雷を伴う,8,1,晴時々曇,8,1,31.1,8,1,37.4,8,1,28,8,1
2023/07/29,晴,8,1,晴後一時薄曇,8,1,30.2,8,1,34.8,8,1,27.7,8,1
2023/07/30,薄曇時々晴,8,1,曇時々晴,8,1,30.3,8,1,35.7,8,1,27.4,8,1
2023/07/31,曇,8,1,曇,8,1,30.3,8,1,35,8,1,27.9,8,1

ADOをつかってもエクセルをCSVファイルに変換できたね。

PR

まとめ

こちらの記事では、CSVファイルをエクセルに取りこむ方法や、変換する方法。また、その逆のエクセルからCSVファイルに変換するマクロを紹介しました。

CSVファイルは職場や作業などで利用しているシステムで出力(エクスポート)できるものも見かけます。日常の作業で蓄積されたデータを使って、分析資料や報告書を作成する場面や、エクセルにあるデータをまとめてCSVに変換してシステムに入力(インポート)する場面で活躍できると思いますので、これらの場面で使えるマクロが作れないかなと考えている人はぜひ参考にしてください。

CSVからエクセル、エクセルからCSVはマクロで処理してしまおうね。

コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。

コメント

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