重ねて質問させていただきます。
データ量の多い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
問題のコードを提示してもらえますか。
質問は編集できますので、質問の方に追記してもらうと後々見やすいです。
あと、「切替のダイアログ」とは具体的にどのようなダイアログでしょうか。
の部分のコメント行を下記のように入れ替えたらどうなりますか。
普通に定義済みクエリを指定してレコードセットを開くと問題ないです。
一方、サブフォームのRecordsetConeプロパティを元にレコードセットを開くと動かなくなりますね。
なんとなくその辺があやしそうそうな気がしたのですか、当たったようですね。
というようにしてみたらどうだろう。
RecordsetCloneとRecordset.Cloneの違いは下記を参照してください。フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは？ - hatena chips
アドバイスありがとうございます。
月曜日に試してみます。
すみません、もう1点教えてください。
MyFocusという関数で、ScreenUpdating 、Calculation等のプロパティを設定していますが、
タイミングの違いで、DisplayAlertだけこのMyFocusという関数で定義できず、スマートな
コーディングができません。
確か、CalculationプロパティはBookを開いてから閉じる間に書かなければならないが、
DisplayAlertはBookを保存する前後に入れなければならなかったと思います。
この点アドナイスいただけますと幸いです。
よろしくお願いいたします。
簡単なサンプルを作成して、実験してみました。
でも、問題なく動作しました。
うまくいかない原因は他にあると思われます。
提示のコードですが、行儀のいいコードではないので、下記の手順で修正してください。
そうすることで、不具合の原因を追究しやすくバグの発生しにくいものになります。
まず、VBAウィンドウのメニューの[ツール]-[参照設定]をクリックして
Microsoft Excel 16.0 Object Library（数字部分はバージョンによって異なる）にチェックを入れて[OK]をクリックしてください。[ツール]-[オプション]をクリックして開くダイアログで[編集]タブの[変数の宣言を強制する]にチェックを入れて[OK]をクリック。
提示のコードが記述してあるモジュールの先頭に下記の１行を挿入。（既にある場合はそのままで）
これの意味は変数を使う場合は必ず宣言することを強制します。
ここでメニューの[デバッグ]-[`*****`のコンパイル]をクリックします。(
*****はプロジェクト名)もし、構文間違いや変数宣言がないとその部分を指摘してくれます。
提示のコードには、xlApp、xlBook の変数宣言がないので、ここでエラーが出るはずです。
でないなら、モジュールの先頭か、標準モジュールの先頭で宣言しているはずです。
その変数宣言を削除してください。（そのような場所で宣言する変数をグローバル変数といいますがこれはバグの元ですのでなるべく使用しないようにします。）
クリック時のイベントプロシージャの先頭で、この変数の宣言をします。
Dim xlApp As Objectというように宣言することもできますが、上記のように宣言するのを推奨します。そうすると
xlApp.まで入力するとプロパティやメソッドがリスト表示されますので入力が楽になるしタイプミスが減らせます。以上、を試してみてください。
お返事遅くなりまして、申し訳ございません。
Set rs = Me.subForm.Form.Recordset.Clone
にしたらうまく動作しました。
ご指摘の点も注意きたします。