ACCESS2007の相談です

Question

初めて投稿します。

現在、会社で古い端末とオフィス(OSは７にオフィスは2007)を利用しています。

業務用のACCESSツールを作っているのですが、２つ挙動がおかしい点がありますのでご相談です。

・ACCESS上でEXCELを動かすVBAのRANGE構文が必ず2回に1回エラーとなる

　これはACCESSのクエリをEXCELのテンプレートに貼付後にテンプレートを修正するVBAで発生します。

　　(利用者がEXCEL派なのでレポートは嫌みたいです・・・)

　　コードとエラー箇所ですが下記になります

　　　~略~

　　　「レコードセットを開いてフォームの値を用いてクエリを作る」

　　　「クエリの値を指定されたEXCELテンプレートの指定の位置へ貼り付ける」

           ~略~

         '見栄えの修正

         ActiveSheet.Activate

         Dim 行 As Long

         行 = 0

         行 = Range("C38").End(xlUp).Row　←このrangeが必ず２回に１回エラー４１を吐く

         行 = 行 + 4

         Rows(行 & ":" & 38).Delete

         Range("J13").Select

　　※予めデータ数の変動が想定されるのでテンプレートは広めにとってある

　　　テンプレートの最終行(C38)から取り込んだデータの終端を求め見栄えを調整する

　　RANGEがなぜか2回に1回エラー41を吐きます。

　　onerrも試しましたがうまく行かず困っております。

　　古いので再現性が低くMSの有償サポートも頼れないですし、ネットを調べても出てこず途方にくれています。

　　レポートで出せば？と言われればそれまでですが使用者がEXCELじゃないとヤダ！ということで吐き出す形式にしております。

　・ACCESSファイルが共有できない

　　もともと共有モードに設定をし利用者のいる部署のNASにリンクテーブルとその他を分けて別ファイル保存しています。

　　しかしその他のメインの操作したいファイルは一人しか開けません。

　　上司からは、設定調べてみな！できると思うからとだけ言われ調べましたがこちらも答えがわからず困っています。

　　上司は元エンジニアなので聞けば教えてくれるかと思いますがまずは自分で解決したいと思っております。

どうかお知恵を拝借させてください。

Answer 1

御返事ありがとうございます。

持病と他の業務が立て込んでいるためツールの改善業務ができず試せておりませんが、

時間を見つけてリンクを参考にテストと修正をしてみます。

オートメーションエラーというのがあるのは初耳でした・・・

もっと調べないとだめですね。

自分で使うツールであればまぁ2回に一回ならデバック画面出るって割り切るしかないのですが、

他の部署の方が使うので早急に治せるようにしたいところです。

こちらが解決したら次点の「2台以上で共有」を手つけて行きたいと思っています。

OS入れ替えの際にACCESSがどのバージョンになるかによっては大規模なデバックが発生しそうですがこれは覚悟の上ですので大丈夫です。

※サポートの方に聞きました。

Answer 2

> ActiveSheet.Activate

>

> Dim 行 As Long

>

> 行 = 0

>

> 行 = Range("C38").End(xlUp).Row

>

> 行 = 行 + 4

>

> Rows(行 & ":" & 38).Delete

>

> Range("J13").Select

xlWs.Activate

Dim 行 As Long

行 = xlWs.Range("C38").End(xlUp).Row

行 = 行 + 4

xlWs.Rows(行 & ":" & 38).Delete

xlWs.Range("J13").Select

---

Access のモジュールから実行する場合は、

基本的には上記のように記述なさればよいはず。

> ただ、それでは私の一番の疑問である

> 「なぜ必ず２回に１回だけエラーなのか」が不思議です・・・

MS サポート: 2 回目のコード実行時に Excel のオートメーションが失敗する

引用:

> Excel オブジェクト変数で修飾せずに Excel のオブジェクト、メソッド、または

> プロパティを呼び出すコード行があるため、Visual Basic で Excel への参照が確立されます。

> Visual Basic では、プログラムを終了するまでこの参照は解放されません。

> この参照が原因で、コードを 2 回以上実行した場合に、オートメーション コードが失敗します。

Answer 3

御返事ありがとうございます。

吐出しから見栄えの修正までのコードは以下になります。

(これは納品書ですが請求書も見積書も同様な流れで処理しています)

