Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Access 2010)
Recordsetで取得した値をExcelへCellsで代入すると「アプリケーション定義またはオブジェクト定義のエラーです。」で止まる
投稿日時: 19/10/04 09:33:28
投稿者: 万葉の湯

Accessで店舗ごとの売上を集計し、Excelで帳票を作成するマクロを作成しています。
エラーが出る箇所は、Accessのテーブル(店舗マスタ)から複数の店番と店名をExcelのセルへ列方向へ代入する部分です。
 
デバックのステップインで実行するとエラーがでません。
また、エラーで止まったあとに、デバッグ画面を表示させてからF5で続行させるとエラーがでません。
通しでマクロを実行すると必ず下記の場所でエラーが発生します。
どなたか同じようなご経験をお持ちの方は是非ご教授お願いいたします。
 
【完成イメージ】
 店番A 店名A ・・・ 店番B 店名B・・・ 店番C 店名C・・・
 
 
【エラーの出るコード】
    Dim RS As ADODB.Recordset
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim StrPath As String, FileNM As String
    Dim i As Long
    Dim Cnt As Integer
    Set RS = New ADODB.Recordset
 
    StrPath = CurrentProject.Path
    FileNM = "\Template.xlsx"
     
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(StrPath & FileNM)
 
    xlApp.Application.Visible = False
    xlApp.ScreenUpdating = False
    xlApp.Application.DisplayAlerts = False
 
    RS.Open "SELECT 店舗マスタ.* FROM 店舗マスタ ORDER BY 店番", CN, adOpenKeyset
    Str_CN = RS.RecordCount
    For i = 1 To Str_CN
        xlBook.Sheets("店別実績").Cells(3, 6 + (9 * i)) = RS("店番") ←ここで「アプリケーション定義・・・」のエラー発生
        xlBook.Sheets("店別実績").Cells(3, 7 + (9 * i)) = RS("店名")
        RS.MoveNext
    Next i
    RS.Close
   :
   :
 
【試したこと】
  1.エラーの出るところで、Cellsの指定を固定セルCells(1,3)に変更 → エラーは発生しません
  2.エラーの出るところで、RS("店番")を固定値"aa"に変更 → エラーは発生しません
  3.エラーの出るところで、RS("店番")をCstr(RS("店番")に変更 → エラーは発生しません ※RS("店番")は数値
  4.3の結果を踏まえ 下記の処理の順番を変更 → エラー発生
        xlBook.Sheets("店別実績").Cells(3, 7 + (9 * i)) = RS("店名")
        xlBook.Sheets("店別実績").Cells(3, 6 + (9 * i)) = RS("店番")
  5.エラーが出る直前の行にDummyの値をセットする行(xlBook.Sheets("店別実績").Cells(3, 6 + (9 * i)) = "test")を加える
      → エラーが発生しません

回答
投稿日時: 19/10/04 10:28:15
投稿者: sk

引用:
StrPath = CurrentProject.Path
FileNM = "\Template.xlsx"
  
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(StrPath & FileNM)

テンプレート用のブック以外のブック(例えば新規ブック)への
出力については、既に試されたのでしょうか。
 
----------------------------------------------------------------------
 
Set xlBook = xlApp.Workbooks.Add
 
----------------------------------------------------------------------

投稿日時: 19/10/05 09:03:10
投稿者: 万葉の湯

skさん
 
ご返信ありがとうございます。
新規Bookは試してなかったので、テストしてみました。
結果は エラーが出ました。
となると Recordsetの方に問題があるんでしょうかね。。。
ローカルセットの画面で確認しても取得している値に問題なさそうなんですが・・・。

回答
投稿日時: 19/10/05 14:48:36
投稿者: hatena
投稿者のウェブサイトに移動

既定のプロパティを省略せずに記述したらどうなるでしょうか。
 

xlBook.WorkSheets("店別実績").Cells(3, 7 + (9 * i)).Value = RS("店名").Value

回答
投稿日時: 19/10/05 16:51:09
投稿者: WinArrow
投稿者のウェブサイトに移動

エラー発生時に
変数:i
の値は?
 
気になるところ
>Str_CN
のデータ定義は、されていますか?

投稿日時: 19/10/05 18:58:41
投稿者: 万葉の湯

WinArrowさん
ご返信ありがとうございます。
 
>Str_CN
申し訳ございません。途中のコードを省略したため、データ定義部分が抜けてしまいました。
実際はきちんとIntegerで宣言しております。
また、エラー発生時の変数もすべて意図した値が入っていることを確認しています。。。(T.T)
 
 
>hatenaさん
ご返信ありがとうございます。
左側には.VALUEを付けてテストしてみてエラーになったので、.VALUEではないと思ってたのですが
両辺は試してみませんでした。
先ほど実行してみたところ、エラーにならずに続行しました。
 
ただ 前述しました試したことでエラーが出なった 3,5で最悪これで行こうかと思って
進めていたのですが、ひょっこりまたエラーが出始めました。。。
 
hatenaさんからご指摘していただいたこともしばらく実行を繰り返して様子を見てみます。

回答
投稿日時: 19/10/06 11:55:54
投稿者: Moko

DAO にしてみたらどうかしら?

投稿日時: 19/10/07 13:38:41
投稿者: 万葉の湯

Mokoさん
ご意見ありがとうございます。
 
実はこのAccessでデータ集計→結果をExclに貼り付け帳票作成 というマクロを複数パターン作成しており
以前、DAOでRecordsetの結果をCopyFromRecordsetで貼り付けという処理を繰り返していたところ
とても動作が不安定だったため、ADOに変えたという経緯があります。

回答
投稿日時: 19/10/08 01:18:12
投稿者: MMYS

"SELECT 店舗マスタ.* FROM 店舗マスタ ORDER BY 店番"
 
セミコロンが無いけど、安定動作するのかな。
SELECT句にはテーブル指定あるけど
ORDER BY句はテーブル指定ない。
今回のケースならSELECT句はフィールド指定したほうがよいかと。
 
"SELECT 店番 FROM 店舗マスタ ORDER BY 店番;"
 
 
またエラー原因がセル側か、レコード側かはっきりしません。
私なら
  a = RS("店番").Value
  xlBook.Sheets("店別実績").Cells(3, 6 + (9 * i)).Value = a
と分割します。
 

投稿日時: 19/10/08 09:14:59
投稿者: 万葉の湯

MMYS さん
アドバイスありがとうございます。
 
記載内容を省略してしまったのですが、店舗マスタの各項目をExcelへ転記しているため、
全フィールドをselectしていました。
 
また、セミコロンを付けて2,3度実行してみたところ、エラーが出なくなりました。
ネットで調べたところ、セミコロンは終了記号とのこと・・・
セミコロンは必要だったんですね(^^;)
 
 
hatenaさんの「.VALURE」とMMYSさんの「;」をこのまま継続してみたいと思います。
ありがとうございました。