• ベストアンサー

エクセルVBAでSQLを使ってアクセルのデータを引き出す

下記マクロ「アクセスからインポート」はアクセスの人口テーブルから番号フィールドがエクセルシートセルA1に入力された番号と一致するデータのみをエクセルのセルA5以下に入力するマクロですが、★部分を書き換えて番号フィールドがセルA1、都道府県フィールドがセルB2と一致するデータを取り出したい。 Sub アクセスからインポート() Dim cn As Connection Dim rs As Recordset Dim SelCmd As String 'データベースに接続 Set cn = New Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Excel\Sample.mdb" cn.Open '★↓これはうまく動きます SelCmd = "SELECT * FROM 人口 " _ & "WHERE 番号 = " & Range("A1").Value 'データを取得 Set rs = New Recordset rs.Open SelCmd, cn 'シートに貼り付け Range("A5").CopyFromRecordset rs '後処理 rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub 　★↓これを入力しようとするとエラーになります。 SelCmd = "SELECT * FROM 人口 " _ & "WHERE 番号 = " & Range("A1").Value　and　都道府県 = " & Range("B1").Value　 andの書き方が悪いのでしょが、いろいろ試したがうまく行きません。 初心者ですがよろしくお願い致します。

質問者が選んだベストアンサー

  • ベストアンサー
回答No.2

たびたびすみません。 &も要りそうです 【例】 & "WHERE 番号 = " & Range("A1").Value & " and　都道府県 = " & Range("B1").Value

hiroshi77777
質問者

お礼

うまくいきました。ありがとうございます。

その他の回答 (2)

  • dober-o
  • ベストアンサー率59% (260/439)
回答No.3

