| | Home | |
1、テキストデータを指定したシートに開く。
プログラムの過程でテキストファイルを開く場合、指定したブックのシート上に開く事が前提になります。
区切り文字形式（各種）・固定長の場合を列情報を設定した上でVBAから開きます。
プログラムの過程でテキストファイルを開く場合、指定したブックのシート上に開く事が前提になります。
区切り文字形式（各種）・固定長の場合を列情報を設定した上でVBAから開きます。
テキストデータの種類 →sample-txt
■カンマ区切りの場合。
＜例：１＞
商品番号,商品名,単価,数量,日付
003,みかん,30,8,2008/5/12
001,りんご,100,5,2008/5/12
･････････････
■カンマ区切りでデータが、引用符「ダブルクォーテーション」で囲まれている場合。
＜例：２＞
"商品番号","商品名","単価","数量","日付"
"003","みかん","30","8","2008/5/12"
"001","りんご","100","5","2008/5/12"
･････････････
■カンマ区切りでデータが引用符「シングルクォーテーション」で囲まれている場合。
＜例：３＞
'商品番号','商品名','単価','数量','日付'
'003','みかん','30','8','2008/5/12'
'001','りんご','100','5','2008/5/12'
･････････････
■カンマ区切りの場合。
＜例：１＞
商品番号,商品名,単価,数量,日付
003,みかん,30,8,2008/5/12
001,りんご,100,5,2008/5/12
･････････････
■カンマ区切りでデータが、引用符「ダブルクォーテーション」で囲まれている場合。
＜例：２＞
"商品番号","商品名","単価","数量","日付"
"003","みかん","30","8","2008/5/12"
"001","りんご","100","5","2008/5/12"
･････････････
■カンマ区切りでデータが引用符「シングルクォーテーション」で囲まれている場合。
＜例：３＞
'商品番号','商品名','単価','数量','日付'
'003','みかん','30','8','2008/5/12'
'001','りんご','100','5','2008/5/12'
･････････････
■Cドライブのdataフォルダにあるsample.txtを、シート1のRange(A1)を基点として展開します。
※注１ 例として、自機のCドライブのdataフォルダのsample.txtファイルを指定しています。
仮に今開かれているエクセルと同じフォルダの場合、mytxtfile = ActiveWorkbook.Path & "\sample.txt"でも可。
また、ネットワークコンピューターの場合は、mytxtfile = "\\keiri\c\date\sample.txt"（例：keiriというネットワークコンピューターのCドライブのdataフォルダのsample.txtを指定）の様に記述します。
※下図が1〜13までのコードを実行した、各々のシート上の結果になります。（セルＡ1〜A11展開されています）
＜例：１＞の場合
＜例：２＞の場合
＜例：３＞の場合
この後、＜例：１＞と＜例：２＞の場合は以下14〜16のステートメントを実行すれば、求める結果になります。
--------------------------------------------
Set tmprange = Range(myrange, myrange.End(xlDown))
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub
■TextToColumnsメソッド
1つのセル内でスペースやタブ、カンマなどで区切られているデータを複数のセルに分割します。
Ａ列で展開したテキストファイルをカンマ区切りを元に5列（商品番号・商品名・単価・数量・日付）に振り分けます。
対象となるテキストファイルが「区切り文字形式」の時は、引数DataTypeにxlDelimitedを指定します（規定値）。
引数FieldInfoのArray関数では1番目が列番号、2番目が変換方法を表します。（変換方法は、1〜10まで、下記表参照 ※表１）
上の設定の場合1、2列目が「文字列」 3,4列目が「標準」 5列目が「年月日」の形式になります。
※さて、問題は＜例：３＞の「カンマ区切りでデータが引用符・シングルクォーテーションで囲まれている場合」ですが、 よく見ると、各行の頭の「シングルクォーテーション」がありません。
そもそもエクセルの仕様として、セルの先頭に付いている「シングルクォーテーション」は、それ以後に入力された数字や数式を文字列として表示する、という意味を持っていますので、 この場合もその仕様に従った結果になった訳です。
このまま、先ほどのプロシージャを実行しても求める結果は得られません。
プロシージャmyimportを実行した結果
対応策として、次のステートメントを付け加え、「全てのシングルクォーテーション」をはずしてしまうことにします。
tmprange.replace What:="'", Replacement:=""
これを、
Set tmprange = Range(myrange, myrange.End(xlDown)) と
ココ→
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _ の間に入れ、
↓
Set tmprange = Range(myrange, myrange.End(xlDown))
tmprange.replace What:="'", Replacement:=""
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub
として、実行すれば完成です。 →sample-code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub myimport() Dim i As Integer, j As Integer Dim mytxtfile As String, mystr as String Dim myrange As Range, tmprange As Range mytxtfile = "c:\data¥sample.txt" '※注１ Worksheets(1).Activate Range("A1").CurrentRegion.Clear Set myrange = Range("A1") Open mytxtfile For Input As #1 Do Until EOF(1) Line Input #1, mystr myrange.Offset(i).Value = mystr i = i + 1 Loop Close #1 Set tmprange = Range(myrange, myrange.End(xlDown)) tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5)) End Sub |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
myimport という名のプロシージャを記述します。 変数の宣言。 開く対象となるテキストファイルのパスを,変数mytxtfileに代入します。(例：Cドライブのdataフォルダのsampleテキスト) テキストファイルを開くシートを指定します。 念の為、セルA1のアクティブセル領域をクリアします。（領域に以前のデータが残っていた場合の削除） 変数myrangeにセルA1を格納する。 Openステートメントの入力モード(Input)でテキストファイルを開き、番号1を割り当てる。 EOF関数がTrueを返すまで（ファイルの末端になるまで）次の処理を繰り返します。 7.で開いた文書データを行単位で読み込み、変数 mystr に格納します。 myrange(セルA1)から下方向(Offset(1))に格納した文書データを1行ずつ表示していきます。 変数iに1を足します。 8.に戻ります。 ファイルを閉じて、番号（この場合1）を開放します。 変数tmprangeにmyrang(セルA1)から、下に向けてデータの入力されている終端迄のセル範囲を格納します。 14.で選択された範囲のデータをTextToColumnsメソッドを使い複数の（この場合5つ）セルに分割します。 プロシージャの記述を終了します。 |
※注１ 例として、自機のCドライブのdataフォルダのsample.txtファイルを指定しています。
仮に今開かれているエクセルと同じフォルダの場合、mytxtfile = ActiveWorkbook.Path & "\sample.txt"でも可。
また、ネットワークコンピューターの場合は、mytxtfile = "\\keiri\c\date\sample.txt"（例：keiriというネットワークコンピューターのCドライブのdataフォルダのsample.txtを指定）の様に記述します。
※下図が1〜13までのコードを実行した、各々のシート上の結果になります。（セルＡ1〜A11展開されています）
＜例：１＞の場合
＜例：２＞の場合
＜例：３＞の場合
この後、＜例：１＞と＜例：２＞の場合は以下14〜16のステートメントを実行すれば、求める結果になります。
--------------------------------------------
Set tmprange = Range(myrange, myrange.End(xlDown))
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub
■TextToColumnsメソッド
1つのセル内でスペースやタブ、カンマなどで区切られているデータを複数のセルに分割します。
Ａ列で展開したテキストファイルをカンマ区切りを元に5列（商品番号・商品名・単価・数量・日付）に振り分けます。
対象となるテキストファイルが「区切り文字形式」の時は、引数DataTypeにxlDelimitedを指定します（規定値）。
引数FieldInfoのArray関数では1番目が列番号、2番目が変換方法を表します。（変換方法は、1〜10まで、下記表参照 ※表１）
上の設定の場合1、2列目が「文字列」 3,4列目が「標準」 5列目が「年月日」の形式になります。
| ▲Top |
※さて、問題は＜例：３＞の「カンマ区切りでデータが引用符・シングルクォーテーションで囲まれている場合」ですが、 よく見ると、各行の頭の「シングルクォーテーション」がありません。
そもそもエクセルの仕様として、セルの先頭に付いている「シングルクォーテーション」は、それ以後に入力された数字や数式を文字列として表示する、という意味を持っていますので、 この場合もその仕様に従った結果になった訳です。
例：）入力値 → セル上の表示
00001 → 1 (シングルクォーテーションのない場合)
'00001 → 00001 （シングルクォーテーションを付けた場合）
└→ 頭のカンマが取れ、文字列として表示される
00001 → 1 (シングルクォーテーションのない場合)
'00001 → 00001 （シングルクォーテーションを付けた場合）
└→ 頭のカンマが取れ、文字列として表示される
このまま、先ほどのプロシージャを実行しても求める結果は得られません。
プロシージャmyimportを実行した結果
対応策として、次のステートメントを付け加え、「全てのシングルクォーテーション」をはずしてしまうことにします。
tmprange.replace What:="'", Replacement:=""
これを、
Set tmprange = Range(myrange, myrange.End(xlDown)) と
ココ→
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _ の間に入れ、
↓
Set tmprange = Range(myrange, myrange.End(xlDown))
tmprange.replace What:="'", Replacement:=""
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub
として、実行すれば完成です。 →sample-code
| ▲Top |
カンマ区切り以外の場合
■タブ、セミコロン、スペース、カンマ＋スペースなどの場合
※例えば、sub myimport() のステートメント15.の Comma:=True, _ の箇所を
･････････････
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _ ←ココ
に、変更する事で対応できます。
■特殊なケースとして、「カンマ＋スペース」での区切りの場合。 →sample-code →sample-txt
同じく、sub myimport() のステートメント15.を以下の様に変更します。
･････････････
tmprange.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
Comma:=True, Space:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub
※ConsecutiveDelimiter:=True
連続した区切り文字を1文字として認識する時は、Trueを指定します。
■タブ、セミコロン、スペース、カンマ＋スペースなどの場合
※例えば、sub myimport() のステートメント15.の Comma:=True, _ の箇所を
･････････････
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _ ←ココ
Tab:=True, _ ←タブ区切りオン
Semicolon:=True, _ ←セミコロン区切りオン
Space:=True, _ ←スペース区切りオン
Semicolon:=True, _ ←セミコロン区切りオン
Space:=True, _ ←スペース区切りオン
に、変更する事で対応できます。
■特殊なケースとして、「カンマ＋スペース」での区切りの場合。 →sample-code →sample-txt
同じく、sub myimport() のステートメント15.を以下の様に変更します。
･････････････
tmprange.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
Comma:=True, Space:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub
※ConsecutiveDelimiter:=True
連続した区切り文字を1文字として認識する時は、Trueを指定します。
■固定長フィールド形式の場合
固定長フィールド形式とは、文字列の長さが決められている文字列のことです。文字が指定の長さの満たない場合は、スペースや0が追加されます。 主にCOBOLを使ったメインフレームとのデータのやり取りなどに必要です。
＜コード例：２＞ →sample-txt
Sub myimport_kotei()
Dim i As Integer, j As Integer
Dim mytxtfile As String, mystr As String
Dim myrange As Range, tmprange As Range
mytxtfile = "c:\data\kotei.txt" '←固定長フィールド形式のファイルを選択。
Worksheets(1).Activate
Range("A1").CurrentRegion.Clear
Set myrange = Range("A1")
Open mytxtfile For Input As #1
Do Until EOF(1)
Line Input #1, mystr
myrange.Offset(i).Value = mystr
i = i + 1
Loop
Close #1
Set tmprange = Range(myrange, myrange.End(xlDown)) '←ここまではテキストファイルの指定以外、＜コード例・１＞と同じです。
tmprange.TextToColumns DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(18, 1), Array(24, 1), Array(30, 5)) '←引数とArray関数に注意 ※注２
End Sub
※注２
固定長フィールド形式のテキストファイルを開く時は引数 DataType に xlFixedWidth を必ず指定します。
引数FieldInfoのArray関数では1番目が文字位置、2番目が変換方法を表します。（変換方法は、1〜10まで、下記表参照 ※表１）
上の設定の場合1、2列目が「文字列」 3,4列目が「標準」 5列目が「年月日」の形式になります。
■参考として、kotei.txtをワークシート上から開いてみます。
ファイル→開く→Cドライブ→dataフォルダ→kotei.txt （※Cドライブのdataフォルダにkotei.txtがある場合）
すると、下のテキストファイルウィザードが開きます。
区切り位置を指定する。（0、8、18、24、30）
列のデータ形式を選択する。（1、2列 = 文字列 3、4列 ＝標準 5列 = 日付）
↓↓↓完了です↓↓↓
※区切り位置に付いては、受け側で決めるものではなく、通常、メインフレーム側で予め設定されていますので、その指示に従って区切ればOKです。逆に桁数を指定してテキストファイルの作成を依頼される場合は、その指示通りに作成する必要があります。
【参考】
└→ ワークシート上のデータを固定長にして、テキストファイルとして書き出す。
固定長フィールド形式とは、文字列の長さが決められている文字列のことです。文字が指定の長さの満たない場合は、スペースや0が追加されます。 主にCOBOLを使ったメインフレームとのデータのやり取りなどに必要です。
＜コード例：２＞ →sample-txt
Sub myimport_kotei()
Dim i As Integer, j As Integer
Dim mytxtfile As String, mystr As String
Dim myrange As Range, tmprange As Range
mytxtfile = "c:\data\kotei.txt" '←固定長フィールド形式のファイルを選択。
Worksheets(1).Activate
Range("A1").CurrentRegion.Clear
Set myrange = Range("A1")
Open mytxtfile For Input As #1
Do Until EOF(1)
Line Input #1, mystr
myrange.Offset(i).Value = mystr
i = i + 1
Loop
Close #1
Set tmprange = Range(myrange, myrange.End(xlDown)) '←ここまではテキストファイルの指定以外、＜コード例・１＞と同じです。
tmprange.TextToColumns DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(18, 1), Array(24, 1), Array(30, 5)) '←引数とArray関数に注意 ※注２
End Sub
※注２
固定長フィールド形式のテキストファイルを開く時は引数 DataType に xlFixedWidth を必ず指定します。
引数FieldInfoのArray関数では1番目が文字位置、2番目が変換方法を表します。（変換方法は、1〜10まで、下記表参照 ※表１）
上の設定の場合1、2列目が「文字列」 3,4列目が「標準」 5列目が「年月日」の形式になります。
■参考として、kotei.txtをワークシート上から開いてみます。
ファイル→開く→Cドライブ→dataフォルダ→kotei.txt （※Cドライブのdataフォルダにkotei.txtがある場合）
すると、下のテキストファイルウィザードが開きます。
区切り位置を指定する。（0、8、18、24、30）
列のデータ形式を選択する。（1、2列 = 文字列 3、4列 ＝標準 5列 = 日付）
↓↓↓完了です↓↓↓
※区切り位置に付いては、受け側で決めるものではなく、通常、メインフレーム側で予め設定されていますので、その指示に従って区切ればOKです。逆に桁数を指定してテキストファイルの作成を依頼される場合は、その指示通りに作成する必要があります。
【参考】
└→ ワークシート上のデータを固定長にして、テキストファイルとして書き出す。
※表１
XlColumnDataTypeクラスの定数
XlColumnDataTypeクラスの定数
| 数値 | 組み込み定数 | 変換方式 |
| 1 | xlGeneralFormat | 一般（標準） |
| 2 | xlTextFormat | テキスト（文字列） |
| 3 | xlMDYFormat | MDY (月日年) 形式の日付 |
| 4 | xlDMYFormat | DMY (日月年) 形式の日付 |
| 5 | xlYMDFormat | YMD (年月日) 形式の日付 |
| 6 | xlMYDFormat | MYD (月年日) 形式の日付 |
| 7 | xlDYMFormat | DYM (日年月) 形式の日付 |
| 8 | xlYDMFormat | YDM (年日月) 形式の日付 |
| 9 | xlEMDFormat | EMD (台湾年月日) 形式の日付 |
| 10 | xlSkipColumn | スキップ列（その列は削除） |
| ▲Top |