質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.84%

  • VBA

    1561questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1335questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Oracle

    554questions

    Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

  • SQL Server

    536questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

  • マクロ

    201questions

    定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

ExcelVBA オートメーションエラー回避

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 229

bako

score 2

 前提・実現したいこと

SQLserverのT_GroupAccessKeyのアクセスキーコードとoracleのS20の責任者パスワードを比較して一致していなければエラー表示させるという既存のExcelVBAがあります。
そこにSQLserverのT_GroupAccessKeyの団体番号&枝番とT_Groupの団体番号&枝番を結合してT_Groupの発送区分がNull以外のものという抽出条件を一つ追加したいです。

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

既存のVBAに構文を追加したんですが、「実行時エラー'-2147217865(80040e37)':オートメーションエラーです。」のエラーが表示されます。

 該当のソースコード

    'レコードセットを取得
    Set adoRsWeb = New ADODB.Recordset
    adoRsWeb.CursorLocation = adUseClient  'RecordCountの取得に設定が必要

    '"配列に設定した団体番号を1件ずつSQLを実行し、データが取得されるかチェックする
    '(受付システム側にアップロード先となる団体番号のデータが存在しているか確認する作業)
    For intArray = 0 To MaxArray

        '配列の値が空白でない場合に処理をする
        If strCheckNoUketukeUpSakiAll(intArray) <> "" Then

            'SQLの作成
            strSQLWeb = "select * from S20 where DANTAINO in ('" & strCheckNoUketukeUpSakiAll(intArray) & "')"

            'レコードセットを取得
            adoRsWeb.Open strSQLWeb, adoConWeb, adOpenStatic, adLockReadOnly

            'データが取得された場合
            If adoRsWeb.RecordCount = 0 Then
                Sheets("ツール").Cells(intArray + StartRowNo, ColUmu).Value = "エラー有"
                Sheets("ツール").Cells(intArray + StartRowNo, ColError).Value = "受付システムに団体番号がありません"
            'データが取得されなかった場合(ここでは、"エラー無"に設定)
            ElseIf adoRsWeb.RecordCount <> 0 And _
                    strCheckNoUketukeUpSakiAll(intArray) = adoRsWeb!DANTAINO And _
                    Sheets("ツール").Cells(intArray + StartRowNo, ColUmu).Value = "受付エラー無" Then
                Sheets("ツール").Cells(intArray + StartRowNo, ColUmu).Value = "エラー無"
            End If

            '"ツール"Sheetにアップロード先の団体番団を設定
            '(UPLOADSAKIGROUPIDにデータがある場合のみ設定、無い場合は空欄)
            Sheets("ツール").Cells(intArray + StartRowNo, ColUpsaki).Value = strCheckNoUketukeUpSakiDiffer(intArray)

            'レコードセットを閉じる
            adoRsWeb.Close

        End If

    Next intArray

    DBConnectCheck


    'MS SQLServer側のレコードセットを定義
    Set adoRsPass = New ADODB.Recordset

    Dim passCheck As String
    Dim passWeb As String

    For intArray = 0 To MaxArray

        '配列の値が空白でない場合に処理をする
        If strCheckNoUketukeUpSakiAll(intArray) <> "" Then

'----------------------------------------------
'ここから追加
'SQLの作成
strDis = "SELECT T_GroupAccessKey.GroupID, T_GroupAccessKey.BranchNo, T_GroupAccessKey.AccessKeyCD, T_GroupAccessKey.AccessKeyKind, T_GroupAccessKey.AccessKey, T_GroupAccessKey.ApprovalFlag, T_GroupAccessKey.UpdateName, T_GroupAccessKey.UpdateDate, T_Group.DispatchKbn FROM T_GroupAccessKey LEFT JOIN T_Group ON (T_GroupAccessKey.BranchNo = T_Group.BranchNo) AND (T_GroupAccessKey.GroupID = T_Group.GroupID) WHERE (((T_Group.DispatchKbn)<>''))"
'ここまで
'----------------------------------------------

            '---- MS SQLServer側(団体アクセスキーテーブル)
            'SQLの作成
'-----------------------------------------------
'            strSQLCheck = " select * from T_GroupAccessKey " _
'                         & " where CONCAT(GroupId,BranchNo) = '" & strCheckNoUketukeUpSakiAll(intArray) & "'" _
'                         & " AND AccessKeyCD = (select AccessKeyCD from M_AccessKeyPeriod where CONVERT(varchar,current_timestamp,112) between CONVERT(varchar,accesskeyfrom,112) and CONVERT(varchar,accesskeyto,112) )" _
'                         & " AND AccessKeyKind = '1'"
'            & " AND AccessKeyCD = '28' AND AccessKeyKind = '1'"
'-----------------------------------------------

