【VBA】TextToColumns:CSVファイルをエクセルで開く

CSVファイルをエクセルで開く(文字列を区切り文字で分割する)VBAを紹介します。

販売システムや会計システムなどから出力したCSVデータを文字列として編集したい場合に便利です。

CVSファイルを開く時に困ることはなんですか。

データをそのまま表示するためには、手数がかかることです。

具体的な例を上げてみましょう。

目次

CSVファイルを開く時に困ること

CSVファイルをクリックしてエクセルで開く

目的のCSVファイルをダブルクリックをしてエクセルで開くと、セルの書式設定が全て標準になってしまう。

そのため、0が先頭の文字列は0が落ちてしまったり、日付がシリアル値になったりする。

CSVファイルをメモ帳で開きエクセルに貼り付ける

データの全てが1列目に表示されてしまう。

そのため、「区切り位置指定ウィザード」を開き、さらに設定をする必要がある

区切り位置指定ウィザードとは
セル範囲を指定→「データ」タブ→「区切り位置」ボタンを押して実行するコマンド

エクセルにある機能を利用して開く

  1. エクセルを開く
  2. 「データ」タブ
     →「データの取得」
      →「従来のウィザード」
       →「テキストから(レガシ)」
  3. 区切り位置指定ウィザード」が開く
     →言語、区切り文字、セルの書式設定などを選択
      →完了を押してエクセルに取り込む

ー結果ー

「従来のウィザード」が出ない場合
「ファイル」
 →「オプション」
  →「データ」
   →レガシデータインポートウィザードの表示の
    「テキストから(レガシ)」にチェック

そうなんですよね。手数が多いんです。

毎回同じテーブルのデータを表示するのであれば、あらかじめ書式を設定しておくと便利ですね。

エクセルマクロ

CSVファイルをエクセルで開くVBA

TextToColumnsを使うVBA

Sub カンマ区切りでわける()

Sheets(“Sheet1”).Select
Cells.Clear

With Range(“A1”)
      .Value = “1”
      .TextToColumns Destination:=Range(“A1”), _
               DataType:=xlDelimited, _
               Comma:=True, _
               TrailingMinusNumbers:=True
End With

Range(“F:F”).NumberFormatLocal = “@”   
Range(“I:J, O:P”).Style = “Comma [0]”

MsgBox “.csvをメモ帳で開いて、「A1」へ貼り付け”

Range(“A1”).Select

End Sub

TextToColumnsを使うVBAの解説

シート「Sheet1」をクリア

A1に1を入力(空白のセルでは実行できないため)
文字列を区切り文字で分ける
コンマ区切り、負の数にマイナス符号を付ける

セルの書式設定
F列:文字列
I~J列、O~P列:桁区切り

メッセージボックスを開く

A1を選択

その他のセルの書式設定

Selection.NumberFormatLocal = “yyyy/m/d”
Selection.NumberFormatLocal = “G/標準”

Splitを使うVBA

Sub カンマ区切りで分ける()

Dim tmp As Variant
Dim e, i, h As Long

e = Range(“A1”).End(xlDown).Row

Range(“F:F”).NumberFormatLocal = “@”
Range(“I:J, O:P”).Style = “Comma [0]”

For i = 1 To e
    tmp = Split(Cells(i, 1), “,”)

      For h = 0 To UBound(tmp)
        Cells(i, h + 1) = tmp(h)
    Next h

Next i

End Sub

Splitを使うVBAの解説

データ数をカウント

セルの書式設定をする

Split(対象の文字列, 区切り文字)
UBound(tmp):配列tmpの最大インデックス数

順番にセルに転記していく

※「For~Next」でループしているので、TextToColumnsを使う方法よりも遅い

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!

この記事を書いた人

子育てに奮闘しながらも、再びガッツリ走り込める日を夢見るフルタイム会社員。

コメント

コメントする

CAPTCHA


目次
閉じる