ExcelVBAで巨大なテキストファイル(ファイルサイズ7MB程、全角半角混在の改行無しのベタデータ)を読み込み、500文字で区切ってそれを1レコードとし、A1,A2・・・とセルを変えて文字列を貼り付けようとしています。

いかんせんファイルサイズが大きいために処理時間に大変時間がかかっています。
・処理の速いコード
・時間がかかるとして処理中にプログレスバー表示をするコード
をご呈示下さい。Excelのバージョンは2000-2007です。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2009/04/28 20:32:40
  • 終了:2009/05/05 10:26:00

回答(5件)

id:AZUY No.1

AZUY回答回数343ベストアンサー獲得回数122009/04/28 20:37:04

id:smileless

ありがとうござます。

プログレスバーだけの回答についてはこれで終了とさせて下さい。

本当に質問内容が言葉足らずで申し訳ないです。

プログレスバーの表示はあくまでおまけで、本筋は巨大なファイルを読み込んで処理をするのに処理時間を短くするにはどうしたらよいか、でした。

具体的なコードを示し、ご教授いただきたいということろこですのでよろしくお願いいたします。

一応、

OFFICE TANAKA:VBA高速化テクニック

http://officetanaka.net/excel/vba/speed/index.htm

にあるようなテクニックは使用しているのですが、それでも遅いので、ファイルの読み方そのものに何か工夫があるのではないか?と質問させていただきました。

(逆にプログレスバー処理を入れることで遅くなるのであればそれは外そうともかんがえています)

よろしくお願いいたします。

2009/04/28 20:45:55
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912009/04/28 22:07:14

ポイント40pt

現在の「大変時間がかかっている」がどの程度か分かりませんが、

下記のようなコードでどうでしょうか。

Sub loadLargeFile()
    Const data_file = "C:\test.txt"
    Const temp_file = "C:\temp.csv"
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Data = fso.OpenTextFile(data_file).ReadAll()

    Dim dlen As Long, dpos As Long
    dlen = Len(Data)
    
    Dim outFile As Object
    Set outFile = fso.CreateTextFile(temp_file, True)
    For dpos = 1 To dlen Step 500
        outFile.WriteLine """" & Mid(Data, dpos, 500) & """"
    Next
    outFile.Close
    
    Workbooks.Open temp_file
End Sub

先頭のファイル情報を変更して、お試しください。

7Mでも数秒で終わるので、プログレスバーは実装しませんでした。


http://msdn.microsoft.com/ja-jp/library/cc428071.aspx

id:smileless

大変参考になりました。

ありがとうございました。

やはり、いろいろなタイプのコードを見るのは勉強になりますね。

2009/04/28 22:39:54
id:jccrh1 No.3

jccrh1回答回数111ベストアンサー獲得回数192009/04/28 22:29:01

ポイント20pt

以下の条件で対応しました。

・入力ファイルはEXCELと同一フォルダーに入っているものとしました。

・レコード数は14,000件にしました。

・データはすべて半角で対応しました。

処理速度は私のPC(EXCEL2003,VISTA,CPU=P8600)で8~9秒でした。

Option Explicit

Sub 入力処理()
  Const レコード長 = 500
  Dim レコード数 As Long
  Dim レコード   As String * レコード長
  Dim I         As Long
  Dim 進捗率     As Integer
  
  Open ThisWorkbook.Path & "\TEST.TXT" For Random As #1 Len = レコード長
  レコード数 = (LOF(1) / レコード長)
  
  For I = 1 To レコード数
   進捗率 = I * 10 / レコード数
   Application.StatusBar = String(進捗率, "■") & String(10 - 進捗率, "□")
   Get #1, I, レコード
   Range("A1").Offset(I - 1).NumberFormatLocal = "@"
   Range("A1").Offset(I - 1).Value = レコード
  Next I
  
  Close
  Application.StatusBar = False
End Sub

ダミーURL

http://www.hatena.ne.jp/

id:smileless

ありがとうございます。

