ACCESS VBAでExcel出力

Question

###前提・実現したいこと
Access VBAでExcel出力をしようとしています。
対象レコードが3万件ほどあり、出力にかなり時間が掛かってしまっています。
今はセル一つずつに書き込んでいるので、遅くなっていると思うのですが、
速度改善の方法として、レコードセットの内容をまとめて書き込む方法等ありましたら
ご教授頂けますでしょうか？

###該当のソースコード

Dim sql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim tmpField As ADODB.Field
Dim app As Excel.Application
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim fName As String
Dim data() As String
Dim cnt As Integer
Dim rows As Long
Dim cols As Integer

Set cn = CurrentProject.Connection
fName = CurrentProject.Path & "\export\PV数（月別）_" & Format(Now, "yyyymmddHHMMSS") & ".xlsx"
Set app = CreateObject("Excel.Application")
app.Visible = False
Set book = app.Workbooks.Add
Set sheet = book.Worksheets(1)
sheet.Name = "DATA"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
sql = "SELECT * FROM PV数"
rs.Open sql, cn, adOpenDynamic, adLockOptimistic

rows = 8
Do Until rs.EOF
    cols = 1
    For Each tmpField In rs.fields
        If rows = 8 Then
            sheet.Cells(rows - 1, cols).Value = tmpField.Name
        End If
        sheet.Cells(rows, cols).Value = tmpField.Value
        cols = cols + 1
    Next

    rows = rows + 1
    rs.MoveNext
Loop
cn.Close
Set cn = Nothing
Set rs = Nothing

book.SaveAs (fName)

app.Quit
Set book = Nothing
Set app = Nothing

###試したこと
ループの部分を以下のように変更して、一行ずつ配列に入れてから書き込むようにしました。
速度改善の効果は出たのですが、出力したエクセルを開いてみると、全てのセルが文字列扱いになってしまい、
数値のセルも文字列として扱われてしまっています。
エクセル上である列をオートSUMしたりしたいのですが、文字列のためできなくなってしまいました。
書き込む際にこのセルは数値として指定したりできるのでしょうか？

Do Until rs.EOF
cols = 1

cnt = 0
ReDim data(cnt)
If rows = 8 Then
    For Each tmpField In rs.fields
        ReDim Preserve data(cnt)
        data(cnt) = tmpField.Name
        cnt = cnt + 1
    Next
    sheet.Range(sheet.Cells(rows - 1, 1), sheet.Cells(rows - 1, UBound(data) + 1)) = data
End If

cnt = 0
ReDim data(cnt)
For Each tmpField In rs.fields
    ReDim Preserve data(cnt)
    If IsNull(tmpField.Value) Then
        data(cnt) = ""
    Else
        data(cnt) = tmpField.Value
    End If
    cnt = cnt + 1
Next
sheet.Range(sheet.Cells(rows, 1), sheet.Cells(rows, UBound(data) + 1)) = data

rows = rows + 1
rs.MoveNext

Loop

###補足情報(言語/FW/ツール等のバージョンなど)
より詳細な情報

Answer 1

解決済みですけど、処理方法によって速度は異なるようです。
さまざまなエクスポート方法の比較

Answer 2

Office TANAKA - Excel VBA高速化テクニック[無駄な表示を止める]
が最有力かなぁ。その他、高速化テクニックのまとめは
Office TANAKA - Excel VBA高速化テクニック[目次]
など。Withステートメントは地味に効いてくる。
あと、Excel.Applicationのインスタンス自体を非表示にするのもアリ。

Office TANAKA - Excel VBA高速化テクニック[型を指定する]
も重要。郵便番号の文字列を普通に書き出すと先頭の0が欠落したり、
電話番号の先頭の0が欠落したりするのを防げる。

Excelに計算式を埋め込んであるなら特に、
再計算を止めるのも有効。詳しくは：
遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法

Comments

[MotohiroSuzuki]

ありがとうございます。

まだ試せてませんが、速度改善の参考サイト非常に参考になりました。
明日になってしまうと思いますが、実際に試してみて結果をご報告させて頂きます。

Answer 3

CSV で出力して Excel で開けばいいと思います。