１フォームでコンボボックスから選択した取引先を基にクエリの生成

２指定された場所に保存された書類のテンプレートを呼び出し

３テンプレートへクエリから貼り付け

４テンプレートは変動に対応できるよう幅をもたせているので最終行からデータの終わりまでを求めて削除

５わかりやすいように指定場所へ保存

     Dim rs As DAO.Recordset

     Dim qd As DAO.QueryDef

     Dim xlApp As Excel.Application

     Dim xlWb As Excel.Workbook

     Dim xlWs As Excel.Worksheet

     Dim fileName As Variant

     Dim x1, y1, x2, y2, x3, y3, i As Long

     Dim endrc

         Dim res As Variant

        res = MsgBox("データの吐き出しをおこないますか？" & Chr(13) & "本出しは戻れません！！", _

         vbYesNo, "吐き出しの確認")

     If res = vbYes Then

     'レコードセットを開く

     'VBAでパラメータクエリをセットさせないといけないので・・・

    Set qd = CurrentDb.QueryDefs("Q_納品書吐き出し")

    qd.Parameters("[forms]![F_納品書吐き出し]![txt1得意先]") = Forms!F_納品書吐き出し!txt1得意先

    Set rs = qd.OpenRecordset

    If Not rs.RecordCount = 0 Then

         'Excel

         'オブジェクトの作成

        Set xlApp = CreateObject("Excel.Application")

         'ファイルを開く→絶対パスで！

        Set xlWb = xlApp.Workbooks.Open("\NAS\share\作業管理\各種書類テンプレ\納品書.xltx")

         'シートをセット

        Set xlWs = xlWb.Worksheets("納品書")

         xlApp.Visible = True

         'セルを指定してレコードセットを張り付け

         'エクセルシートは独自の形をしているので分割して吐き出そう・・・・

         '**********************************************************************************************************

         'セル位置は(Y,X)で表記しよう！！！

         '業者名・担当者・発行日・納品書ナンバー

         xlWs.Range("C8").MergeArea = rs("得意先")

         'xlWs.range("M6") = rs("") 担当者はベタ打ちらしい！

         xlWs.Range("H3").MergeArea = Date

         xlWs.Range("L1").MergeArea = rs("最新納品書発行番号")

         '番号・型番・製造番号・修理内容・修理金額

         'ループさせながらクエリをエクセルへ貼り付けるよ！

         With rs

            'レコードの終端をループの最後にするために取得します

            '→Do Loopは時間がかかりすぎたので、ForNext作戦・・・・

            .MoveLast

            endrc = .RecordCount

            .MoveFirst

            '開始地点セルはC19セルなので・・・

            x1 = 3

            y1 = 19

            i = 0

            For i = 1 To endrc

                xlWs.Cells(y1, x1) = rs("管理番号")

                xlWs.Cells(y1, x1 + 1) = rs("メーカー名")

                xlWs.Cells(y1, x1 + 2) = rs("型番")

                xlWs.Cells(y1, x1 + 3) = rs("製造番号")

                xlWs.Cells(y1, x1 + 4) = rs("修理内容")

                xlWs.Cells(y1, x1 + 5) = rs("金額(税抜)")

                rs.MoveNext

                y1 = y1 + 1

            Next

         End With

         '見栄えの修正

         ActiveSheet.Activate

         Dim 行 As Long

         行 = 0

         行 = Range("C38").End(xlUp).Row

         行 = 行 + 4

         Rows(行 & ":" & 38).Delete

         Range("J13").Select

 '**********************************************************************************************************

重要なポイントは、 Excel の最上位オブジェクトである

Excel.Application オブジェクトへの参照をオブジェクト変数に保持し、

そのメンバーや下位のオブジェクトを明示的に参照できるような

コードを記述する、ということです。

これは、私のコードでは貼り付けまでは、「xlWs」で流用しているが、

range段階ではアクティブシートにしているのがまずい・・・

という解釈でよろしいでしょうか。

ただ、それでは私の一番の疑問である「なぜ必ず２回に１回だけエラーなのか」が不思議です・・・

rangeもxlWsで記述は試していないので明日、試して見ます。

また、共有モードであれば多人数で開けますとネットや解説書では見ますが

こちらもなぜか７と２００７の組み合わせではできず困っております・・・