Access VBA Docmd.TransfferSpreadsheet でクエリがエクスポートできない。

Question

後述のコードの問題点を教えてください。宜しくお願い致します。

この部分でエラーが出ます。

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
                              varAc, varXls, True

エラーの内容は次の通りで、エクスポートすることができません。

「3011 : オブジェクト 'Q_児童名簿（地区・コード）' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'Q_児童名簿（地区・コード）' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。」

###試したこと・確認したこと

• Acceessのクエリ「Q_児童名簿（地区・コード）」が存在していて、Accessでは実行できていること。
• 出力先のExcelファイルをxls形式にして、acSpreadsheetTypeExcel9にするとエクスポートできる。

Public Sub 児童名簿_地区別_出力()
   
    Dim objExcel As Excel.Application
    Set objExcel = New Excel.Application
    Dim objWb As Excel.Workbook
    
    Dim varXls As Variant
    Dim varAc As Variant

    varXls = Application.CurrentProject.Path & "\temp\" & "児童名簿（地区別）.xlsm"
    varAc = "Q_児童名簿（地区・コード）"
   
    Set objWb = objExcel.Workbooks.Open(FileName:=varXls, ReadOnly:=False, _
                                        Password:=PASSWORD_STRING)
    
  
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
                              varAc, varXls, True

    objWb.Save
    objWb.Close
    Set objWb = Nothing
    objExcel.Quit
    Set objExcel = Nothing
                                
End Sub

こちらのコードでは理想の動きをしました。理由は判りません。

Public Sub 児童名簿_地区別_出力()

    Dim objExcel As Excel.Application
    Set objExcel = New Excel.Application
    Dim objWb As Excel.Workbook
    
    Dim varXls As Variant
    Dim varAc As Variant

    varXls = Application.CurrentProject.Path & "\temp\" & "児童名簿（地区別）.xls"
    varAc = "Q_児童名簿（地区・コード）"

    Set objWb = objExcel.Workbooks.Open(FileName:=varXls, ReadOnly:=False, _
                                        PASSWORD:=PASSWORD_STRING)
    
    objWb.PASSWORD = ""
    objWb.Save
    objWb.Close
    Set objWb = Nothing
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                              varAc, varXls, True

    Set objWb = objExcel.Workbooks.Open(FileName:=varXls, ReadOnly:=False, _
                                        PASSWORD:="")
    objWb.PASSWORD = PASSWORD_STRING
    objWb.Save
    objExcel.Quit
    Set objWb = Nothing
    
    Set objExcel = Nothing
                                
End Sub

Answer 1

Q_児童名簿（地区・コード）　→　Q_児童名簿_地区・コード

クエリ名に全角括弧が入っていたため、エラーを吐いていたようです。全角括弧を取り除いたところ予定通りの動作をしました。

Answer 2

エクスポート対象のexcelファイルを開いたまま、同じファイル名でエクスポートしようとしているからではないでしょうか？
何のためにエクスポート前にexcelファイルを開いているんでしょうか？

    ' Set objWb = objExcel.Workbooks.Open(FileName:=varXls, ReadOnly:=False, _
    '                                     Password:=PASSWORD_STRING)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
                              varAc, varXls, True

    ' objWb.Save
    ' objWb.Close
    ' Set objWb = Nothing
    ' objExcel.Quit
    ' Set objExcel = Nothing

Comments

[wakame01]

excelファイルにパスワードを設定してあるので、開いた状態でないとエクスポートできないとのことです。

[Y.H.]

そうなんですね。不思議な環境ですね。私の環境では無理でした。

acSpreadsheetTypeExcel9 のコードのほうは
・「Excelで開いてパスワード解除しExcel 終了」→「TransferSpreadsheet でexport」→「Excelで開いてパスワード設定しExcel 終了」
acSpreadsheetTypeExcel12Xml では
・「Excelで開いて」→「TransferSpreadsheet でexport」→「Excel 終了」