テスト業務で、日々パソコンに保存されているデータを
CSV形式のファイルにし、そのファイルをExcelで分析することになりました。
見なければならないファイルが一つや二つという問題ではなく、正直煩雑さを感じるようになってしまったので
ファイルを選択し、カンマ区切りで列を分けるところまでだけでも
マクロを使って短縮できないものか
と思い立ちました。
Excelでマクロを使うために、手始めに
Excelで「開発」タブを出現させる必要がありますが
それは既に当ブログ内で紹介済みなので割愛させていただきます。
早速調べていくと
https://qiita.com/ktyubeshi/items/199fd3efcf48e67645f1
にて、このようなコードを発見してしまいました。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
Option Explicit
Sub ReadTabDelimitedTextFile()
'タブ区切りファイルを全て文字列として読み込む
Dim FileName As String
Dim i As Long
Dim Cnt As Long
Dim Buf As Variant
Dim FileNo As Integer
Dim SplitString As Variant
'ファイルダイアログを表示・・・①
FileName = Application.GetOpenFilename("テキストファイル,*.txt")
If FileName <> "False" Then
'全セル選択して書式を文字列にセットする
Cells.Select
Selection.NumberFormatLocal = "@"
Cells(1, 1).Select
'空いているファイル番号を取得
FileNo = FreeFile()
Buf = Space(FileLen(FileName))
'ファイルを開いてbufに1行読み込み
' → タブで配列に分割
' → セルに書き出し
Open FileName For Input As #FileNo
Do Until EOF(FileNo)
Line Input #FileNo, Buf
Cnt = Cnt + 1
‘・・・②
SplitString = Split(Buf, vbTab)
For i = 0 To UBound(SplitString)
Cells(Cnt, i + 1) = SplitString(i)
Next i
Loop
Close #FileNo
'そのままでは数式等が使えなくなるため、書式を標準に戻す
Cells.Select
Selection.NumberFormatLocal = "G/標準"
Cells(1, 1).Select
Else
'ファイルダイアログをキャンセルされた場合何もしない
End If
End Sub
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
おっ、これを使えば万事解決じゃな！
ありがたくこれを使わせてもらうとしよう！
？？？「おい、お前は裏取りをしないのか？まだまだ半人前だな、おい！」
そんな天の声が聞こえたような気がしますが
とりあえず、このまま使ってみましょう。
・・・＿|￣|○(csvファイルから出てこないとはこれ如何に・・・。)
はい、ちゃんと裏取ってきますorz
ここから、少し手を加えるだけで
最低限欲しいものは作れそうですが…(根拠のない自信)
①でファイルを選ぶダイアログを出現させるのに使っている
Application.GetOpenFilenameメソッド
これは、ファイル名を取得するために[ファイルを開く]ダイアログを表示するものです。
マイクロソフト社のリファレンス(https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/application-getopenfilename-method-excel)より、
式 . GetOpenFilename( FileFilter , FilterIndex , Title , ButtonText , MultiSelect )
とありました。
現状では、csvファイルを選択することができないので
csvファイルも選択できるようにFiliFilterにあたる部分を書き換えてみます。
FileName = Application.GetOpenFilename("テキストファイル,*.txt;*.csv")
無事にcsvファイルが選べました。
さぁ、これで取り込めるだろ・・・。
・・・＿|￣|○
残念ながら、思ったように取り込めていませんでした。
カンマで区切れるのが理想なのですが・・・。
もうひと手間、②をいじってみます。
SplitString = Split(Buf, vbTab)
Split関数の書式は
マイクロソフト社のリファレンス
https://msdn.microsoft.com/ja-jp/vba/language-reference-vba/articles/split-function
によると、書式は
Split( expression ** [ **,delimiter [ ,limit [ ,compare ]]] )
この”expression”に区切りたい文字列、”delimiter”に何を基準にして区切ればいいかを入れれば使えるようなので
SplitString = Split(Buf, “,”)
にして、カンマ(,)で区切るようにしてみます。
ちなみに変更前のvbTabはタブ文字という意味です。
もう一度、実行してみましょう。
で～きた～！！
いやぁ、いい感じになりました(安堵の表情)。
？？？「まさか、上手くできたからこれで終わりと思ってないだろうな？」
はい、もう少し頑張ります・・・＿|￣|○
途中で出てきた
Selection.NumberFormatLocal = "@"
Selection.NumberFormatLocal = "G/標準"
は意外に役立ちます。
CSV形式のファイルは、標準のまま取り込むと
日時の秒のところが消えてしまったり、値の末尾が勝手に0に変えられたりして
思い通りに表示できないことがあるのですが
Selection.NumberFormatLocal = "@"
として("@"は文字列形式を表す)あげれば
勝手に変換されることなく、取り込むことができるのです。
また、取り込んだ後
このカーソルのセルに数式を入れると
数式がそのまま、文字列として入力され計算ができません。
この操作、毎度毎度やっていると億劫なので、助かります。
最初は”＠”を指定することで、文字列として取り込み
終わりに”G/標準”に戻すのがポイントですね。
最後に、最初のコードを
本来やってみたかったことが実現できるように改変するとこうなります。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
Option Explicit
Sub ReadCsvFile()
'タブ区切りファイルを全て文字列として読み込む
Dim FileName As String
Dim i As Long
Dim Cnt As Long
Dim Buf As Variant
Dim FileNo As Integer
Dim SplitString As Variant
'ファイルダイアログを表示
FileName = Application.GetOpenFilename("テキストファイル,*.txt;*.csv")
If FileName <> "False" Then
'全セル選択して書式を文字列にセットする
Cells.Select
Selection.NumberFormatLocal = "@"
Cells(1, 1).Select
'空いているファイル番号を取得
FileNo = FreeFile()
Buf = Space(FileLen(FileName))
'ファイルを開いてbufに1行読み込み
' → タブで配列に分割
' → セルに書き出し
Open FileName For Input As #FileNo
Do Until EOF(FileNo)
Line Input #FileNo, Buf
Cnt = Cnt + 1
SplitString = Split(Buf, “,”)
For i = 0 To UBound(SplitString)
Cells(Cnt, i + 1) = SplitString(i)
Next i
Loop
Close #FileNo
'そのままでは数式等が使えなくなるため、書式を標準に戻す
Cells.Select
Selection.NumberFormatLocal = "G/標準"
Cells(1, 1).Select
Else
'ファイルダイアログをキャンセルされた場合何もしない
End If
End Sub
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
何かのお役に立てたら幸甚です。
