Microsoft Access 掲示板

そのコード、EXCELなら動くんですか？

Excelでなら動く

Range(Cells(21 + i, 1), Cells(21 + i + j, 1)).EntireRow.Insert

を、なんやかんやして

xls.Range(xls.Cells(21 + i, 1), xls.Cells(21 + i + j, 1)).EntireRow.Insert

とした場合、このコード部分だけなら動作させることは可能です
つまり、このコードが動くための準備がうまく出来ていません

xlsはSet xls = CreateObject("Excel.application")としていますので、EXCELそのものを示します
Application.Range プロパティ (Excel)

アクティブ シートがワークシートでない場合、このプロパティは失敗します。

Application.Cells プロパティ (Excel)

作業中の文書がワークシートでない場合、このプロパティは無効になります。

いつもの難しいMicrosoft記述ですが、つまり、「ユーザーが操作できる状態じゃないとエラーになるよ」「ユーザーが操作できる状態のモノが対象になるよ」ということです

解決策としては、「操作したいシートをアクティブにする」か、「アクティブでないRange/Cellsを対象にする記述をする」のどちらかです。とはいえ、実際に使っているExcel上でマクロを動かすならともかく、「アクティブな状態」というあやふやな状態を対象とする記述はできる限りやらないほうが無難です

うまいこと解説してるサイト見つけてペタッとして終わりたかったんですけど見つけられないですねぇ

このような問題を起こさないためには、「アクティブな」というその瞬間によって変わるような参照を使わないようにする必要があります。そのためには、オブジェクトの親子関係をしっかり把握して、最上位の親から目的の子まで、すべてをつなげる必要があります

Range/Cellsの場合

(Excel)Application　　　：xls = CreateObject("Excel.application")はこのレベル
　└Workbook
　　└Worksheet
　　　└Range / Cells

つまり、xls.Cellsと記述すると「xls.ActiveWorkBook.ActiveSheet.Cells」と同等の結果になります

「アクティブな」参照をしないためにはWorkbookとWorksheetを明示してあげればいいので、例えば

xls.Workbooks(1).Worksheets(1).Cells

のような記述が考えられます
また、毎回このような記述は冗長なので、変数で置き換えたり

Dim WS As object
Set WS = xls.Workbooks(1).Worksheets(1)
WS.Cells(～)
Set WS = Nothing

With　～　End Withステートメントを使ったり

With xls.Workbooks(1).Worksheets(1)
    .Cells(～)
End With

すると良いコードになります

中略した部分も提示してください。

中略した部分でブックを開いてますか。
それとも新規ブックのシートに代入するのですか。

xls.Range() としてますが、xls(エクセルアプリケーション)はRangeプロパティをもってません。

エクセルアプリケーションのブックのシートがRangeプロパティをもっています。

どのブックのどのシートのセルに代入するかをはっきりさせないとだめです。

エクセルのVBAで、Range()としたときは、ActiveBook.ActiveSheet.Range() ということにエクセルが解釈してくれます。Access君はそのように自動で解釈してくれませんので、きちんと指定してあげないと、Access君は困ってしまいます。

既存ブックを開いて、行を挿入する場合のコード例

Sub Accessで既存ブックを開いて行挿入()
    Dim xls As Object
    Set xls = CreateObject("Excel.Application")
    xls.Visible = True
    
    Dim wb As Object 'ワークブックオブジェクト
    Set wb = xls.Workbooks.Open(Filename:="C:\Test\Test.xlsx")
    
    Dim i As Long, j As Integer
    i = 1
    j = 5
    
    With wb
        .Sheets(1).Rows(21 + i).Resize(j).Insert
        .Save
        .Close
    End With
    xls.Quit
    Set xls = Nothing
    Set wb = Nothing
End Sub

Openメソッドでブックを開きますが、戻り値は開いたブックなのでそれを変数に代入しておいてあとで利用します。
開いたブックの先頭シートに行挿入してます。
行挿入はRowsを使った方がコードがシンプルになります。

新規ブックを開いて、行挿入する場合は、Openメソッドの代わりにAddメソッドを使います。
保存するときにファイル名を指定するようにします。

Sub Accessで新規ブックを開いて行挿入()
    Dim xls As Object
    Set xls = CreateObject("Excel.Application")
    xls.Visible = True
    
    Dim wb As Object 'ワークブックオブジェクト
    Set wb = xls.Workbooks.Add
    
    Dim i As Long, j As Integer
    i = 1
    j = 5
    
    With wb
        .Sheets(1).Rows(21 + i).Resize(j).Insert
        .Save Filename:="C:\Test\Test.xlsx"
        .Close
    End With
    xls.Quit
    Set xls = Nothing
    Set wb = Nothing
End Sub

お二方回答ありがとうございます。
回答を見たばかりなので、内容確認できてはおりません。

中略した部分も提示してください。

Set xls = CreateObject("Excel.application")
With xls
    .screenUpdating = False
    .workbooks.Open strTemplateDir & strTemplateBook
    .workbooks(strTemplateBook).Worksheets("xxx").Copy
    .workbooks(strTemplateBook).Close
