Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2016)
パラメタクエリのDoCmd.TransferSpreadsheet
投稿日時: 17/10/06 19:18:16
投稿者: Masaka

クエリ概要
クエリ1:ヘッダー(不変)
クエリ2:データ(パラメタ)
クエリ3:クエリ1とクエリ2のユニオン
 
'クエリの定義
Set qdf = currentdb.CreateQueryDef("Q_MAP", "Select * from クエリ3")
 
'クエリデフにパラメタを渡す
qdf.Parameters("prm") = 変数
 
'エクスポートの実行
DoCmd.TransferSpreadsheet acExport, , "Q_MAP", excellPath, False, ""
 
エクスポート実行で、パラメタを聞かれてしまいます。
 
クエリデフからレコードセットを取得する方法で、
copyfromrecordsetで書き出す方法もありますが、
このエクスポートを120回も繰り返す必要があるため、
パフォーマンスのよいDoCmd.TransferSpreadsheetを使いたいと思っています。
 
よろしくお願いいたします。

回答
投稿日時: 17/10/08 17:50:46
投稿者: Suzu

こんにちは。
 

引用:
qdf.Parameters("prm") = 変数

は、DAO でのパラメーターを渡す操作であり、その操作は TransferSpredSheet には反映されません。
ですので、CopyFromRecordSet でレコードセットから当該レコードセットを取得し
オートメーション操作のExcelワークブックへ貼り付ける事になります。
 
ここで、120回も繰り返すと言うのがパラメーターを120種渡すという事ですよね。
 
パラメーターをどの様にSQLに埋め込んでいるのか判りませんが
演算フィールドへの埋め込みではなく、単に抽出条件としているのであれば
クエリ1 + クエリ2(パラメーター無し)のユニオンクエリをOpenRecordSertにて開き
抽出条件を RecordSetのFilterプロパティーに渡し、その状態でOpenRecordSetをする事で
当該抽出条件で抽出したレコードセットを得ることができます。
そのレコードセットに対し CopyFromRecordSet を行います。
 

Dim db As DAO.Database
Dim rsPrm As DAO.Recordset
Dim rsAll As DAO.Recordset
Dim rsFilter As DAO.Recordset

Dim xls As Excel.Application
Dim wbk As Excel.Workbook

Dim i As Long
Dim valFieldName As Variant

Set db = CurrentDB
Set rsPrm = db.OpenRecordSet("渡すパラメーターの一覧を得ることのできるSQL")
Set rsAll = db.OpenRecordSet("ユニオンクエリ", dbOpenDynaset)

Set xls = CreateObject("Excel.Application")

ReDim valFieldName(0, rsAll.Fields.Count - 1)
xls.Visible = True
For i = 0 To rsAll.Fields.Count - 1
  valFieldName(0, i) = rsAll.Fields(i).Name
Next

Do While Not rsPrm.EOF
  rsAll.Filter = "フィールド = " & rsPrm![パラメーター]
  Set rsFilter = rsAll.OpenRecordSet
  If Not rsFilter.EOF Then
    Set wbk = xls.Workbooks.Add
    wbk.Worksheets(1).Range(wbk.Worksheets(1).Cells(1, 1), wbk.Worksheets(1).Cells(1, i)) = _
                       valFieldName
    wbk.Worksheets(1).Range("A2").CopyFromRecordset rsFilter
    wbk.SaveAs "C:\〜〜\" & rsPrm![パラメーター] & ".xlsx"
    wbk.Saved = True
    wbk.Close False
  End If
Loop

Set wbk = Nothing
xls.Quit: Set xls = Nothing
rsAll.Close: Set rsAll = Nothing
rsPrm.Close: Set rsPrm = Nothing

投稿日時: 17/10/08 21:04:35
投稿者: Masaka

Suzさん、こんばんは。
 
ご回答ありがとうございます。
 
引用:
クエリデフにパラメタを渡すというコーディングは、
DAO でのパラメーターを渡す操作であり、その操作は TransferSpredSheet には反映されません。
です。
 
なるほど、これはできないということですね。
 
引用:
CopyFromRecordSet でレコードセットから当該レコードセットを取得し
オートメーション操作のExcelワークブックへ貼り付ける事になります。
 
ご質問ほ通り、パラメタを120回渡し、EXCELファイルを120個生成する
必要があります。
 
 何度かパラメタを渡すコーディングを施工さがしている中で、
解決の糸口がどうしても見つからずに、
オートメーションでエクセルのインスタンスを生成して、
CopyFromRecordSetで貼り付ける方法もやりました。
実際できましたが、処理時間がかかり過ぎてしまい、
実用に耐えないと感じていました。
 
あれからいろいろ考えていましたが、こういうアプローチはどうか?と今考えています。
(休日につきAcceceの環境がないので週明けまで試せません)
 
パラメタクエリを使わずに、ユニオンクエリに対してwhere条件でクエリデフのSQLの設定
 
Do until rs.EOF
 
    'クエリの定義
    Set qdf =currentDB.CreateQueryDef("Q_Map","select ※ from クエリ1 union select ※ from クエリ2 " )
 
    'SQL定義(where条件として)
    strSQL="select ※ from クエリ1 union select ※ from クエリ2 where Manager =' " & rs!変数 & "'")
    
    "クエリデフにSQLを設定
     currentDB.QueryDefs!["Q_Map"].SQL=strSQL
 
    'エクスポート実行
    Docmd.transferspreadsheet acexport,,"Q_Map",excellpath,false,""
 
    rs.Movenext
 
Loop
 

投稿日時: 17/10/10 10:43:11
投稿者: Masaka

パラメタの問題は、上記のSQLで解決しました。
ExcelへのCopyFromRecordsetでの転記で7〜8分かかっていた処理が数秒で対応できました。
 
しかしながら、別の問題がありました。
DoCmd.TransferSpreadsheet acExportを使ってパフォーマンスよくエクスポートできましたが、
HasFieldNamesの引数では、1行目の不用なフィールド名の行を出す・出さないの選択はできないようです。
※HasFieldNames:
 Access テーブルや選択クエリのデータをエクスポートした場合は、この引数の値に関係なく、
 ワークシートの 1 行目にフィールド名が挿入されます。
 
Output後、改めて120個のファイルをオートメーションで開いて1行目を削除するという方法で試してみますが、
あまりやりたくない方法です。もっと気の利いた方法がないものでしょうか?
 
ExcelへのOutputイメージ
--クエリ1、クエリ2共通のフィールド名-------------------------------------------------------------------------------
(フィールド名)フィールド20 フィールド21 フィールド22 フィールド23 フィールド24 フィールド25
--クエリ1のデータ------------------------------------------------------------------------------------------------------
(ヘッダ)toeic 経営戦略
(ヘッダ)得点 スコア ランク 得点 スコア ランク
--クエリ2のデータ------------------------------------------------------------------------------------------------------
(データ)18 540 D 25 695 A
(データ)10 340 F 19 552 C
(データ)23 665 A 25 695 A
 ※Where条件をManagerの条件を設定しているので、不要なフィールド名は除外されている。

投稿日時: 17/10/10 18:06:23
投稿者: Masaka

出来上がった120個のエクセルファイルの1行目を全て削除しても、
1分も掛からない処理なので、これで解決としたいと思います。
 
ありがとうございました。