Microsoft Access 掲示板

Microsoft Access 掲示板

エクセル操作　Rangeと変数

17 コメント
views
4 フォロー

たびたび失礼します。

Dim xls as Object
Dim i,j as integer
Set xls = CreateObject("Excel.application")
(中略)
xls.Range(cells(21+i , 1), cells(21+i+j , 1).entirerow.Insert

やりたいことは、21+ｉ行目からj行分の行を挿入することです。
「オブジェクトは、このプロパティまたはメソッドをサポートしていません」となってしまいます。
AccessにCellsがないということだと思いますが、

xls.Range(xls.cells(21+i , 1), xls.cells(21+i+j , 1).entirerow.Insert

としても、変化がありません。

Accessではどうすればよいのでしょうか

nokonoko
作成: 2023/07/20 (木) 14:59:28
通報 ...
1
hiroton 2023/07/20 (木) 15:49:15 14585@f966d

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

2
nokonoko 2023/07/20 (木) 16:27:32 3e2e6@54883 >> 1

試してませんでした。

Sub sample()
Dim i, j As Integer
i = 2
j = 3
Range(Cells(21 + i, 1), Cells(21 + i + j, 1)).EntireRow.Insert
End Sub

実行したところ、23~26行目に行が4行挿入されました。

3
hiroton 2023/07/20 (木) 17:24:08 14585@f966d

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上でマクロを動かすならともかく、「アクティブな状態」というあやふやな状態を対象とする記述はできる限りやらないほうが無難です

4
hiroton 2023/07/20 (木) 18:37:00 14585@f966d

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

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

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

すると良いコードになります
5
hatena 2023/07/20 (木) 18:47:55 修正

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

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

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

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

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

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

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

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を使った方がコードがシンプルになります。
7
hatena 2023/07/21 (金) 00:10:21

新規ブックを開いて、行挿入する場合は、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
8
nokonoko 2023/07/21 (金) 08:17:38 修正 3e2e6@54883

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

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

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
様のサイトを参照して作っている部分です。
9
hatena 2023/07/21 (金) 09:15:12

まず、回答の前に、一言。
コードの一部の抜粋を提示するより、可能ならば 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

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

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

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

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

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

10
nokonoko 2023/07/21 (金) 09:31:09 3e2e6@54883

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

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

11
nokonoko 2023/07/21 (金) 10:11:52 3e2e6@54883

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

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
12
hiroton 2023/07/21 (金) 10:33:38 22c35@f966d

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

            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

13
nokonoko 2023/07/21 (金) 10:43:23 3e2e6@54883 >> 12

失礼いたしました。
もともとのコードがぐちゃぐちゃで、そのまま貼り付けるにははばかられるようなものだったので、
修正をして貼り付けたときに、ミスが出たのだと思います。
14
hatena 2023/07/21 (金) 10:55:59 修正

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

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

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

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

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

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

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

15
nokonoko 2023/07/21 (金) 11:04:14 3e2e6@54883

ありがとうございます。

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

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

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

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

16
nokonoko 2023/07/21 (金) 11:07:09 3e2e6@54883

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

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

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

17
nokonoko 2023/07/21 (金) 11:36:52 3e2e6@54883

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

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

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