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マクロを紹介しているサイトもありますので、それらを上手く組み合わせて、プログラミングしてみてはいかがでしょうか。
一度作ってしまえば、それは自分の知的な資産になりますし、自分だけでなく他人の助けにもなるツールだと思います。
ぜひ、楽しみながら挑戦してみてください。
