AccessでExcelファイルを出力する際にエラーが出る

Question

前提・実現したいこと

ExcelのテンプレートにAccessのデータを挿入するシステムを作成しています。
初心者のためネットを参考にしてコードの入力ができたのですがエラーが出てしまいます。
ちなみに参考にしたサイトはこちらです AccessからExcelのテンプレートシートを利用するプログラム例

エラーを回避するにはどうしたらよいでしょうか？

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

実行時エラー1004「申し訳ありません。
C:\Users\name\Desktop\テンプレート,"C:\Users\name\Desktop\テンプレート\テンプレシート.xlsx"
が見つかりません。名前が変更されたか、異動や削除が行われた可能性があります」

と表示されます。

該当のソースコード

デバッグすると下記が黄色になります

'テンプレートファイルを開く
    .Workbooks.Open cstrTemplateDir & cstrTemplateBook

試したこと

ファイルを別の場所に移動したり、名前を変更してみたりしましたが改善されません。
ファイルパスはshift + 右クリックでパスのコピーからそのまま貼り付けているためパスが間違っているわけではないと思います。

補足情報（FW/ツールのバージョンなど）

コードのすべてを記載致します。(パス名などは変更しています)

Public Sub ExcelTemplateSample()

  Dim dbs As Database
  Dim rst As Recordset
  Dim xls As Object
  Dim intRow As Integer
  Dim lngOrderID As Long
  Dim strSaveBookPath As String
  'テンプレートの保存先フォルダ
  Const cstrTemplateDir As String = "C:\Users\name\Desktop\テンプレート"
  'テンプレートのファイル名
  Const cstrTemplateBook As String = "C:\Users\name\Desktop\テンプレート\テンプレシート.xlsx"
  'データが代入されたファイルの保存先フォルダ
  Const cstrSaveBookDir As String = "C:\Users\name\Desktop\"

  'データ元のクエリを開く
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("テンプレクエリ")

  'Excelオブジェクトを生成
  Set xls = CreateObject("Excel.Application")
  With xls
    '画面の再描画を抑止
    .ScreenUpdating = False
    'テンプレートファイルを開く
    .Workbooks.Open cstrTemplateDir & cstrTemplateBook　　'←エラー箇所
    'ワークシートをコピー
    .Workbooks(cstrTemplateBook).Worksheets("原紙").Copy
    'テンプレートファイルを閉じる
    .Workbooks(cstrTemplateBook).Close

    '商品明細の全レコードをループで各セルに代入
    intRow = 10
    Do Until rst.EOF
      .Cells(intRow, 1).Value = rst!番号
      .Cells(intRow, 2).Value = rst!商品名
      .Cells(intRow, 3).Value = rst!種類
      intRow = intRow + 1
      rst.MoveNext
    Loop
    rst.Close

    '保存するファイル名のフルパスを組み立て
    strSaveBookPath = cstrSaveBookDir & "納品書" & ".xlsx"

    '同名ファイルを強制削除
    On Error Resume Next
    Kill strSaveBookPath
    On Error GoTo 0

    'データを代入したブックを保存
    .ActiveWorkBook.SaveAs strSaveBookPath

    '画面の再描画を元に戻す
    .ScreenUpdating = True
    'Excelを終了
    .Quit

  End With
  Set xls = Nothing

End Sub

Answer 1

こうですかね。

Const cstrTemplateBook As String = "テンプレシート.xlsx"

Comments

[zerobemy]

'テンプレートのファイル名
Const cstrTemplateBook As String = "C:\Users\name\Desktop\テンプレート\テンプレシート.xlsx"

この式でしょうか？

[jinoji]

そうです。
テンプレフォルダのパスと、テンプレファイルのフルパスを 結合しているので、
ファイルパスがおかしなことになっているのだと思いました。
hatena19さんの書いている
Debug.Print cstrTemplateDir & cstrTemplateBook
の結果をもう一度確かめてみてください。

以下のどちらかにするのがよいのではと思います。

Const cstrTemplateDir As String = "C:\Users\name\Desktop\テンプレート\"
Const cstrTemplateBook As String = "テンプレシート.xlsx"
'
Workbooks.Open cstrTemplateDir & cstrTemplateBook

または、
Const cstrTemplateDir As String = "C:\Users\name\Desktop\テンプレート"
Const cstrTemplateBook As String = "C:\Users\name\Desktop\テンプレート\テンプレシート.xlsx"
'
Workbooks.Open cstrTemplateBook

[zerobemy]

なるほど・・・
結合になったいたんですね。
本当に助かりました。ありがとうございます。

[jinoji]

すみません。上記で2通り書きましたが、後続の処理を見ると、1つ目のようにしないといけませんでした。
.Workbooks(cstrTemplateBook).Worksheets("原紙").Copy
の cstrTemplateBook のところはフルパスでは駄目なので。
（参考しているサイトでもそうなっていますよね。）

[zerobemy]

指摘頂いたように「"テンプレシート.xlsx"」この部分を完全に見落としていました・・・
ありがとうございます。

訂正し、実行したのですが実行中のまま動かかなくなってしまいました(笑)
エラーは今のところ出ていません・・・
一体何が原因でしょうか・・・

[jinoji]