お手製のステータスバーが非常にいい感じです。(できればコントロールを使いたくないので)

これって、Application.ScreenUpdating = Falseと併用はできるのでしょうか、ちょっと試してみます。

全角混在だとやはりユニコードでmidするしかないのでしょうか。

2009/04/28 22:48:16
id:jccrh1 No.4

jccrh1回答回数111ベストアンサー獲得回数192009/04/29 01:00:02

ポイント20pt

先ほどの変更で全角も対応してあります。

 

> Application.ScreenUpdating = False

これについては、今回全く影響はないので、命令を追加しても意味がないと思います。

 

Option Explicit
 
Sub 入力処理_全角OK()
  Const レコード長 = 500
  Dim レコード数                As Long
  Dim レコード(レコード長 - 1)  As Byte
  Dim I                      As Long
  Dim 進捗率                    As Integer
  
  Open ThisWorkbook.Path & "\TEST.TXT" For Binary As #1
  レコード数 = (LOF(1) / レコード長)
  
  For I = 1 To レコード数
   進捗率 = I * 10 / レコード数
   Application.StatusBar = String(進捗率, "■") & String(10 - 進捗率, "□")
   Get #1, , レコード
   Range("A1").Offset(I - 1).NumberFormatLocal = "@"
   Range("A1").Offset(I - 1).Value = StrConv(レコード, vbUnicode)
  Next I
  
  Close
  Application.StatusBar = False
End Sub

ダミーURL

http://www.hatena.ne.jp/

id:smileless

ありがとうございます。

2009/04/29 09:27:24
id:rolexbaidu No.5

rolexbaidu回答回数64ベストアンサー獲得回数02009/04/29 18:32:33

id:smileless

あー、はてなもこんな回答がつくようになりましたか・・・

誤爆かな?であればかまわないのですが・・

2009/04/29 20:23:44
  • id:Mook
    2000-2007 でということで、複数の環境で実行することを想定されていると思いますが、
    2007 は 2003以前のバージョンとけっこう挙動が異なるので、特に確認しておくことをお勧めします。

    私の環境では、2007で配列を使いデータを読み込む処理は1秒もかかりませんがセルへ展開する
    処理は1分以上かかりました。
    なので、冗長ですがいったん CSV として分割し、ファイルを読み込む処理に変えています。
    データ内に改行や「"」がないのであれば、回答したやり方でできるかと思いますが、
    これらのコードがあると、直接セルに書き込むことになりますので、2003まででは問題なくとも
    2007では時間がかかるので、プログレスバー等が有効かもしれません。
  • id:smileless
    >Mookさん
    私は2007環境で作成しています。やはり環境によって挙動が違うんですね。使用環境はいろいろですので最適な方法を探してみます。
    大変参考になりました。ありがとうございました。
  • id:kn1967
    誤爆ではなく無差別絨毯爆撃。
    最近IDを変えながら何度も行っている悪質なもの。
    他の回答を見てから開けるのがベター。

    ちなみにプログレスバーの出し方は他にもあります。
    (ステータスバーを使うのがスマートだと思うのだけど
    他人が使うものだとするとインパクトにかけて気づかれないかもしれない)
    http://www.h3.dion.ne.jp/~sakatsu/ProgressBarTopic.htm
  • id:smileless
    最後のは0ポイントにしたのに勝手に15ポイントついた・・・orz
  • id:smileless
    いろいろ試してみたのですが、半角文字だけのテキストファイルの場合、皆さんがおっしゃる通り数秒で終わるのですが、半角と全角が混在している場合、LenBやMidBの動きがVBAとワークシート関数で異なるため、下記処理が必要となり、これを通した場合にものすごくおそくなります。
    LenB(StrConv(str, vbFromUnicode))
    StrConv(MidB(StrConv(str, vbFromUnicode), start, length), vbUnicode)
    参考:http://makotowatana.ld.infoseek.co.jp/vba_character.html#bite
    これは何とか回避できないのでしょうか?
    という質問を再度あげてみようと思います。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません