[[20171203124832]]　『固定長の.datファイルから、バイト数で切り出した』（koyayuu）　ページの最後に飛ぶ

『固定長の.datファイルから、バイト数で切り出したをセルに貼り付けたい』（koyayuu）

少しは講習会を受けたり、本を読んだりしましたが、業務で直接コードを書いたことのないVBA の初心者です。
今回、業務としてのVBAのことで質問です。
固定長600バイトの.datファイルがあります。
これを読み取り、以下の項目だけを切り取って
Excel2013のシートに貼り付けたいという依頼を受けました。

支出科目コード 15－22バイト A1～
経費負担所属 23–30バイト B1～
データ区分 32–33バイト C1～
時間外手当 195–200バイト D1～
管理職特別手当227–234バイト E1～

600バイトには、日本語や全角は一切ふくまれておりません。お手すきの方がいらしたら、サンプルコードなどをのせていただけないでしようか。mid関数を使うかとも思ったのですが、ファイルの情報を全て読み込んで、部分的にバイト指定して、切り出す方法が分かりません。
参考になりそうなサンプルは見つけたのですが、わたしの勉強不足もあり、どうすれば、
目的通りのコードになるのか、分かりません。

http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110.html

http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110_055.html

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 ＞固定長600バイトの.datファイルがあります。

 それってテキストファイルですよね？

 エクセルに外部データの取り込み機能(※)がありますから、
 そのインポート操作をマクロの記録でコードに録ってみてください。

 ※「データ」タブ→「外部データの取込み」グループ→テキストファイルを指定
 　すると、テキストファイルウィザードが開きますので、
 　固定長フィールドのデータを指定(1/3)
 　フィールド幅の指定(2/3)　→　完了ボタンクリック

 それで取り込むことができれば、後は配置の調整だけですから、何とでもなりますよ。

（半平太） 2017/12/03(日) 15:01

 あれー？
 koyayuuさんが探したこれそのものじゃないですか?
                      ↓
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110_055.html

 何がどう分かんないんですか？

（半平太） 2017/12/03(日) 15:14

申し訳ございません。
上の二つばかり見て、ここを見るのを忘れてました。
でも、斜め読みで全てを理解するほど、csv形式以外のデータの取り込みに慣れてないので、明日あたり、何回か確認するかもしれません。その時はおつきあいいただけると、助かります。
（koyayuu） 2017/12/03(日) 17:15

あの、すみません。指摘されたページをじっくりと見たところ、気づいたことを
まとめてみます。

・まず、.datファイル自体は全て半角英数字か半角スペースで、ひらがなや漢字等の

   全角文字は含まれていません。ですから、半角か全角かの判定を行う、
' 指定バイト数の固定長データ作成(文字列処理)は不要と考えられます。

・次にファイルを開き、エンドオブファイルまで読み込む流れは理解できましたが、

    読み込んだ1レコード600バイト中、必要な5項目のデータは、とびとびで、
    バイト数を個別に取り出して、A列～E列の各セルに書き込むものですが、
    これが、どうしてもわからないんですよ。固定長のデータを業務で扱う以上、
    あってもおかしくない考え方だと思うんですが、なぜか、固定長データを
    連続して読み込み、どこで区切るかの問題になってしまいます。
    ここが、どうしてもわかりません。多分、その考え方のsampleがあれば、
    教えていただけないでしょうか。
