Access テンプレートを指定してエクスポート

Question

前提・実現いこと

AccessのVBAでテーブルをExcelにエクスポートするとき、用意されているテンプレート.xlsxのシートを指定してエクスポートしたい。

エクスポートテーブル:T_日報
エクスポートファイル名:日報_yyyymmdd.xlsx
テンプレートExcel:テンプレート.xlsx
※シート名左から：既存,解約,日報

①T_日報をテンプレート.xlsxのテンプレート.xlsxのシート：日報を使って、日報_yyyymmdd.xlsxとして保存したい。
②T_日報のフィールドNoは昇順でエクスポートするようにしたい

発生している問題・エラーメッセージ

①過去の質問などを参考にして、シートを指定しなければエクスポートできるところまではできたのですが、シートの指定の仕方（日報シートを指定したい）が分かりません。
②シートを指定しなければエクスポートできるが、Noの昇順にならない。※順番はバラバラでどのような基準でエクスポートされているのか不明だが、件数と内容は正しくエクスポートできている。

該当のソースコード

    Dim strsql          As String
    Dim strTemplate     As String
    Dim strFileName     As String
    Dim xlapp           As Object
    Dim myCn            As New ADODB.Connection
    Dim myRs            As New ADODB.Recordset
    
    On Error GoTo Err_Exit
    
    csOutPutFileName = Application.CurrentProject.path
    
    
    csOutputTemplate = "テンプレート.xlsx"
    
    
    
    'ﾌｧｲﾙ名作成
    strFileName = csOutPutFileName & "\" & "Export\" & "日報_" & Format(Date, "yyyymmdd") & ".xlsx"


    
    'EXCELｱﾌﾟﾘｹｰｼｮﾝを起動
    Set xlapp = CreateObject("Excel.Application")
    
    'ｾｯﾄする過程が見えないよう一旦不可視
    'xlapp.Visible = False
    
    Set myCn = CurrentProject.Connection
    
    strsql = "SELECT * FROM T_日報"
    
    'ﾚｺｰﾄﾞｾｯﾄｵｰﾌﾟﾝ
    myRs.Open strsql, myCn, adOpenForwardOnly, adLockReadOnly
    
    With xlapp

        'ﾃﾝﾌﾟﾚｰﾄを開く
        strTemplate = Application.CurrentProject.path & "\" & "雛形\" & csOutputTemplate
        
        'ﾃﾝﾌﾟﾚｰﾄﾌｧｲﾙが存在しないときはｴﾗｰ
        If Dir(strTemplate) = "" Then
            MsgBox "テンプレートファイルを確認してください。", vbOKOnly + vbCritical, "エラー"
            .Visible = True
            .Quit
            Exit Sub
        End If
        
        'ﾃﾝﾌﾟﾚｰﾄﾌｧｲﾙｵｰﾌﾟﾝ
        .Workbooks.Open strTemplate
    
        '結果値出力処理(1行目にﾍｯﾀﾞｰを表示しているので、2行目1列目からｾｯﾄ
        .Cells(2, 1).CopyFromRecordset myRs
        
        '完了したら保存
        .ActiveWorkBook.SaveAs FileName:=strFileName
        
        MsgBox "出力しました。", vbOKOnly + vbInformation
    End With
    
    Set myRs = Nothing: Close
    Set myCn = Nothing: Close
    'Excelを終了します
    xlapp.Quit
    Exit Sub
    
Err_Exit:
    MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical, "OutputExcel()"
    Set myRs = Nothing: Close
    Set myCn = Nothing: Close
    xlapp.Quit

End Sub


### 試したこと
csOutputTemplate = "テンプレート.xlsx," True,"日報"
としてみたのですが赤字でエラー
あとは検討もつかずです。どうかご教示いただけないでしょうか。
宜しくお願い致します。


### 補足情報（FW/ツールのバージョンなど）
Access2010
ここにより詳細な情報を記載してください。

Answer 1

②T_日報のフィールドNoは昇順でエクスポートするようにしたい

strsql = "SELECT * FROM T_日報
を下記に修正。
strsql = "SELECT * FROM T_日報 ORDER BY [No]"

Noは予約語なのでそのまま使うとエラーになるので、[ ]で囲む必要があります。

シートの指定の仕方（日報シートを指定したい）が分かりません。

.Workbooks.Openメソッドの返り値で開いたブックが返るのでそれを利用して、WorkSheetsプロパティで参照できます。

'前略
 
       'ﾃﾝﾌﾟﾚｰﾄﾌｧｲﾙｵｰﾌﾟﾝ
        Dim wb As WorkBook
        Set wb = .Workbooks.Open(strTemplate)

        '結果値出力処理(1行目にﾍｯﾀﾞｰを表示しているので、2行目1列目からｾｯﾄ
        wb.WorkSheets("日報").Cells(2, 1).CopyFromRecordset myRs

'後略

Comments

[saku99]

ありがとうございます。Dim wb As WorkBookはエラーとなってしまいました。shinya0320さんのSet excelWorkBook = .Workbooks.Open(strTemplate)とSet excelWorkSheet = excelWorkBook.WorkSheets("日報")で対応できました。
ですが、strsql = "SELECT * FROM T_日報 ORDER BY [No]"はご教示いただいた内容でばっちり動きました。大変助かりました。ありがとうございました。

[hatena19]

Set wb = .Workbooks.Open strTemplate
を下記に修正してください。
Set wb = .Workbooks.Open(strTemplate)

回答も修正しておきました。

Answer 2

AccessVBAでExcelテンプレートへのエクスポートをやったことがないので
適当なことを言うかもですが

strsql = "SELECT * FROM T_日報"

これにOrder Byを指定すればどうでしょう？

Comments

[saku99]

ありがとうございます。
適当どころか大変参考になります。
昇順についてはORDER BY T_日報.No ASC”で対応してみます。
シートを指定してエクスポートについてはご教示いただけますでしょうか。
宜しくお願い致します。

[sinya0320]

> 'ﾃﾝﾌﾟﾚｰﾄﾌｧｲﾙｵｰﾌﾟﾝ
> .Workbooks.Open strTemplate

WorkBook型、WorkSheet型の変数を定義して
Set excelWorkBook = .Workbooks.Open(strTemplate)
と、開いたWorkbookを参照できるようにします。
Set excelWorkSheet = excelWorkBook.WorkSheets(1)
と指定すれば、開いたブックのシートを参照することができます。
シートのインデックスは1から始まる連番です。
# シート名でもアクセスできたような気がしますが、確認してません。

↓のように指定すればできると思います。
excelWorkSheet.Cells(2, 1).CopyFromRecordset myRs

[saku99]

ご教示いただいた内容で解決できました。ありがとうございました。