ExcelマクロでCSVファイルを読み込んでみた

Excelマクロとは

みなさんこんにちは、こんばんは、おはようございます。
SE作業で日々脳汁を垂れ流しているイガラシです。

みなさんはExcelマクロを使ってますか？

僕は使ってます。

親の顔を忘れるくらいVBエディタを見て、パチパチとコードを打ち込んでおります。

あまりにもVBエディタを見すぎたせいか、親の顔が再生ボタンにしか見えません。。。。

Excelマクロとは何かと申しますと、Excelでの作業を自動化するためのプログラミングコードになります。

このマクロを利用すると、あら不思議、ボタン１つで、大量のファイルをまとめたり、データを読み取って、資料を作ったり、どんな夢でも叶えてくれる、夢のようなシステムなのです。。。限度はありますけどね。

そんなExcelマクロで、今まで僕が作った中からこれは「便利だな～」というものを紹介したいと思います。

CSVファイルを読込むマクロ

CSVファイルとは、カンマと呼ばれる記号「,」で区切られたテキストファイルの事を言います。
お客様とのデータのやり取りや、データベースのデータを抽出した時によくみるファイルです。
この業界で働いていれば、そりゃあもう、親の顔をわｓ

このCSVファイルは、Excelをインストールしたパソコンでしたら、特別に設定をしない限り、Excelで開かれるようになっております。
Excelがインストールされていないパソコンでしたら、メモ帳などのテキストファイルを開くアプリで表示されるものです。

Excelで自動で開かれるんだったら、何もマクロにする必要ってある？
そう思う方もいらっしゃると思います。

たしかにCSVファイルを開くだけならば、何もマクロで行う必要はありません。

ただ、Excelは、とっても気遣いができ、思いやりのある子なので、CSVファイルを中身を判断して、データの形式(データの種類)を自動的に変えてしまう性格なのです。

優しい！

うんうん、数字を見て数値形式と判断したんだよね。
わかるわかる。だから前にあるたくさんのゼロを消しちゃったんだよね。

それ、商品コードですからーー！やめて！

優しさが重いッ

なので、マクロを１つ作っておくと後々便利になるわけです。
以下にコードを記載しますね。

～Excelマクロ　はじまり～

Public Sub ImportCsv()

    Dim App As Application
    Dim WrkBook As Workbook
    Dim WrkSheet As Worksheet

    Dim MaxRow As Double
    Dim MaxClm As Double
    Dim RangeStr As String
    
    Dim ReadFilePath As String
    Dim ReadFileName As String
    
    Set App = Application
    Set WrkBook = App.Workbooks(ThisWorkbook.Name)
    WrkBook.Activate

    Set WrkSheet = WrkBook.ActiveSheet
    WrkSheet.Select

    ReadFileName = ""
    CreateObject("WScript.Shell").CurrentDirectory = App.ThisWorkbook.Path
    ReadFilePath = App.GetOpenFilename("csvファイル,*.csv")
    If ReadFilePath = "False" Then
        ReadFileName = ""
        Exit Sub
    End If
    ReadFileName = Dir(ReadFilePath)
    ReadFilePath = Replace(ReadFilePath, ReadFileName, "")

    App.Cells.Select
    App.Selection.Delete Shift:=xlUp
    
    FileImport App, WrkSheet, ReadFilePath, ReadFileName, "A1"

    MaxClm = App.Cells(2, Columns.Count).End(xlToLeft).Column
    RangeStr = "A1:" & App.Cells(1, MaxClm).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    LineSet App, WrkSheet, RangeStr
    ColorSet App, WrkSheet, RangeStr, RGB(169, 208, 142)
    
    App.Cells.EntireColumn.AutoFit
    App.Range("A1").Select
    
    Set App = Nothing
    Set WrkBook = Nothing
    Set WrkSheet = Nothing
    
End Sub