（koyayuu） 2017/12/03(日) 18:26

 済みません。
 引用された記事はテキストファイルに固定長で「書き出す」ものでした。m(__)m

 今回のはデータがシンプルなので、井上治さんのような凝った作りにする必要はないような気がします。

 koyayuuさんのデータの型がどうなっているのか分からないですが
 取りあえず、全部文字型で処理してもワークするなら、以下のような改造になるんじゃないですか？

 '*******************************************************************************
 '   固定長形式テキストファイルを読み込むサンプル(改行あり)
 '
 '   作成者:井上治  URL:http://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
 '*******************************************************************************
 ' [参照設定]
 '   ・Microsoft Scripting Runtime
 '*******************************************************************************
 Option Explicit

 '*******************************************************************************
 ' 固定長形式テキストファイルを読み込むサンプル(改行あり)
 ' 参照設定：Microsoft Scripting Runtime
 '*******************************************************************************
 Sub READ_FixLngFile1()

 Const cnsFOLDNAME = "C:\ ・・・・・"  '←ここを自分の環境に合わせる(1/2)
 Const cnsFILENAME = "\Sample.dat"     '←ここを自分の環境に合わせる(2/2)

     Dim FSO As New FileSystemObject ' FileSystemObject
     Dim TS As TextStream            ' TextStream
     Dim GYO As Long                 ' 収容するセルの行
     Dim strREC As String            ' レコードを収容する変数

     ' 指定ファイルをOPEN(入力モード)
     Set TS = FSO.OpenTextFile(cnsFOLDNAME & cnsFILENAME, ForReading, False)
     ' 2行目から開始
     Rows("2:65536").ClearContents
     GYO = 2
     Do Until TS.AtEndOfStream
     ' レコードの読み込み
         strREC = TS.ReadLine

         ' １レコード分のセルへのセット
         Call GP_EDIT_FixLngRec(strREC, GYO)
         ' 行を加算
         GYO = GYO + 1
     Loop
     ' 指定ファイルをCLOSE
     TS.Close
     Set TS = Nothing
     Set FSO = Nothing
 End Sub

 '*******************************************************************************
 ' CSV形式テキストの1レコードのセルへの転記処理(スタート～エンドバイト目)
 '*******************************************************************************
 Private Sub GP_EDIT_FixLngRec(strREC As String, GYO As Long)
     ' A列
     Cells(GYO, 1).Value = FP_GET_REC_To_String(strREC, 15, 22)
     ' B列
     Cells(GYO, 2).Value = FP_GET_REC_To_String(strREC, 23, 30)
     ' C列
     Cells(GYO, 3).Value = FP_GET_REC_To_String(strREC, 32, 33)
     ' D列
     Cells(GYO, 4).Value = FP_GET_REC_To_String(strREC, 195, 200)
     ' E列
     Cells(GYO, 5).Value = FP_GET_REC_To_String(strREC, 227, 234)
 End Sub

 '*******************************************************************************
 ' 固定長データから指定バイト数を切り出す処理(文字列処理)
 '*******************************************************************************
 Private Function FP_GET_REC_To_String(strREC As String, _
                                       lngStrPos As Long, _
                                       lngLngs As Long) As String
     Dim strREC2 As String
     strREC2 = Mid(strREC, lngStrPos, lngLngs - lngStrPos + 1)
     FP_GET_REC_To_String = strREC2
 End Function

（半平太） 2017/12/03(日) 20:02

コメント、ありがとうございます。私なりに少し整理してみました。
（もちろん、これで完成などとは思っておりません）

ただ、質問がいくつか、あります。
Const cnsFOLDNAME = "C:\ ・・・・・" '←ここを自分の環境に合わせる(1/2)
上記はなぜ、ドライブ指定しているのでしょうか。
また、
Const cnsFILENAME = "\s01201711R.DAT" '←ここを自分の環境に合わせる(2/2)
\マークさえつければ、.dat ファィルの位置は分ると思うのですが。

あと、 [参照設定]・Microsoft Scripting Runtimeですが、これやFSOを使うには
何か設定する必要があるのでしょうか。ネット上ではVBEのオプションに設定項目が
あるとありますが、Excel2013のVBEにはありませんでした。
特に気にする必要はないのでしょうか。ご存知でしたら、お願いいたします。

'*******************************************************************************

 '   固定長形式テキストファイルを読み込むサンプル(改行あり)
 '*******************************************************************************
 ' [参照設定]
 '   ・Microsoft Scripting Runtime
 '*******************************************************************************
 Option Explicit
 '*******************************************************************************
 ' 固定長形式テキストファイルを読み込むサンプル(改行あり)
 ' 参照設定：Microsoft Scripting Runtime
 '*******************************************************************************
 Sub READ_FixLngFile1()
 Const cnsFOLDNAME = "C:\ ・・・・・"  '←ここを自分の環境に合わせる(1/2)
 Const cnsFILENAME = "s01201711R.DAT"     '←ここを自分の環境に合わせる(2/2)
     Dim FSO As New FileSystemObject ' FileSystemObject
     Dim TS As TextStream            ' TextStream
     Dim GYO As Long                 ' 収容するセルの行
     Dim strREC As String            ' レコードを収容する変数
     ' 指定ファイルをOPEN(入力モード)
     Set TS = FSO.OpenTextFile(cnsFOLDNAME & cnsFILENAME, ForReading, False)
     ' 2行目から開始
     Rows("2:65536").ClearContents
     GYO = 2
     Do Until TS.AtEndOfStream
     ' レコードの読み込み
         strREC = TS.ReadLine
         ' １レコード分のセルへのセット
         Call GP_EDIT_FixLngRec(strREC, GYO)
         ' 行を加算
         GYO = GYO + 1
     Loop
     ' 指定ファイルをCLOSE
     TS.Close
     Set TS = Nothing
     Set FSO = Nothing
 End Sub
 '*******************************************************************************
 '固定長テキストの1レコード内のﾊﾞｲﾄ指定でのセルへの転記処理(スタート～最終行)
 '*******************************************************************************
 Private Sub GP_EDIT_FixLngRec(strREC As String, GYO As Long)
     ' A列
     Cells(GYO, 1).Value = FP_GET_REC_To_String(strREC, 15, 22)
     ' B列
     Cells(GYO, 2).Value = FP_GET_REC_To_String(strREC, 23, 30)
     ' C列
     Cells(GYO, 3).Value = FP_GET_REC_To_String(strREC, 32, 33)
     ' D列
     Cells(GYO, 4).Value = FP_GET_REC_To_String(strREC, 195, 200)
     ' E列
     Cells(GYO, 5).Value = FP_GET_REC_To_String(strREC, 227, 234)
 End Sub

（koyayou） 2017/12/04(月) 16:22

 ＞Const cnsFOLDNAME = "C:\ ・・・・・" '←ここを自分の環境に合わせる(1/2) 
 ＞上記はなぜ、ドライブ指定しているのでしょうか。 
 ＞また、 
 ＞Const cnsFILENAME = "\s01201711R.DAT" '←ここを自分の環境に合わせる(2/2) 
 ＞\マークさえつければ、.dat ファィルの位置は分ると思うのですが。

 ちょっと意味が分かりません。
 どこのフォルダーに入っているのか分からなかったら、cnsFILENAMEがある場所が特定できないです。

 誰かが「俺は山田だ」と言っただけで、どこの国のどこの町に住んでいるのか分かりますか？

 ＞あと、 [参照設定]・Microsoft Scripting Runtimeですが、これやFSOを使うには 
 ＞何か設定する必要があるのでしょうか。

 井上さんが「設定」って書いているんですから、そのコードなら必要ですよ。
 敢えて設定したくないなら、また話は別ですが、無理に逆らうこともないじゃないですか？
 本題はそんなことじゃないんですから。

 「VBA、参照設定」でネット検索してください。

（半平太） 2017/12/04(月) 17:25

 拡張子DATファイルはアプリケーションに依って仕様が異なるのが通常です。
 メモ帳などで文字列として確認できていても、基本的には「Binaryファイル」だと
 思ってください。ご提示の項目名から判断すると会計アプリか給料計算アプリから
 出力されたデータのようですね。

 気になる点は、

 【時間外手当　195-200バイト】５バイト
  　5バイトの構成ですね。整数だと4バイトですが、「文字列」として扱っていますか？
    文字列だと5桁の数字ですか？

 【管理職特別手当227–234バイト】７バイト
    これは7バイト構成ですね。こちらは7桁の「文字列」ですか？

 ※まずはご自身でそのDATファイルの「データ出力仕様」を理解することが先です。
（Abyss2） 2017/12/05(火) 15:51

半平太さん、申し訳ありませんm(_ _)m
Microsoft Scripting Runtimeですが、以前探した時、VBEのツール→オプションの中に参照設定があると勘違いしてしまい。そこで、見つからなかったので、訳がわからなくなりました。
ツール→参照設定なんですね。Microsoft Scripting Runtimeにはチェックを入れました。

Abyss2さん、.DAT、ただのテキストという認識が強く、あくまでバイト数分とれればいいと考えていました。
ご指摘、ありがとうございます。VBAで自動化できるまではテキストファイルウィザードで、A列に全て取り込み、以下の５項目以外は排除する。という手順でした。なお、取出す、項目は以下のように最初の3項目は、文字列指定に直していると言われました。あとの2項目を含む、他の項目は全て「Ｇ／標準」でした。

どうも、去年、受けたVBAベーシック、スタンダードという4日間の講習会で、田中先生と言う方が
Excelの操作をマクロ取込しても無駄が多い上に、繰り返し処理ができず、VBAの勉強にならない。
多分、正論なんでしょうが、それでも、今まで手動で、どう操作してきたのかを真っ先に確認しな
かったのは私の手落ちです。ご迷惑をおかけしました。とりあえず、手動操作を正確にトレースしながら、
マクロ取込し、なにか参考になる部分があるか見てみようと思います。