実行中のまま動かない、というのは、
For ・・ Next や Do ・・ Loop といった繰り返し処理の中が
処理が重い、もしくは間違っている可能性が高いです。
DoEvents という行を、Next や Loop の前に入れておくと、
VBEの画面で「中断」を押すことで途中で止めることができます。
そうやって原因を突き止めていくのがいいと思います。

[zerobemy]

ありがとうございます。
Do eventsの処理について教えて頂きありがとうございます。
Do eventsの使い方は
------------------
Do Until rst.EOF
.Cells(intRow, 1).Value = rst!番号
.Cells(intRow, 2).Value = rst!商品名
.Cells(intRow, 3).Value = rst!種類
intRow = intRow + 1
rst.MoveNext
Loop
---------------------
このコードのLoopの前に入れればよいのでしょうか？
入れてみたところ変わりはなく実行中のままになりました。

[jinoji]

DoEvents自体に問題を解決する効果はありません。
実行中のまま操作できなくなるのを解消するだけです。
（Ctrl＋Pauseで処理を中断できる）
ためしに
intRow = intRow + 1
の後ろに、
If intRow mod 10 = 0 Then Msgbox intRow
DoEvents
と追加してみたらどうなりますか。

Answer 2

下記のようにエラーケ所の前にデバッグ用コードを埋め込んで実行してみてください。

    Set xls = CreateObject("Excel.Application")
    With xls
        '画面の再描画を抑止
        .ScreenUpdating = False
        'テンプレートファイルを開く
        Debug.Print cstrTemplateDir & cstrTemplateBook 'デバッグ用
        Stop                                           'デバッグ用
        .Workbooks.Open cstrTemplateDir & cstrTemplateBook    '←エラー箇所
        'ワークシートをコピー

イミディエイトウィンドウに開こうとしているブックのパスが表示されますので、それを見れば原因はわかると思います。

デバッグの方法は他にもいろいろありますので、WEB検索して調べてみてください。エラーやバグが自力で解決できるスキルがアップします。

Comments

[zerobemy]

回答ありがとうございます。
イミディエイトウィンドウにブックのパスが出てきましたが、開こうとしているExcelのパスが出てきているのでどこがダメなのか分からないです、、

デバッグの方法についてご教授頂きありがとうございます。
このようなデバッグ方法もあると知らず大変勉強になりました。
ありがとうございます。

[hatena19]

C:\Users\name\Desktop\テンプレートC:\Users\name\Desktop\テンプレート\テンプレシート.xlsx
となっていると思いますか、これを見て、おかしいと思いませんか。

[zerobemy]

たしかになっていました・・・
このデバッグをしたのが初めてでしたのでこういう表記なのかと勘違いしていました。。
すごく勉強になり助かりました！！

エラーが移り次は、
'ワークシートをコピー
.Workbooks(cstrTemplateBook).Worksheets("原紙").Copy
が黄色くなりました・・・
実行時エラー9「インデックスが有効範囲にありません」と出ます。
このエラーもdebug.printで調べればよいのでしょうか？

[hatena19]

cstrTemplateBook の中身をdebug.printで確認してもいいですが、コードを見れば、
C:\Users\name\Desktop\テンプレート\テンプレシート.xlsx
ということは分かりますよね。
次に 「Excel VBA Workbooks」のキーワードでWEB検索してみましょう。解説ページが見つかるので、それを読めば、上記のようにフルパスでファイル名を指定するのは間違いだと分かると思います。
例えば、グーグルで最初に出てくるのが、下記のページです。
https://www.officepro.jp/excelvba/book/index1.html

[zerobemy]

調べ方まで教えて頂き本当にありがとうございます。
また一から勉強し直したいと思います。
C:\Users\name\Desktop\テンプレート\テンプレシート.xlsx
↓
テンプレシート.xlsx
と変更したところエラーがなくなりました。
なんでもフルパスで指定してはいけないのですね・・・

エラーはなくなったのですが処理に時間がかかり実行中のまま止まってしまいました。。
何度もお聞きし申し訳ございませんが、処理方法を教えて頂きたいです。

[hatena19]

「実行中のまま止まってしまいました。」とは具体的にどのような状態でしょうか。

エラーメッセージはでないですか。
マウスカーソルが実行中のまま、Accessの操作ができない、という状態ですか。

End Sub の前に下記のコードを挿入して実行したとき、メッセージボックスは表示されますか。

Msgbox ”終了しました”。

[zerobemy]

hatena19様
エラーメッセージなどは出ないです。
おっしゃる通りでマウスカーソルが実行中のままAccessの操作ができません。

＞Msgbox ”終了しました”
こちら入力しましたがメッセージボックスは表示されません。
Escを押してもなにも反応せずカーソルが青い丸のままグルグルしています。

[hatena19]

「テンプレクエリ」のレコード件数はどのくらいですか。このクエリの件数を数件になるように抽出条件を設定して実行するとどうなりますか。

あと、デバッグ方法を学習しましょう。下記が参考になるでしょう。
https://www.239-programing.com/excel-vba/basic/basic020.html

上記の説明を読んでで「ブレークポイント」を設定して「ステップ実行」をして、どこで時間がかかっているか確認してみてください。