アクセスでのEXCELファイルからのシートインポート高速化について

Question

ACCESSでアプリ開発をしています。

2000個ほどの、EXCELファイルの中から、シートをIMPORTしていますが、
ものすごく時間がかかっています。

EXCELシートからの取り込みに関して、もっと良い方法があれば教えていただきたいのですが

現状のソースを下記に示します。

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTblName, strPath, True, sheet & "!"

Public TableNames As Collection
Private qdf As DAO.QueryDef
Dim myRS As DAO.Recordset
Private sql As String
Private xlPath As String

Public Sub tablename()
Dim FSO As FileSystemObject
Dim filenum As Variant
Dim table As Variant
Dim FIO As ClsFileIO
Set FIO = New ClsFileIO
Dim bar As Variant: Set bar = New clsProgressBar
Dim count_all
Dim count
count_all = TableNames.count

For Each filenum In TableNames
With filenum
Dim tbln
tbln = StrConv(.tbl, 4)
FIO.Import .Path & "\" & .File, "Lot結果", "Lot結果"
bar.UpdateProgress count, count_all, filenum.File, Form_frmProgressBar
count = count + 1
End With
Next

End Sub



Public Sub Import(strPath As Variant, strTblName As Variant, sheet As Variant)
On Error GoTo err

Select Case OdbcSelect(strPath)
Case ACCESS
    DoCmd.TransferDatabase acImport, "Microsoft Access", _
            strPath, acTable, strTblName, strTblName
    Exit Sub
    
Case EXCEL
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTblName, strPath, True, sheet & "!"

    Exit Sub
Case Else
End Select

err: MsgBox "テーブル構造が違います"

End Sub

読み込むシートは下記のようなデータで、４００行程度です。

L1シートNo 基板連番 YMP測定No 個片No NG判定 ブロックNo
10311M87U010254 1 1 1 4WNG #N/A
10311M87U010254 1 2 28 4WNG #N/A
10311M87U010254 1 3 55 4WNG #N/A
10311M87U010254 1 4 82 4WNG #N/A
10311M87U010254 1 5 2 4WNG #N/A
10311M87U010254 1 7 56 4WNG #N/A
10311M87U010254 1 8 83 4WNG #N/A
10311M87U010254 1 15 58 4WNG #N/A
10311M87U010254 1 16 85 4WNG #N/A

Answer 1

エクセルファイルをテキストとして読むなんて、無茶してますね。
正しく読めているんでしょうか？

リンクするかインポートして扱う方が高速でしょうし、処理も簡潔になるかと思います。
Excel ブックのデータのインポートとリンクの設定
DoCmd.TransferSpreadsheet メソッド

Comments

[yuujiMotoki]

大変申し訳ありません。添付すべき箇所を間違えておりました。DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTblName, strPath, True, sheet & "!"　。このコマンドが非常に時間がかかっている状態です。

[sazi]

４００行程度でどの程度時間がかかっているのですか？
またインポートされた結果のテーブルは何行位ですか？
普通は数千件でも数秒でインポートは完了すると思いますので、コマンド単独での処理時間の計測をお勧めします。

[yuujiMotoki]

先ほどの回答から、すぐに走らせて見た結果です

1　　　11:45ごろ

1995 2018/10/30 12:49:26
1996 2018/10/30 12:49:30
1997 2018/10/30 12:49:34
1998 2018/10/30 12:49:38
1999 2018/10/30 12:49:40

２０００件で1時間ちょっとでしたので、1件あたり2秒程度になっている見込みです

[sazi]

そのログがファイル単位というなら、所要時間の解釈がおかしいですし、１件単位というなら、DoCmd.TransferSpreadsheet はファイル単位の処理ですから、コマンドの処理時間ではないですね。
インポートした後の処理に時間が掛かっているんじゃないですか。

[yuujiMotoki]

ありがとうございます。コマンド単位の解釈がよく分からなかったもので。コマンド前後でのnow()表示を見てみます。