支出科目コード 15－22バイト A1～（文字列）
経費負担所属 2330バイト B1～（文字列）
データ区分 3233バイト C1～（文字列）
時間外手当 195200バイト D1～（Ｇ／標準）
管理職特別手当227234バイト E1～　（Ｇ／標準）

（koyayou） 2017/12/07(木) 09:54

 フーム、なんかよく分からない展開ですねぇ。

 ＞Const cnsFOLDNAME = "C:\ ・・・・・"  '←ここを自分の環境に合わせる(1/2)
 ＞Const cnsFILENAME = "\Sample.dat"     '←ここを自分の環境に合わせる(2/2)

 (1/2)→　ファイルのあるフォルダーパス
 (2/2)→　ファイル名(頭に「\」付き)

 それをチャンと書いて、私が改造したプロシージャを実行すれば(成功しなきゃ始まらないですけど）
 プロトタイプとしては終わりだと思っているんですけど。

 後は、それらの名前を動的に変更できるように修正していけば、いいんじゃないですか？ 
 何の苦労も要らないです。

（半平太） 2017/12/07(木) 10:18

テキストウイザードで読み込み出来るならテキストですよね。

で
実験代行！

600個の半角数字　（1234567890　を　6回つなげ）を　10回つなげ　600個の数字が50行のダミーファイル
サイズ　30000バイト　+　キャリッジとリターン　2*50　＝　30100バイトです。
半平太さんの提示されたコードで、パスとファイル名のみ変更で
読み込めてましたですよ。
ご参考まで。

<(_ _)>

（隠居じーさん） 2017/12/07(木) 11:15

（隠居じーさん）さん、ご検証いただきありがとうございますm(_ _)m
　ぜひ、参考にさせていただきます。
（koyayou） 2017/12/07(木) 11:29

半平太さん、どうも真っ直ぐに話が進まないのは、私の経験不足が大きいでしょう。
手動操作をマクロで記録してみましたが、直接的には役に立たない内容でした。
隠居じーさんさんの助言もありましたし、半平太さんが最初に書いていただいたSampleをじっくり見なおして、
意味を理解したうえで検証してみようと思います。
（koyayou） 2017/12/07(木) 11:37

修正を加えましたが、強引に付け足すだけの内容になってしまいました。
固定長ファイルを最後まで読み込み、A〜E列に分るという動作自体には半平太さんが書いていただいたSample
で動作自体は全く問題ありませんでした。ありがとうございますm(_ _)m

しかし、残念ながら最後にひとつの壁が立ちふさがりました。

以下がテキストウィザードを使って、手動でインポートした最終目標です。

　A列　　　　　　B列 C列　　　D列　　　　E列
01020101　｜　25010205　｜　01　｜　　　0　｜　　　　0　｜
01020101　｜　060503　　｜　01　｜　17256　｜　　　　0　｜
01020101　｜　060112　　｜　02　｜　　　0　｜　 153000　｜
01020101　｜　25010205　｜　01　｜ 659994　｜　　　　0　｜

最初に半平太さんのSampleを流した時は以下のようになりました。

　A列　　　　　B列 C列　　　D列　　　　E列
1020101　｜　25010205 ｜　 1　｜　　　　｜　　　　　｜
1020101　｜　60503　　｜　 1　｜　17256　｜　　　　　｜
1020101　｜　60112　　｜　 2　｜　　　　｜　 153000　｜
1020101　｜　25010205 ｜　 1　｜ 659994　｜　　　　　｜

これはテキストファイルウィザードで、ABC列は「文字列」指定し、
DE列は「G/標準」を指定したことと無関係ではないと考え、以下の
行を付け加えてみました。

     'ActivesheettのA:Cの列全体をセルを文字列化
     ActiveSheet.Range("A:C").NumberFormatLocal = "@"
     'ActivesheettのD:Eの列全体をセルを標準化
     ActiveSheet.Range("D:E").NumberFormatLocal = "G/標準"

その実行結果が以下です。

　A列　　　　　　B列 C列　　　D列　　　　E列
01020101　｜　25010205　｜　01　｜　　　　｜　　　　0　｜
01020101　｜　060503　　｜　01　｜　17256　｜　　　　0　｜
01020101　｜　060112　　｜　02　｜　　　　｜　 153000　｜
01020101　｜　25010205　｜　01　｜ 659994　｜　　　　0　｜

ここまで来ると、あと一歩だとは思うのですが、D列だけ何故、
「G/標準」に指定しても0のみの場合に空白になるのか分かりません。
なにか確認すべきことでもあれば、教えて頂けないでしょうか。

 Option Explicit
 '*******************************************************************************
 ' 固定長形式テキストファイルを読み込むサンプル(改行あり)
 ' 参照設定：Microsoft Scripting Runtime
 '*******************************************************************************
 Sub READ_FixLngFile1()
 Const cnsFOLDNAME = "C:\Users\somujimu23\Desktop\検証環境\"  '←ここを自分の環境に合わせる(1/2)
 Const cnsFILENAME = "\s01201711R.DAT"     '←ここを自分の環境に合わせる(2/2)
     Dim FSO As New FileSystemObject ' FileSystemObject
     Dim TS As TextStream            ' TextStream
     Dim GYO As Long                 ' 収容するセルの行
     Dim strREC As String            ' レコードを収容する変数
     Dim myRow As Integer
        myRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
        Rows(myRow).Insert Shift:=xlDown
        Cells(myRow, 1) = "支出科目"
        Cells(myRow, 2) = "負担所属"
        Cells(myRow, 3) = "区分"
        Cells(myRow, 4) = "時間外手当額"
        Cells(myRow, 5) = "特別勤務手当"
     'ActivesheettのA:Cの列全体をセルを文字列化
     ActiveSheet.Range("A:C").NumberFormatLocal = "@"
     'ActivesheettのD:Eの列全体をセルを文字列化
     ActiveSheet.Range("D:E").NumberFormatLocal = "G/標準"
     '指定ファイルをOPEN(入力モード)
     Set TS = FSO.OpenTextFile(cnsFOLDNAME & cnsFILENAME, ForReading, False)
     ' 2行目から開始
     Rows("2:65536").ClearContents
     GYO = 2
     Do Until TS.AtEndOfStream
     ' レコードの読み込み
         strREC = TS.ReadLine
         ' １レコード分のセルへのセット
         Call GP_EDIT_FixLngRec(strREC, GYO)
         ' 行を加算
         GYO = GYO + 1
     Loop
     ' 指定ファイルをCLOSE
     TS.Close
     Set TS = Nothing
     Set FSO = Nothing
     ActiveSheet.Columns("A:E").AutoFit
 End Sub
 '*******************************************************************************
 ' CSV形式テキストの1レコードのセルへの転記処理(スタート～エンドバイト目)
 '*******************************************************************************
 Private Sub GP_EDIT_FixLngRec(strREC As String, GYO As Long)
     ' A列
     Cells(GYO, 1).Value = FP_GET_REC_To_String(strREC, 15, 22)
     ' B列
     Cells(GYO, 2).Value = FP_GET_REC_To_String(strREC, 23, 30)
     ' C列
     Cells(GYO, 3).Value = FP_GET_REC_To_String(strREC, 32, 33)
     ' D列
     Cells(GYO, 4).Value = FP_GET_REC_To_String(strREC, 195, 200)
     ' E列
     Cells(GYO, 5).Value = FP_GET_REC_To_String(strREC, 227, 234)
 End Sub
 '*******************************************************************************
 ' 固定長データから指定バイト数を切り出す処理(文字列処理)
 '*******************************************************************************
 Private Function FP_GET_REC_To_String(strREC As String, _
                                       lngStrPos As Long, _
                                       lngLngs As Long) As String
     Dim strREC2 As String
     strREC2 = Mid(strREC, lngStrPos, lngLngs - lngStrPos + 1)
     FP_GET_REC_To_String = strREC2
 End Function

（koyayou） 2017/12/08(金) 14:38

 0が空白になることはないと思います。

 こちらでは再現しないので何ともなりません。

 これで私は降ります。

（半平太） 2017/12/08(金) 16:52

半平太さん、誠に申し訳ありませんでした！<m(__)m>

＞0が空白になることはないと思います。

おっしゃる通りです。取り込んだ部分以外のレコードでは0は全て+0に
なっていて、E列は+36、+336とか、1以上の数字は全て+(数字)となるのですが、
そうでないレコードは全て空白でした。元々、空白だったわけです。

Excelのファイルテキストウィザードで、G/標準を選択すると、なぜ、0が挿入されるのか
不明です。ただ、空白であるという大前提がある以上、空白だった場合、0に置き換える
手法を探してみようと思います。今まで、ありがとうございました。
（koyayou） 2017/12/11(月) 09:36

コメント返信：

[ 一覧(最新更新順) ]

[[20171203124832]] 『固定長の.datファイルから、バイト数で切り出した』（koyayuu） ページの最後に飛ぶ

[[20171203124832]]　『固定長の.datファイルから、バイト数で切り出した』（koyayuu）　ページの最後に飛ぶ