Private Function FileImport(getApp As Application, getWrkSheet As Worksheet, getFilePath As String, getFileName As String, getSetRange As String)
'外部ファイルのインポート処理
    
    'ADOオブジェクト変数
    Dim AdoCnt As New ADODB.Connection
    Dim AdoRst As New ADODB.Recordset
    Dim StrSqlCmd As String
    
    Set AdoCnt = New ADODB.Connection
    With AdoCnt
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "text;FMT=Delimited;HDR=NO;"
        .Open getFilePath
    End With

    Set AdoRst = New ADODB.Recordset
    
    StrSqlCmd = "SELECT * FROM [" & getFileName & "];"
    
    AdoRst.Open StrSqlCmd, AdoCnt, adOpenKeyset, adLockReadOnly
    If AdoRst.RecordCount > 0 Then AdoRst.MoveFirst
    
    '指定したセルに選択されたデータを貼付
    getWrkSheet.Select
    getApp.Range(getSetRange).CopyFromRecordset AdoRst
    RowEnd = RowSta + AdoRst.RecordCount
    AdoRst.Close
    
    Set AdoRst = Nothing
    Set AdoCnt = Nothing

End Function

Private Function LineSet(getApp As Application, getWrkSheet As Worksheet, getSetRange As String)
'エクセルセルに格子罫線を設定

    getWrkSheet.Select
    getApp.Range(getSetRange).Borders.LineStyle = False
    getApp.Range(getSetRange).Borders.LineStyle = True

End Function

Private Function ColorSet(getApp As Application, getWrkSheet As Worksheet, getSetRange As String, getSetColorNum As Double)
'エクセルセルへの色付け

    getWrkSheet.Select
    With getApp.Range(getSetRange).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = getSetColorNum
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Function

～Excelマクロ　おわり～

このマクロを実行するためには、「Microsoft ActiveX Data Objects *.* Library」の参照が必要になります。

参照方法は以下の通りです。

Excelの画面に「開発」リボンが無い場合、画面のリボンの所で右クリックし、「リボンのユーザー設定」をクリックします。

「開発」にチェックします。OKをクリックします。

「開発」リボンが表示されたら、メニュー内の「Visual Basic」をクリックします。

「ツール」→「参照設定」をクリックします。

参照可能なライブラリファイルより、「Microsoft ActiveX Data Objects *.* Library」、ここでは、「Microsoft ActiveX Data Objects 6.1 Library」を選択します。
OKをクリックして適用してください。

「挿入」→「標準モジュール」をクリックし、モジュールの作成を行います。

標準モジュールが作られますので、何も記載されていないところに、上記にあるマクロのコードをコピーして貼り付けてください。

コード貼り付け。

コードを貼り付けたExcelファイルを保存するときは、拡張子を「xlsm」にして保存してください。

このマクロで読込むためのCSVファイルを用意します。

TestData.csv
 "商品コード","商品名"
 "0000000001","洗濯洗剤"
 "0000000002","シャンプー"
 "0000000003","リンス

１行目が項目名を表し、２行目以降がデータになります。
全てダブルクォーテーション「”」で囲まれたデータになります。

今回のマクロは、全てダブルクォーテーション「”」で囲まれたデータでないと上手く取り込めないので、ご注意ください。

Excelファイルを保存したら、Excelシートがある画面を表示し、「開発」→「マクロ」をクリックします。

コピーしたコードの中にある「ImportCsv」というマクロが表示されますので、それを選択し、実行をクリックします。

ファイルを選択するウィンドウが表示されますので、用意したCSVファイルを選択します。

無事、CSVファイルの内容を読込むことができました。

最後に

このようにExcelマクロは、利用するととても利便性の高いプログラムになります。
ゼロからプログラムを作るのは確かに大変ですが、インターネット上にはたくさんのExcelマクロを紹介しているサイトもありますので、それらを上手く組み合わせて、プログラミングしてみてはいかがでしょうか。

一度作ってしまえば、それは自分の知的な資産になりますし、自分だけでなく他人の助けにもなるツールだと思います。

ぜひ、楽しみながら挑戦してみてください。