vbaデータの一括の読み込み

ソフトウェア
2022.03.28

やっと実験のデータが集め終わった！

データをエクセルにまとめて散布図をつくろう！

データファイルを開いて…

エクセルにコピペ…

データファイルを開いて…

エクセルにコピペ…

データファイルを…

面倒すぎる！！！

こんな作業してたら実験も進まないよ！

一気に取り込めるVBA作ったで

神！！！！

ということで簡単にテキストデータを一括でエクセルに取り込めるVBAを紹介しようと思います。

散布図作成は、次の記事で紹介します。

使い方

1.開発タブの表示

開発のタブが表示されていないときは、以下のように設定してください。

ファイル→オプション→リボンのユーザー設定→メインタブ→開発にチェック

2.FileSystemObjectの設定

設定をしていない状態でマクロを実行すると

このようなコンパイルエラーが表示されると思います。

↓↓の設定を行ってください！↓↓

FileSystemObjectの設定をオン

VisualBasic→ツール→参照設定→「Microsoft Scriping Runtime」にチェックを入れる
分からない場合、こちらのサイトを参考にしてみてください。

3.コードのコピペ

以下のコードをvisual basicの標準モジュールにコピペしてください。先に使い方を解説します。

Sub テキストファイルを順番に読み込みしてエクセルへ転記タブ区切り()

Dim openFilePath As Variant
Dim pt As Variant
Dim MacroFile As Workbook
Dim Cnt As Long
Dim i As Long
Dim data_quant As Long

Dim fso As FileSystemObject
Set fso = New FileSystemObject ' インスタンス化
Dim file_name As String


'開いているシートの編集をする
Set MacroFile = ActiveWorkbook

'取り込みデータの最大列数の入力画面を出力
column_quant = Application.InputBox(prompt:="取り込むデータの列数を入力して下さい", _
title:="数値入力", _
Type:=1)
If column_quant = False Then
MsgBox "キャンセルします"
Exit Sub
End If

'ファイルを開くダイアログ（複数ファイル選択OK）
openFilePath = Application.GetOpenFilename _
(,, , "ファイルを選択", MultiSelect:=True)

'テキストファイルを開く
If IsArray(openFilePath) Then

Cnt = 1
t_i = 1
'選択したファイルを順番に開く
For Each pt In openFilePath

'マクロ中の画面の切り替えを非表示にする（マクロが軽くなります）
Application.ScreenUpdating = False

file_name = fso.GetBaseName(pt)
Cells(t_i, 1) = file_name
Cells(1, Cnt + 7) = file_name
'テキストファイルの貼り付け設定
'consecutive-連続する区切り文字
'Datatype-固定長のフィールドデータの設定
'その他のオンオフ、trueがオンでfalseがオフです

Workbooks.OpenText Filename:=pt, _
ConsecutiveDelimiter:=True, _
DataType:=xlDelimited, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
otherchar:=""

For i = 1 To column_quant
Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp)).Copy MacroFile.ActiveSheet.Cells(2, Cnt + i + 6)
Next

Cnt = Cnt + column_quant + 2
t_i = t_i + 1

'テキストファイル閉じる
ActiveWorkbook.Close False

Next pt
End If

End Sub

4.マクロの実行

開発タブ→マクロ→「テキストファイルを順番に読み込みしてエクセルへ転記タブ区切り」を実行します。

テキストファイルから取り込むデータ列の範囲を設定することができます。必要なデータ列が取り込まれてないときは値を増やしてみて下さい。

ファイル選択のポップアップが開くので取り込みたいデータをすべて選択します。

選択したテキストファイルの上から順にエクセルに出力されます。2列ごと開けて出力されます。

データを取り込んだ後

①A1セルから順に取り込んだテキストファイル名が表示されます。
②それぞれのデータの上にテキストファイル名が表示されます。
③読み込んだテキストデータが表示されます。今回は、取り込むデータの列数を「3」と入力したので3列分が表示されます。