'----------------------------------------------
'ここから追加
            strSQLCheck = " select * from strDis " _
                         & " where CONCAT(GroupId,BranchNo) = '" & strCheckNoUketukeUpSakiAll(intArray) & "'" _
                         & " AND AccessKeyCD = (select AccessKeyCD from M_AccessKeyPeriod where CONVERT(varchar,current_timestamp,112) between CONVERT(varchar,accesskeyfrom,112) and CONVERT(varchar,accesskeyto,112) )" _
                         & " AND AccessKeyKind = '1'"
'ここまで
'----------------------------------------------

            'レコードセットを取得
            adoRsPass.Open strSQLCheck, adoConCheck, adOpenStatic, adLockReadOnly

           '---- Oracle 側(団体情報マスタ)
            'SQLの作成
            strSQLWeb = "select * from S20 where DANTAINO = '" & strCheckNoUketukeUpSakiAll(intArray) & "'"

            'レコードセットを取得
            adoRsWeb.Open strSQLWeb, adoConWeb, adOpenStatic, adLockReadOnly

            'T_GroupAccessKey、S20 双方のパスワードを突合せ

            '判定フラグ(パスワード一致=1)
            hanteiFlg = 0
            Do While Not adoRsPass.EOF

                 Do While Not adoRsWeb.EOF

                    passWeb = adoRsWeb!SEKININSHAPSW
                    passCheck = adoRsPass!AccessKey

                    If passWeb = passCheck Then

                        hanteiFlg = 1

                    End If
                    adoRsWeb.MoveNext

                Loop
                adoRsPass.MoveNext

            Loop

            'レコードセットを閉じる
            adoRsPass.Close
            adoRsWeb.Close

            'パスワードが一致した場合
            If hanteiFlg = 1 Then

                Sheets("ツール").Cells(intArray + StartRowNo, ColUmu).Value = "エラー無"
                Sheets("ツール").Cells(intArray + StartRowNo, ColError).Value = ""
                Sheets("ツール").Cells(intArray + StartRowNo, 7).Value = ""
                Sheets("ツール").Cells(intArray + StartRowNo, 8).Value = ""

            Else

                If Sheets("ツール").Cells(intArray + StartRowNo, ColUmu).Value = "エラー無" Then

                    Sheets("ツール").Cells(intArray + StartRowNo, ColUmu).Value = "エラー有"
                    Sheets("ツール").Cells(intArray + StartRowNo, ColError).Value = "受付システムのパスワードが違います"
                    Sheets("ツール").Cells(intArray + StartRowNo, 7).Value = passWeb
                    Sheets("ツール").Cells(intArray + StartRowNo, 8).Value = passCheck

                End If

            End If

        End If

    Next intArray

    DBDisConnectCheck

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With

    'DBの切断
    DBDisConnectUketuke
    DBDisConnectWeb

    MsgBox "処理が完了しました"

End Sub

修正前

    Set adoRsPass = New ADODB.Recordset

    Dim passCheck As String
    Dim passWeb As String

    For intArray = 0 To MaxArray

        '配列の値が空白でない場合に処理をする
       If strCheckNoUketukeUpSakiAll(intArray) <> "" Then


            '---- MS SQLServer側(団体アクセスキーテーブル)
            'SQLの作成
'-----------------------------------------------
            strSQLCheck = " select * from T_GroupAccessKey " _
                         & " where CONCAT(GroupId,BranchNo) = '" & strCheckNoUketukeUpSakiAll(intArray) & "'" _
                         & " AND AccessKeyCD = (select AccessKeyCD from M_AccessKeyPeriod where CONVERT(varchar,current_timestamp,112) between CONVERT(varchar,accesskeyfrom,112) and CONVERT(varchar,accesskeyto,112) )" _
                         & " AND AccessKeyKind = '1'"
'-----------------------------------------------

修正後

    'MS SQLServer側のレコードセットを定義
    Set adoRsPass = New ADODB.Recordset

    Dim passCheck As String
    Dim passWeb As String

    For intArray = 0 To MaxArray

        '配列の値が空白でない場合に処理をする
        If strCheckNoUketukeUpSakiAll(intArray) <> "" Then

'SQLの作成
strDis = "SELECT T_GroupAccessKey.GroupID, T_GroupAccessKey.BranchNo, T_GroupAccessKey.AccessKeyCD, T_GroupAccessKey.AccessKeyKind, T_GroupAccessKey.AccessKey, T_GroupAccessKey.ApprovalFlag, T_GroupAccessKey.UpdateName, T_GroupAccessKey.UpdateDate, T_Group.DispatchKbn FROM T_GroupAccessKey LEFT JOIN T_Group ON (T_GroupAccessKey.BranchNo = T_Group.BranchNo) AND (T_GroupAccessKey.GroupID = T_Group.GroupID) WHERE (((T_Group.DispatchKbn)<>''))"

            '---- MS SQLServer側(団体アクセスキーテーブル)
            'SQLの作成