　　.Range(xls.cells(21+i , 1), xls.cells(21+i+j , 1).entirerow.Insert

こんな感じです。
https://tsware.jp/tips/tips_689.htm
様のサイトを参照して作っている部分です。

まず、回答の前に、一言。
コードの一部の抜粋を提示するより、可能ならば Sub から End Sub まですべて提示した方が話がはやいでしょう。
もし、コードが提示するには長すぎるなら、処理を複数のサブルーチンに分割することを検討した方がいいでしょう。
今回も、結局リンク先のコードをみて類推して回答することになりますが、認識違いで無駄なやりとりになる可能性もあります。

こちらで、リンク先のコードと提示のコードからサンプルを作成して実験したところ、下記を修正したら正常に動作しました。

With xls
    .screenUpdating = False
    .workbooks.Open strTemplateDir & strTemplateBook
    .workbooks(strTemplateBook).Worksheets("xxx").Copy
    .workbooks(strTemplateBook).Close
-　　.Range(xls.cells(21+i , 1), xls.cells(21+i+j , 1).entirerow.Insert
+　　.Range(xls.cells(21+i , 1), xls.cells(21+i+j , 1)).entirerow.Insert

ようは不足していた)を追加しただけです。

ただし、この修正前のコードでは構文エラーになり実行すらできませんので、

「オブジェクトは、このプロパティまたはメソッドをサポートしていません」

というようなエラーにはなりませんので、提示されていない他の部分に原因があるでしょう。

ということで、やはりコード全体を提示してください。

ご指摘ありがとうございます。

そのままでは3000字を越えてしまうので、少し検討します。

明らかに不要なところを除きました。
実行時に同様のエラーが出ます。

Private Sub cmd練習_Click()
   
On Error GoTo Err_Handler

Dim i As Integer
Dim xls As Object
Dim intNumRow, intGNumRoW As Integer 
Dim strTemplateDir, strTemplateBook As String

intNumRow = 3
intGNumRoW = 5

'テンプレートの保存先フォルダ
    strTemplateDir = Application.CurrentProject.Path & "\データ\テンプレート\"
'テンプレートのファイル名
    strTemplateBook = "原紙.xlsx"
    
'Excelオブジェクトを生成
Set xls = CreateObject("Excel.application")
With xls
    '画面の再描画を抑止
    .screenUpdating = False
    'テンプレートファイルを開く
    .Workbooks.Open strTemplateDir & strTemplateBook
    'ワークシートをコピー
    .Workbooks(strTemplateBook).worksheets("xxx").Copy
    'テンプレートファイルを閉じる
    .Workbooks(strTemplateBook).Close

    'ワークシートに行追加 A
    If intNumRow > 1 Then
        For i = 1 To intNumRow - 1
            .Range("A16").entirerow.Insert
        Next
    End If
 
    'ワークシートに行追加 B
        If intGNumRoW > 1 Then
            xls.Range(xls.cesll(21 + intNumRow, 1), xls.cells(21 + intNumRow + intGNumRoW, 2)).entirerow .Insert
        End If
    
    '画面の再描画を元に戻す
    .screenUpdating = True
End With

MsgBox "保存されました。"
DoCmd.Close acForm, Me.Name

    'Excelを終了
xls.Quit
Set xls = Nothing

Exit_here:
If xls Is Nothing Then Exit Sub
Exit Sub

Err_Handler:
MsgBox Err.Number & vbTab & Err.Description, vbExclamation
Resume Exit_here

End Sub

書き込みがある度、コードが変わりますね
落ち着いて、コピペをしてください

            xls.Range(xls.cesll(21 + intNumRow, 1), xls.cells(21 + intNumRow + intGNumRoW, 2)).entirerow .Insert

            xls.Range(xls.cesll(21 + intNumRow, 1), xls.cells(21 + intNumRow + intGNumRoW, 2)).entirerow .Insert

ざっと見た感じではコード自体に問題はなさそうです。
（もっとスマートな書き方かあるかもということは置いておいて）

とりあえずうまくいかないとき、原因を特定するためのデバッグ作業をしてみましょう。
まず、
On Error GoTo Err_Handler
はコメントアウトしておきましょう。これがあるとどこでエラーがある分かりません。
次に、

Set xls = CreateObject("Excel.Application")
xls.Visible = True '挿入
With xls
    '画面の再描画を抑止
'    .screenUpdating = False コメントアウト

としてエクセルを表示させ、画面の再描画停止をやめましょう。
これでエクセル上での操作を目で確認できます。

そのうえで1行ずつステップ実行して動作を確認しつつ、どこでどのようなエラーが出るのか確認しましょう。ステップ実行 は下記リンクを参考にしてください。

デバッグの基本 – ステップ実行 | ExcelVBA入門 自宅でプログラミング

このデバッグ作業をして、どの行でどのようなエラーがでるのか教えてください。

ありがとうございます。

コメントアウトなどの処置をして、検証しました。

xls.Range(xls.cesll(21 + intNumRow, 1), xls.cells(21 + intNumRow + intGNumRow, 1)).entirerow.Insert

のところで、「実行時エラー‘438‘:　オブジェクトは、このプロパティまたはメソッドをサポートしていません。」が出ます。

その前までは、望んでいるような結果が出ています。

失礼しました
Cesllになってました。
直したら、動きました。

こんなくだらないことに、たくさん回答いただき、大変申し訳ないです。
Hiroton様もありがとうございました。

ほかに問題がないか後で検証します。

11のところで抜粋したものについてはCesllになぜかなっていましたが、
抜粋前の本コードのほうはCellsであっていましたが、hiroton様指摘の通り、entirerowの後にスペースがあることによってエラーが出たことがわかりました。

いずれにせよ、当方の非常に初歩的なミス、プラスおっちょこちょいでした。

お二方には改めてお礼を申し上げます。
今後は、このようなことがないように、利用させていただきます。