and が問題ではなく "" の区切り位置ですかね。 SelCmd = "SELECT * FROM 人口 " _ & "WHERE 番号 = " & Range("A1").Value & " and 都道府県 = " & Range("B1").Value ちなみに都道府県フィールドが数値ではなく文字列なら SelCmd = "SELECT * FROM 人口 " _ & "WHERE 番号 = " & Range("A1").Value & " and 都道府県 = """ & Range("B1").Value & """" となります。

hiroshi77777
質問者

お礼

うまくいきました。ありがとうございます。

回答No.1

" が１つ足りないとか、、 SelCmd = "SELECT * FROM 人口 " _ & "WHERE 番号 = " & Range("A1").Value　and　都道府県 = " & Range("B1").Value　 ↑" 【修正例】 & "WHERE 番号 = " & Range("A1").Value " and　都道府県 = " & Range("B1").Value　

  1. カテゴリ
  2. [技術者向] コンピューター
  3. プログラミング・開発
  4. Visual Basic

関連するQ&A

  • 数値のMAXの値を取得したい　ADO　VBA

    アクセスです。 ADOで該当のフィールドの数値のMAXの値を取得するにはどうすればいいですか？ テーブルの番号フィールドには、 1 2 3 4 5 と入っているのですが、 この場合、一番大きい値は５なので MAX関数のようなもので５を返したいのですが ADOにそのようなプロパティはありますか？ オブジェクトブラウザーでRecordsetを見てみましたが 見つけられませんでした。 Sub Sample() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Set cn = CurrentProject.Connection rs.CursorLocation = adUseClient rs.Open "SELECT * FROM T_test", cn, adOpenStatic, adLockPessimistic rs.Sort = "番号 DESC" Debug.Print rs("番号").Value rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub これで同じような動きは出来ますが、 もっとシンプルにできませんか？

  • ADOでエクセルからSQL Serverへデータを移行するには

    エクセルｖｂａのＡＤＯを使って、 SQL Serverの「test」という名のデータベースの「Table_1」に 新規レコードを追加する事はできますか？ エクセルからアクセスには Sub test() Dim データベース名 As String Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & データベース名 rs.Open "Ｔテーブル１", cn, adOpenKeyset, adLockOptimistic rs.AddNew rs.Fields("フィールド１") = データ rs.Update rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub で移行しています。 これをエクセルからSQL Serverへ移行させるにはどうすればいいのでしょうか？ よろしくお願いします。

  • Accessのテーブルの文字列フィールドにVBAでアクセスするには？

    いつもお世話になっております。困っていることがありますので教えていただければ幸いです。 AccessのテーブルAに、メモ型のフィールドBがあり、1000文字くらいの文字が入っています。VBAの関数Cの中でそのテーブルにアクセスし、1000文字をいろいろ処理したいと思っています。見よう見まねで試しに Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim FileNum As Integer Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset rs.Open テーブルA, cn, adOpenDynamic, adLockOptimistic msgbox !フィールドB としてみたのですが、なぜか255文字までしか表示されません。VBAを用いてテーブル（やクエリ）にアクセスし、256文字以上の文字列を扱う方法はありませんでしょうか？ 何かこちらで勘違いしているところがありましたら、ご指摘いただければと思います。よろしくお願いします。

  • エクセルでアクセスのクエリを更新したい

    お世話になります。 アクセスのデータをエクセルで編集する方法を勉強しています。 アクセスのクエリをエクセルで呼び出すところまで成功しました。 ところが、クエリを読み込んだ後、アクセスのクエリに戻すところで煮詰まっています。 ************************************************************** Sub btn更新_Click() Dim DelCmd As String Dim z As Long Dim i As Long Dim cn As Connection Dim rs As Recordset Set cn = New Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Excel\Sample.mdb" cn.Open DelCmd = "DELETE * FROM クエリＡ" cn.Execute DelCmd If Range("A1").Value = "" Then z = 0 Else z = Range("A1").End(xlDown).Row End If Set rs = New Recordset rs.Open "クエリＡ", cn, adOpenKeyset, adLockOptimistic For i = 1 To z rs.AddNew rs!番号 = Range("A" & i).Value rs!日付 = Range("B" & i).Value rs!担当者 = Range("C" & i).Value rs!顧客名 = Range("D" & i).Value rs!内容 = Range("E" & i).Value rs.Update Next MsgBox "更新しました" '後処理 rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub *********************************************************** という構文を作ってみたのですが、 rs.Open "クエリＡ", cn, adOpenKeyset, adLockOptimisticのところで ストップし、SQLステートメントがただしくありません。 'DELETE'、'INSERT'、'PROCEDURE'、'SELECT'、または'UPDATE'を使用してください。というエラーになります。 持っている書籍にはこのことについて何も記述が無いので、ネット検索しながら独学で解決方法を探しているのですが、めぼしい記述に出会えず、困っています。 解決方法をご存知の方、また参考になる書籍・サイトをご存知の方、お力をお貸しください。 OS：windowsXP Excel:2003 Access:2003

  • ExcelVBA Accessにデータ書き込み

    VBAでコマンドボタンを押した際に特定のセルの値をAccessDBに入力するプログラムを作りたいのですが、上手くいきません...。 実行した際に「実行時エラー '21472179000 (80040e 14)':　オートメーションエラーです。」と表示されます。 また、ステップインで実行してみるとEnd Withのところでエラーが発生します。 恐らくインサート文が間違っていると思うのですが、試行錯誤しても解決できませんでしたので教えて頂きたいです。 以下プログラムです。 Private Sub CommandButton1_Click() Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim constr As String Dim strSQL1 As String Dim a As String a = Range("A1").Value Dim b As String b = Range("A2").Value Dim c As String c = Range("A3").Value constr = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=～.accdb strSQL1 = "insert into " & _ "TableName (1,2,3) " & _ "values ('" + Range("A1").Value + "','" + Range("A2").Value + "','" + Range("A3").Value + "')" Set cn = New ADODB.Connection cn.ConnectionString = constr cn.Open Set cmd = New ADODB.Command With cmd .ActiveConnection = cn .CommandText = strSQL1 .Execute End With Set cmd = Nothing Set rs = Nothing cn.Close Set cn = Nothing End Sub 以上、宜しくお願い致します。

  • エクセル側からアクセスへデータ転送

    エクセル側からアクセスへデータを転送したく、 Sub Data_Add() Dim db As New ADODB.Connection Dim Rs As New ADODB.Recordset db.Open _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Users\Owner\Desktop\A.mdb;" Rs.Open "B", db, adOpenStatic, adLockPessimistic Rs.AddNew Rs!a = Worksheets("1").Range("A1").Value Rs!b = Worksheets("1").Range("B1").Value Rs!c = Worksheets("1").Range("C1").Value Rs!d = Worksheets("1").Range("D1").Value Rs!e = Worksheets("1").Range("E1").Value Rs.Update Rs.Close db.Close これで、エクセルからアクセス"A"のテーブル"B"のフィールド"a"にエクセルの"A1"を、フィールド"b"にエクセルの"B1"をという風に、セル1つづつを転送させる事はできましたが、セル1つづつでは無く、Ａ列をフィールド"a"に、B列をフィールド"b"にという風に、列ごと追加するにはどの様にしたらいいですか？ 又、追加ではなく更新（すでにあったデータに追加するのではなく、上書き）するにはどの様にしたらいいですか？ 追加、更新それぞれしたいので、誰か教えて頂けませんか？ よろしくお願いします。

  • VBAのデバックをどなたかお手伝いください。

    もちろん自分でも調べてはいるのですが、急いでいるため、もしどなたか教えてくだされば大変助かります。 この（下記の）Then 以降からがわかりません。 Do Until rs.EOF '該当レコードあり If rs!MCD = "3162" Then '--------------------------------------------- strcriteria = "CAT = '" & rs!CAT & "'" ' --- A rs2.Find strcriteria, 0, adSearchForward If rs2.EOF Then ' Else rs!仕入単価世代1 = rs!仕入単価 rs!仕入単価 = rs2!discount End If '--------------------------------------------- rs!更新日 = Now() rs.Update End If 情報が不足していればお答えします。どうぞ宜しくお願いいたします。 （補足）これより前に入力されているのは以下のものです。 Dim cn As ADODB.Connection Dim cn2 As ADODB.Connection Dim rs As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim strmsg As String Dim lngRet As Long Dim strcriteria As String Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset Set cn2 = CurrentProject.Connection Set rs2 = New ADODB.Recordset rs.Open "商品２_T", cn, adOpenKeyset, adLockOptimistic rs2.Open "商品２_T25discountてすと", cn2, adOpenKeyset, adLockOptimistic

  • SQLサーバーから抽出したデータの並び替え

    SQLサーバーから抽出したデータの並び替え（降順）をVBAを使って行おうとしてます。 以下にマクロ詳細を示します。 --------------------------------------------------- 　 Worksheets("Sheet1").Range("A4:HT20").ClearContents Worksheets("Sheet2").Range("D4") = "" Worksheets("Sheet2").Range("D5") = "" Worksheets("Sheet2").Range("D6") = "" Worksheets("Sheet2").Range("D8") = "" Worksheets("Sheet2").Range("D10") = "" Worksheets("Sheet2").Range("D12") = "" Worksheets("Sheet2").Range("E16") = "" Worksheets("Sheet2").Range("E18") = "" 'Table1に最新の規格値を表示させる Dim CN As Connection 'Connectionオブジェクト Dim rs As Recordset 'Recordsetオブジェクト Dim SelCmd As String 'データを抽出するSQLステートメント 'DBに接続 Set CN = New Connection CN.ConnectionString = "Driver={SQL Server};" & _ "server=○○○.○○.○○.○○; database=DB ; uid=ID; pwd=1234;" CN.Open 'Lotの入力 Dim MLot As String MLot = InputBox("Lotを入力　例 12AB") Sheets("Sheet1").Range("B1") = MLot SelCmd = "SELECT * FROM PD " _ & "WHERE Lot = '" & Sheets("Sheet1").Range("B1").Value & "'" 'データを取得 Set rs = New Recordset rs.Open SelCmd, CN 'シートに貼り付け Sheets("Sheet1").Range("A4").CopyFromRecordset rs '後処理 rs.Close Set rs = Nothing CN.Close Set CN = Nothing ☆☆☆ 　　'データを降順に並び替え 　　Sheets("Sheet1").Select → 　Range("A3:HT20").Sort Key1:=Range("D4"), Order1:=xlDescending, header:=xlGuess ☆☆☆ -------------------------------------------------------- 実行時エラー’1004’ RangeクラスのSortメソッドが失敗しました。とエラーメッセージが出て、 →の部分が黄色く表示されてデバックモードになってしまいます。 新しいエクセルBookで新規で開いて、データを貼り付け☆☆☆に挟まれた部分だけを、 マクロではしらせてみると、無事にデータを降順に並べ替えてくれました。 なぜか、目的とするエクセルファイル内のマクロに取り入れるとエラーが出るのかが わかりません。 解決方法が分かる方いらっしゃいましたら、教えて下さい。 宜しくお願い致します。

  • ACCESS VBA

    ACCESSで検索フォームを作りたいと思っています。 VBAを使って行きたいと思うのですが、うまくいきません。 希望としては、該当するレコードのデータを抽出したいです。 よろしくお願いいたします。 ※現段階でのソースを書いてみました。 最終的に行いたい処理とは違うのですが、根本的に間違っているようなので簡略化しました。 /------------------------------------------------/ Private Sub コマンド1_Click() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String '接続 Set cn = CurrentProject.Connection 'レコードセットを取得 Set rs = New ADODB.Recordset sql = "SELECT * FROM 従業員データ " & _ "WHERE 年齢=30" rs.Open sql, cn, adOpenDynamic, adLockReadOnly rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub /------------------------------------------------/

  • （ADO）フィールド名から取得したい

    ADOを使ってエクセルからアクセスのデータを取得しています。 （2003です。） Sub アクセスのデータをエクセルへ() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim i As Long Range("a1") = "番号" Range("b1") = "種類" rs.Open "SELECT * FROM Tbl", cn, adOpenKeyset, adLockOptimistic For i = 1 To rs.RecordCount Range("a" & i + 1) = rs("番号") Range("b" & i + 1) = rs("種類") rs.MoveNext Next i End Sub 結果は 　　　A　　　　　B 1　　番号　　　種類 2　　××　　　○○ となるのですが コート゛の Range("a1") = "番号" Range("b1") = "種類" の部分を省略して テーブルのフィールド名から取得できないでしょうか？ 例は２つのフィールドしかないから楽なのですが 実際はTblには３０列くらいあるのです。 アクセスのOffice Linksや アクセスからメニュー［ファイル］－［エクスポート］のような結果がほしいです。 どうしてもADOでやりたいので どなたかご教授よろしくお願いします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する