'-----------------------------------------------
            strSQLCheck = " select * from strDis " _
                         & " where CONCAT(GroupId,BranchNo) = '" & strCheckNoUketukeUpSakiAll(intArray) & "'" _
                         & " AND AccessKeyCD = (select AccessKeyCD from M_AccessKeyPeriod where CONVERT(varchar,current_timestamp,112) between CONVERT(varchar,accesskeyfrom,112) and CONVERT(varchar,accesskeyto,112) )" _
                         & " AND AccessKeyKind = '1'"
'----------------------------------------------

            'レコードセットを取得
            adoRsPass.Open strSQLCheck, adoConCheck, adOpenStatic, adLockReadOnly

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

Excel2016
【ソフト名】 A5:SQL Mk-2 Version 2.11.6
ソフトウェア名: osqledit

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2018/05/10 15:04

    mts10806さん、ご指導ありがとうございます。事務局には1年以上前からここやコメント欄にもマークダウンを使えるようにお願いしているんですが、直す気があるのかどうか?もっと使い易い掲示板があると良いですね。

    キャンセル

  • mts10806

    2018/05/10 15:55

    bakoさん色々修正されていますが、最初の指摘の対応がなされていません。 プログラムコード(およびエラーメッセージ)は```で囲ってください。(わからなければ質問編集画面でコード部分を選択し<code>ボタンを押してください)正しく反映されているかどうかは質問編集画面のプレビューを見ながら編集していってください。

    キャンセル

  • mts10806

    2018/05/10 15:57

    Orlofskyさん コメントはあくまで確認と補足という認識かもしれませんね。現実的にはコメントでコードがかなりの勢いで飛び交っているので、現在のユーザーの運用にあわせて改善してもらえればとは私も思います(あと質問投稿時に初期状態で初心者アイコンONとか)

    キャンセル

回答 2

0

1つめに作成したSQL文を格納した変数名が、2つめのSQL文中に文字列として格納されています。

1つめのSQL
strDis = "SELECT T_GroupAccessKey.GroupID, T_GroupAccessKey.BranchNo, ~

2つめのSQL
strSQLCheck = " select * from strDis " _

ダブルクォーテーションで括った文字列内に変数名を直接書いてしまうと、値が展開されないので意味がないです。
また1つめのSQLを2つめのサブクエリとしたいのであれば、括弧で括らないとうまく動かないような気がします。
元々こういう作りなんでしょうか?
条件を追加しただけであればそうなんでしょうけど…。
修正前と修正後のコードを載せたほうが話は早いかもしれませんね。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/05/10 15:45

    ttyp03さん、ありがとうございます。
    抜粋して修正前と修正後を載せました。

    キャンセル

  • 2018/05/10 15:49

    指摘に対して対応後はどうだったのでしょうか?
    ちょうどsaziさんから同じ回答と修正コードが出ているので、参考にしてください。
    (エラーが出るとは言ってますが…)

    キャンセル

  • 2018/05/10 18:59

    ttyp03さん、saziさんのコードを参考にしてみました。
    エラーは回避できたようです。
    ありがとうございました。

    キャンセル

0

見にくいのでちょっとコードを整形します。
現状は以下になっています。

strSQLCheck = _
  "select * from strDis " & _
  " where CONCAT(GroupId,BranchNo) = '" & strCheckNoUketukeUpSakiAll(intArray) & "'" & _
  " AND AccessKeyCD = (select AccessKeyCD from M_AccessKeyPeriod where Convert(varchar, current_timestamp, 112) " & _
  " between CONVERT(varchar,accesskeyfrom,112) and CONVERT(varchar,accesskeyto,112) )" & _
  " AND AccessKeyKind = '1'"


strDisが文字列内で記述されているので、変数になっていませんので、「そんなテーブルは無い」というエラーです。
また、変数を展開するということはサブクエリーですから、()で括る必要があります。エイリアスも付けたほうがいいですね。

strSQLCheck = _
  "select * from (" & strDis & ") Dis " _
  " where CONCAT(GroupId,BranchNo) = '" & strCheckNoUketukeUpSakiAll(intArray) & "'" & _
  " AND AccessKeyCD = (select AccessKeyCD from M_AccessKeyPeriod where Convert(varchar, current_timestamp, 112) " & _
  " between CONVERT(varchar,accesskeyfrom,112) and CONVERT(varchar,accesskeyto,112) )" & _
  " AND AccessKeyKind = '1'"


ただ上記でも、accesskeyfromaccesskeytoは見当たりませんので、エラーは解消しないと思います。
M_AccessKeyPeriodのサブクエリーでした。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/05/10 16:05 編集

    こういった場合は、SQL文の編集後のステップにブレークポイントを置き、イミディエイトウインドウで変数の内容を出力して、その結果のSQL文をコピーして、直接実行してみるとエラーの内容が判別しやすいですよ。

    キャンセル

  • 2018/05/10 18:57

    saziさん、ありがとうございました。
    とても分かりやすく納得しました。
    初心者の質問でもご丁寧に答えていただいて大変助かりました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.84%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • VBA

    1561questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1335questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Oracle

    554questions

    Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

  • SQL Server

    536questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

  • マクロ

    201questions

    定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。