Microsoft Access 掲示板

EXCELオートメーション タイムアウト

8 コメント
views
4 フォロー

重ねて質問させていただきます。

データ量の多いEXCELファイルへのデータ出力(copyfromrecordsetメソットなて)をする際に、タイムタウトになってしまってフリーズしてしまっている?ようです。

デバッグしてみると、copyfromrecordsetメソッドの行でフリーズします。

visbleプロパティをtrueにしてデバッグしてみると、切替のダイアログが表示されて、切替をすると動いてくれます。

現状、このあたりまでの状況把握しかできておらず、原因は解明できてはいませんが、なにか手立てはありますでしょうか?

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

2019/6/14 12:50分だ更新
コードは下記の通りです。

やはり、copyfromrecordsetメソッドの行でフリーズします。

切替ダイアログボックスはその後は表示されなくなってしまいました。

Private Sub Excel出力_Click()

    Dim strFilePath As String
    Dim strTitle As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim cnt As Integer
    
    'ファイル選択ダイアログボックスのパラメタ設定
    strTitle = "ファイルを選択してください。"

    'ダイアログボックスで取得したファイルパスの設定
    strFilePath = GetFileName(strTitle, "C:")
    
    'マススポインタを待機モードに変換
    DoCmd.Hourglass False
        
    'Excelのインスタンスを生成
    Set xlApp = CreateObject("Excel.Application")
    
    'テンプレートファイルを開く
    Set xlBook = xlApp.Workbooks.Open(strFilePath)

'    xlApp.Visible = True
    
    '画面描画等抑止
    MyFocus (True)
    
    'WorkSheetを指定
    Set xlSheet = xlBook.Worksheets("サンプル")
    
    'カレントデータベースを開く
    Set db = CurrentDb()
    
    'レコードセットを開く
'    Set rs = db.OpenRecordset("サンプルクロス集計クエリ")

    Set rs = Me.subForm.Form.RecordsetClone

    'データ貼り付け
    With xlSheet
        
        'データ初期化
.Range("A1").CurrentRegion.ClearContents
        
        'ヘッダ出力
        For cnt = 1 To rs.Fields.count
        
            .Cells(1, cnt).Value = rs.Fields(cnt - 1).Name
            
        Next
        
        'データ出力
        .Range("A2").CopyFromRecordset rs
        
    End With
    
    'レコードセットを閉じる
    rs.Close
    
    'データベースを閉じる
    db.Close
        
    '画面描画等抑止解除
    MyFocus (False)
    
    xlapp.DisplayAlert=false

    'Excelを保存して閉じる
    xlBook.Close , True

    xlapp.DisplayAlert=True

    'Excelを終了する
    xlApp.Quit
    
    'マススポインタを通常モードに変換
    DoCmd.Hourglass True
    
    MsgBox "終了いたしました。", vbInformation
    
End Sub

Public Function MyFocus(Flag As Boolean)

    With xlApp
    
        .ScreenUpdating = Not Flag
        .Calculation = IIf(Flag, xlCalculationManual, xlCalculationAutomatic)
        .EnableEvents = Not Flag
        
    End With
    
End Function
みずき
作成: 2019/06/13 (木) 22:09:26
最終更新: 2019/06/14 (金) 16:33:58
通報 ...
1

問題のコードを提示してもらえますか。
質問は編集できますので、質問の方に追記してもらうと後々見やすいです。

あと、「切替のダイアログ」とは具体的にどのようなダイアログでしょうか。

2
    'レコードセットを開く
'    Set rs = db.OpenRecordset("サンプルクロス集計クエリ")

    Set rs = Me.subForm.Form.RecordsetClone

の部分のコメント行を下記のように入れ替えたらどうなりますか。

    'レコードセットを開く
    Set rs = db.OpenRecordset("サンプルクロス集計クエリ")

'    Set rs = Me.subForm.Form.RecordsetClone
3
みずき 2019/06/14 (金) 16:46:28 a63a9@dbd20

普通に定義済みクエリを指定してレコードセットを開くと問題ないです。

一方、サブフォームのRecordsetConeプロパティを元にレコードセットを開くと動かなくなりますね。

4

一方、サブフォームのRecordsetConeプロパティを元にレコードセットを開くと動かなくなりますね。

なんとなくその辺があやしそうそうな気がしたのですか、当たったようですね。

   Set rs = Me.subForm.Form.Recordset.Clone

というようにしてみたらどうだろう。

RecordsetCloneRecordset.Clone の違いは下記を参照してください。

フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは? - hatena chips

5
みずき 2019/06/14 (金) 21:46:23 5ec36@d6973

アドバイスありがとうございます。
月曜日に試してみます。

すみません、もう1点教えてください。

MyFocusという関数で、ScreenUpdating 、Calculation等のプロパティを設定していますが、
タイミングの違いで、DisplayAlertだけこのMyFocusという関数で定義できず、スマートな
コーディングができません。
確か、CalculationプロパティはBookを開いてから閉じる間に書かなければならないが、
DisplayAlertはBookを保存する前後に入れなければならなかったと思います。

この点アドナイスいただけますと幸いです。

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

6
hatena 2019/06/15 (土) 06:45:15 修正

簡単なサンプルを作成して、実験してみました。

Set rs = Me.subForm.Form.RecordsetClone

でも、問題なく動作しました。
うまくいかない原因は他にあると思われます。

7

提示のコードですが、行儀のいいコードではないので、下記の手順で修正してください。
そうすることで、不具合の原因を追究しやすくバグの発生しにくいものになります。

  • まず、VBAウィンドウのメニューの[ツール]-[参照設定]をクリックしてMicrosoft Excel 16.0 Object Library(数字部分はバージョンによって異なる)にチェックを入れて[OK]をクリックしてください。

  • [ツール]-[オプション]をクリックして開くダイアログで[編集]タブの[変数の宣言を強制する]にチェックを入れて[OK]をクリック。

  • 提示のコードが記述してあるモジュールの先頭に下記の1行を挿入。(既にある場合はそのままで)

    Option Compare Database
    Option Explicit    'この行の挿入
    

    これの意味は変数を使う場合は必ず宣言することを強制します。

  • ここでメニューの[デバッグ]-[`*****`のコンパイル]をクリックします。(*****はプロジェクト名)
    もし、構文間違いや変数宣言がないとその部分を指摘してくれます。

  • 提示のコードには、xlApp、xlBook の変数宣言がないので、ここでエラーが出るはずです。
    でないなら、モジュールの先頭か、標準モジュールの先頭で宣言しているはずです。
    その変数宣言を削除してください。(そのような場所で宣言する変数をグローバル変数といいますがこれはバグの元ですのでなるべく使用しないようにします。)

  • クリック時のイベントプロシージャの先頭で、この変数の宣言をします。

Private Sub Excel出力_Click()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook 

    '以下略

Dim xlApp As Object というように宣言することもできますが、上記のように宣言するのを推奨します。
そうすると xlApp.まで入力するとプロパティやメソッドがリスト表示されますので入力が楽になるしタイプミスが減らせます。

  • MyFocus関数はとりあえず削除してください。高速化のためのものですが、今回の処理にはなくても影響は少ないと思います。うまく動作しない場合は、なるべく余計なことはしないようにした方が原因を特定しやすいです。安定して動作するようになってから、処理が重たいと感じたなら、その時に導入すればいいでしょう。

以上、を試してみてください。

8
みずき 2019/06/20 (木) 13:57:59 a63a9@dbd20

お返事遅くなりまして、申し訳ございません。

Set rs = Me.subForm.Form.Recordset.Clone

にしたらうまく動作しました。

ご指摘の点も注意きたします。