AccessからExcelデータの加工・出力時間が長い

Question

前提

お世話になっております。

Microsoft access 365で
データベース構築をしています。

AccessVBAからExcelデータをエクスポートするのですが
出力したExcelを開くと関数として表示したかったセルが
テキストとして出力され自動計算されません。
(出力されたExcelデータの該当セルをTYPE関数で見ると
論理値(4)ではなくテキスト(2)でした)

そこで別の質問にてお聞きしたところ
(https://teratail.com/questions/ubyo6wcepiu0ea)
CreateObject("Excel.Application")から
Excelオブジェクトでの編集を提案して頂き
そちらを実施しました。
確かにテキストだったものが関数として出力されましたが
データ出力までに１分ほどかかります。
(前回質問の回答の意図がくみ取れておらず
適切なプログラムでないかもしれません。)
そこで別の方法がないかお聞きしたいです。

実現したいこと

AccessからExcelデータを出力するのに
関数を記載したテキストが論理値として出力される
もしくはテキストとして出力後
論理値として変換される。
これを出力時間30秒程度で実現したいです。

該当のソースコード

前回質問時(変更前)のテキストとして出力される
関数部分(処理1～3)のプログラムでの記載内容が以下です。

Do Until rsa.EOF
        j = j + 1
        If rsa!間口 <> Null Or rsa!間口 <> "" Then
            i = i + 1
            rsa!照合1 = "1"
            rsa!照合2 = "2"
            rsa!処理1 = "=IF(E" & j & "=L" & j & ",TRUE,FALSE)"
            rsa!処理2 = "=IF(MID(M" & j & ",11,15)=E" & j & ",TRUE,FALSE)"
            rsa!処理3 = "=IF(MID(M" & j & ",27,1)=F" & j & ",TRUE,FALSE)"
　　　　　　　rsa.Update
        End If
        rsa.MoveNext
    Loop

上記のレコードセット記載の内容を追加したテーブルを
Excelテンプレートに出力します。
上記のコードが記載し下記のプログラムで出力されたExcelデータを開き、
該当セルを選択→enterで確定してみるとテキストから関数として認識されます。

    Const csOutputTemplate As String = "出力テンプレート.xltx"
    strFileName = pass & "\" & name & Format(day, "yyyymmddhhmm") & ".xlsx" 
    Set xlapp = CreateObject("Excel.Application")  
    
    Set myCn = CurrentProject.Connection  
    strsql = "SELECT * FROM checksheet WHERE ((間口)>0) ORDER BY ID"
    myRs.Open strsql, myCn, adOpenForwardOnly, adLockReadOnly
    
    With xlapp
        strTemplate = Application.CurrentProject.Path & "\" & csOutputTemplate 
        .Workbooks.Open strTemplate
        .Cells(5, 1).CopyFromRecordset myRs     '結果値出力処理(ヘッダー位置調整)
        .ActiveWorkbook.SaveAs FileName:=strFileName
    End With

論理値として出力されますが出力に1分ほどかかるプログラムが以下です。
こちらでは上記のレコードセットの書き込みはせず
Excelテンプレートで出力後にobjWs.Cells(, ).Valueを
利用してAccessからExcelへ書き込んでいます。
この処理に変えた途端出力時間が長くなったように思うので
別の方法があれば教えて頂きたいです。

    Dim objExcel As Object, objWb As Object, objWs As Object
    
    Set objExcel = CreateObject("Excel.Application")
    Set objWb = objExcel.Workbooks.Open(pass)
    Set objWs = objWb.Worksheets("Sheet1")
    
    Dim wb  As Workbook
    Set wb = ActiveWorkbook
    
    Dim j As Integer 
    
    objWs.Cells(2, 18).Value = Now()

   For j = 5 To max
        If objWs.Cells(j, 9).Value <> "" And objWs.Cells(j, 6).Value <> "対象外" Then
            objWs.Cells(j, 13).Value = "1"
            objWs.Cells(j, 14).Value = "2"
            objWs.Cells(j, 15).Value = "=IF(E" & j & "=L" & j & ",TRUE,FALSE)"
            objWs.Cells(j, 16).Value = "=IF(MID(M" & j & ",11,15)=E" & j & ",TRUE,FALSE)"
            objWs.Cells(j, 17).Value = "=IF(MID(M" & j & ",27,1)=F" & j & ",TRUE,FALSE)"
        End If
    Next j
    
    objWb.Save
    Set objWs = Nothing
    Set objWb = Nothing

    objExcel.Quit 
    Set objExcel = Nothing

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

Comments

[ukana]

出力時間30秒程度と具体的な理想を
書いてしまいましたが
プログラムの全体像を書いておらず
環境にも依存するので
現状の出力速度より早くなるような
方法ありましたら教えて頂きたいです。

Answer 1

エクセルの Range.Value プロパティに再代入すれば、”=”で始まるテキストは数式として認識されます。

    With xlapp
        strTemplate = Application.CurrentProject.Path & "\" & csOutputTemplate 
        .Workbooks.Open strTemplate
        .Cells(5, 1).CopyFromRecordset myRs     '結果値出力処理(ヘッダー位置調整)
        Dim rngTbl As Range
        Set rngTbl   = .Cells(5, 1).CurrentRegion 'テーブル範囲
        '↓数式の列のテキストを数式に変換、列は実際の数式の列に変更してください。
        rngTbl.Column(3).Value = rngTbl.Column(3).Value
        rngTbl.Column(4).Value = rngTbl.Column(4).Value
        rngTbl.Column(5).Value = rngTbl.Column(5).Value
        .ActiveWorkbook.SaveAs FileName:=strFileName
    End With

Comments

[ukana]

返信が遅くなり申し訳ありません。回答ありがとうございます。
教えて頂いた通りプログラムを記載したつもりなのですが
rngTbl.Column(15).Value = rngTbl.Column(15).Value
上記の.Columnの部分で引数の数が一致していませんという
コンパイルエラーが出てしまいました。そこで
rngTbl.Range("O5:O1000").Value = rngTbl.Range("O5:O1000").Value
rngTbl.Range("P5:P1000").Value = rngTbl.Range("P5:P1000").Value
rngTbl.Range("Q5:Q1000").Value = rngTbl.Range("Q5:Q1000").Value
とするとエラーは消えて出力されたのですが
O5：Q7の範囲のみテキストになっています。
範囲の指定が間違っているのか原因がわかりません。
教えて頂けると嬉しいです。

[hatena19]

すみません。Column を Columns に変更してください。
それでだめなら、下記も試してみてください。

rngTbl.Columns(15).Formula = rngTbl.Columns(15).Value

[ukana]

すみません、こちらのプログラムが一部ちがっていたので修正したのですが
編集のタイミングが重なってしまいました。
rngTbl.Columns(15).Formula = rngTbl.Columns(15).Value
こちら試してみます。

[ukana]

rngTbl.Columns(15).Formula = rngTbl.Columns(15).Value
rngTbl.Columns(16).Formula = rngTbl.Columns(16).Value
rngTbl.Columns(17).Formula = rngTbl.Columns(17).Value
上記に書き換えたところ、Excelに数式として出力されました！
処理も以前は1分以上かかっていたものが10数秒で処理が完了しました。
再代入という発想は全くなく、一人では解決していなかったと思います。
大変たすかりました。ありがとうございました。