マクロのヒント
区切り文字の設定
Workbooks.OpenText Filename:=pt, _
ConsecutiveDelimiter:=True, _
DataType:=xlDelimited, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
otherchar:=""

コードのこの部分を編集することで取り込み設定を変えることができます。

Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
otherchar:=""

ここのコードは、区切り文字の設定です。現在はタブ区切りになっているので「Tab」のみが「True」になっています。例えばコンマ区切りに変えたい場合は、「Tab」を「False」に「Comma」を「True」に変えます。

文字化けするときは？

デフォルトでは「UTF-8」が文字コードとして設定されています。文字化けするときは、以下のコードを追加してみましょう。

Origin:=932, _
  • Shift_JIS : 932
  • UTF-8 : 65001
  • UTF-16 : 1200

がそれぞれの文字コードに対応する番号です。文字コードにあった番号を追加してください。コードは「932」となっているのでテキストデータをJISで読み込みます。

各コード解説(簡単に）

詳しいコードの解説は、調べるとわかりやすい記事があると思うのでそちらでお願いします。

1.定義

Dim openFilePath As Variant
Dim pt As Variant
Dim MacroFile As Workbook
Dim Cnt As Long
Dim i As Long
Dim data_quant As Long

Dim fso As FileSystemObject
Set fso = New FileSystemObject ' インスタンス化
Dim file_name As String

使われる変数の型の設定をしています。

  • Variant-すべての型
  • Long-数値の型
  • String-文字列の型

FileSystemObjectは、読み込むテキストファイルの名前を取得するのに設定しています。

2.データ入力

'開いているシートの編集をする
Set MacroFile = ActiveWorkbook

'取り込みデータの最大列数の入力画面を出力
column_quant = Application.InputBox(prompt:="取り込むデータの列数を入力して下さい", _
title:="数値入力", _
Type:=1)
If column_quant = False Then
MsgBox "キャンセルします"
Exit Sub
End If

2行目で現在開いているシートにマクロを適用します。

コードを実行すると次のようなダイアログが表示されます。

①-title
②-prompt
に対応しています。

typeは入力できる型を選択できます。今回の場合は、1なので数値を入力することができます。

✕やキャンセルを押すと「column_quant」がfalseになり、「exit sub」のプログラムが動くため、プログラムが終了します。

3.テキストファイルの取得

'ファイルを開くダイアログ（複数ファイル選択OK）
openFilePath = Application.GetOpenFilename _
(,, , "ファイルを選択", MultiSelect:=True)

2行目で、ファイルを選択するポップアップを表示します。

4番目の引数は、複数ファイルの選択に関する設定です。複数ファイルの選択が有効になっています。

4.テキストファイル名の表示

'テキストファイルを開く
If IsArray(openFilePath) Then

Cnt = 1
t_i = 1
'選択したファイルを順番に開く
For Each pt In openFilePath

'マクロ中の画面の切り替えを非表示にする（マクロが軽くなります）
Application.ScreenUpdating = False

file_name = fso.GetBaseName(pt)
Cells(t_i, 1) = file_name
Cells(1, Cnt + 7) = file_name

選んだテキストファイルの順番に開いていきます。途中の「Cnt」と「t_i」はこれから使う変数の設定をしています。ファイル名の表示の設定をしています。

5.データの出力

'テキストファイルの貼り付け設定
'consecutive-連続する区切り文字
'Datatype-固定長のフィールドデータの設定
'その他のオンオフ、trueがオンでfalseがオフです

Workbooks.OpenText Filename:=pt, _
ConsecutiveDelimiter:=True, _
DataType:=xlDelimited, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
otherchar:=""

For i = 1 To column_quant
Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp)).Copy MacroFile.ActiveSheet.Cells(2, Cnt + i + 6)
Next

Cnt = Cnt + column_quant + 2
t_i = t_i + 1

'テキストファイル閉じる
ActiveWorkbook.Close False

Next pt
End If

End Sub

区切り文字の設定を行っています。

テキストを1列ごとエクセルにコピーしています。全列コピーが終わったら、2列開けて次のテキストファイルのコピーを行います。

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