[Excel]ADODBでNull変換されてしまう
- ExcelのマクロでADODBを使ってデータを取得しようとすると、文字列型の数値がNullに変換される現象が発生する。この問題の解決方法はあるか。
- ExcelのマクロでADODBを使用してデータを取得する際、文字列型の数値がNullに変換されてしまう問題が発生する。この問題の解決策を教えてください。
- ExcelのマクロでADODBを使ってデータを取得すると、データの一部がNullに変換される現象が発生する。この問題を解決する方法を教えてください。
- ベストアンサー
[Excel]ADODBでNull変換されてしまう
お世話になります。 今、Office2003で次のようなマクロを作成しています。 マクロが保存されているブックM、 参照するデータが保存されているブックD、 出力先のブックOがあり、 ブックMにてADODBをつかって、ブックDからSQLによる条件でブックOへ出力します。 このとき、ブックDのセルではちゃんとデータがセットさせているのに 出力したブックOでは空になってしまうという現象が発生して困っています。 デバッグ等して確認してみたところ、 書式が標準のセルで、文字列型の数値(文字列型のセルから値コピーした場所)が入っていると RecordSetにとれた段階でNullに変換されてしまてしまっているように見えました。 この問題を解決するために何かよい方法はありますでしょうか? ソースコードの詳細は確認できないのですが こんな感じだったと思います。 ※ rs.Requeryにて、rsの中身を確認した時点でNullだったので QueryTablesによる取り込みは関係ないと考えています。 Set conn = CreateObject("ADODB.Connection") conn.Open ConnectString Set sql = CreateObject("ADODB.Command") sql.CommandType = 1 sql.ActiveConnection = conn sql.CommandText = SQL文(? とUNION含む) sql(0) = パラメータ設定 ... Set rs = CreateObject("ADODB.RecordSet") Set rs = sql.Execute With Sheet1.QueryTables.Add(rs, "A1") .AdjustColumnWidth = False .FieldNames = True .BackgroundQuery = False .Refresh False .Delete End With ...
- tsuduki123
- お礼率69% (9/13)
- Visual Basic
- 回答数5
- ありがとう数7
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
度々失礼。 http://support.microsoft.com/kb/194124/ja 全てが強制的に文字列に変わるようですが With conn .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Extended Properties").Value = "Excel 8.0;IMEX=1" '■ .Properties("Data Source") = "c:\temp\data.xls" .Open End With ..『オプション IMEX=1;』を追加する方法でもいいかもしれません。 さっきのSub try()のQueryTableオブジェクトだけで使う場合はConnectionを以下に変更です。 wkCON = "OLEDB;" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\temp\data.xls;" & _ "Extended Properties=""Excel 8.0;IMEX=1;"""
その他の回答 (4)
- end-u
- ベストアンサー率79% (496/625)
間違いました..orz With wkBook.Sheets(1) .Range("A1").Value = "区分" .Range("A2").Value = "追加" .Range("A3").Value = "更新" .Range("A4").Value = "削除" .Range("B1").Value = "都道府県コード" .Range("B2:B4").Value = "1" Set Criteria = .Range("A1:B4") End With
- end-u
- ベストアンサー率79% (496/625)
..ぅ。失礼...orz QueryTableオブジェクトでも混在データはNullを返す? #元からそんな仕様だったっけかな... 代案で Sub try2() Const BKDATA = "c:\temp\data.xls" Const BKOUT = "out.xls" Dim wkBook As Workbook '抽出条件作業Book Dim CopyTo As Range Dim Criteria As Range Application.ScreenUpdating = False '抽出先 With Workbooks(BKOUT).Sheets("sheet1") .UsedRange.Clear Set CopyTo = .Range("A1") End With '抽出条件 Set wkBook = Workbooks.Add(xlWBATWorksheet) With wkBook.Sheets(1) .Range("A1:C1").Value = "区分" .Range("A2").Value = "追加" .Range("B3").Value = "更新" .Range("C4").Value = "削除" .Range("D1").Value = "都道府県コード" .Range("D2:D4").Value = "1" Set Criteria = .Range("A1:D4") End With '抽出元 With Workbooks.Open(BKDATA, ReadOnly:=True) .Sheets("住所録").Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Criteria, _ CopyToRange:=CopyTo, _ Unique:=False .Close False End With wkBook.Close False Application.ScreenUpdating = True Set Criteria = Nothing Set CopyTo = Nothing Set wkBook = Nothing End Sub ScreenUpdatingプロパティで画面表示制御しOpenを意識させないようにして、 オーソドックスにAdvancedFilterメソッド([フィルタオプションの設定])を使うと良いです。
- end-u
- ベストアンサー率79% (496/625)
>SQLはたとえばこんな具合のものです。 あくまで例えば、という事で実際はもっと複雑だったりしますか? もし例示通りで良ければ Sub try() Const BKDATA = "c:\temp\data.xls" Const BKOUT = "out.xls" Dim wkSHT As Worksheet Dim wkQRY As QueryTable Dim wkCON As String Dim wkSQL As String Dim p As String p = "?" wkCON = "ODBC;DSN=Excel Files;DBQ=" & BKDATA wkSQL = "SELECT * FROM [住所録$] WHERE 区分='追加' AND 都道府県コード=" & p & _ " UNION ALL " & _ "SELECT * FROM [住所録$] WHERE 区分='更新' AND 都道府県コード=" & p & _ " UNION ALL " & _ "SELECT * FROM [住所録$] WHERE 区分='削除' AND 都道府県コード=" & p Set wkSHT = Workbooks(BKOUT).Sheets("sheet1") Set wkQRY = wkSHT.QueryTables.Add(Connection:=wkCON, _ Destination:=wkSHT.Range("A1"), _ Sql:=wkSQL) With wkQRY .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = False .Refresh BackgroundQuery:=False wkSHT.Names(.Name).Delete '名前定義削除 .Delete 'QueryTable削除(上の行とセットで必要に応じ。) End With Set wkSHT = Nothing Set wkQRY = Nothing End Sub こんな感じになりますが。 または、SQL文は以下でも同じかと。 wkSQL = "SELECT * FROM [住所録$]" & _ " WHERE (区分='追加') AND (都道府県コード=" & p & ")" & _ " OR (区分='更新') AND (都道府県コード=" & p & ")" & _ " OR (区分='削除') AND (都道府県コード=" & p & ")"
- end-u
- ベストアンサー率79% (496/625)
>書式が標準のセルで、文字列型の数値(文字列型のセルから値コピーした場所)が入っていると.. 書式ではなく、データ型の混在が原因だと思われます。 [HOWTO] Visual Basic または VBA から ADO を Excel データで使用する http://support.microsoft.com/kb/257819/ja より抜粋。 >両方の OLE DB プロバイダに適用される考慮事項 >データ型の混在についての注意 > >「スキャンする行数」で説明したとおり、ADO は Excel のワークシートまたは範囲に含まれる各列のデータ型を推測する必要があります (これは Excel のセル書式設定には影響されません)。同じ列に数値と文字列値が混在していると重大な問題が発生することがあります。Jet プロバイダと ODBC プロバイダはどちらも、最も数が多い型についてはそのデータを返し、その他の数が少ないデータ型については NULL (空) 値を返します。2 つの型が列内で同数の場合、文字列ではなく数値が返されます。 そもそも、ADODB.RecordSetを経由せず、 QueryTablesでダイレクトにxlsファイルを読み込めば良いと思いますが、 何か事情があるのでしょうか?
お礼
ありがとうございます。 やっぱり、混ざっていることが原因ですか。。。 できれば、エラーとして検出するよりも許容する方向へ倒したいです > そもそも、ADODB.RecordSetを経由せず、 > QueryTablesでダイレクトにxlsファイルを読み込めば良いと思いますが、 > 何か事情があるのでしょうか? 手抜きといってしまえばそれまでなのですが、 抽出する条件が、複数パターンあってそれを結合して取り込む必要があるためです。 QueryTablesでは全部を取り込んでしまうので条件指定は出来ず また、オートフィルタをかけたものを取り込むにしても複数ある関係で 毎回、1行目のタイトル行を削除したりしなければならないのかなーと SQLはたとえばこんな具合のものです。 SELECT * FROM [住所録$] WHERE 区分='追加' AND 都道府県コード=? UNION ALL SELECT * FROM [住所録$] WHERE 区分='更新' AND 都道府県コード=? UNION ALL SELECT * FROM [住所録$] WHERE 区分='削除' AND 都道府県コード=?
関連するQ&A
- ソートの設定
ASPでアクセス97を使って品番、納期という順番に表示させたいのですが うまくいきません。 SQL文を使って品番順にはならんだのですが、納期がばらばらになってしまいます。どうしたら品番、納期順という並びになるか教えて下さい。 Dim Conn,SQL,RS xxxx=server.mappath("*****.mdb") DBName="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & xxxx Set Conn = Server.CreateObject("ADODB.Connection") Conn.open DBName SQL = "select * from ******" SQL = SQL & " order by 品番 asc" set RS = Server.CreateObject("ADODB.Recordset") RS.Open SQL, Conn,3,3
- ベストアンサー
- Microsoft ASP
- ASPでORACLEのエラーが発生するのですが、対処方法をお教えください。
Win2000(IIS) + ORACLE8.1.6 でASPを使ったWEBサーバを構築しようとしています。 まず手始めに、単純にデータを登録するような簡単なASPを作成しようとしているのですが、 下記処理が、133行目でエラーになってしまいます。 ※当然の事ながら、ODBC接続の設定は正常に行えていることを確認しております。 (Accessでリンクテーブルを使って、ODBC経由でテーブルにデータを追加できます。) <処理内容 (/Oracle/newtoroku.asp の一部)> 126行目 Dim Conn,SQL,RS 127行目 DBName="Provider=msdaora;Data Source=xxxxx;User ID=xxxxx;Password=xxxxx" 128行目 Set Conn = Server.CreateObject("ADODB.Connection") 129行目 Conn.open DBName 130行目 SQL = "select * from XXテーブル" 131行目 Set RS = Server.CreateObject("ADODB.Recordset") 132行目 RS.Open SQL, Conn,3,2 133行目 rs.AddNew <エラー内容> ADODB.Recordset (0x800A0CB3) 現在の Recordset は更新をサポートしていません。 プロバイダか、選択されたロックタイプの限界の可能性があります。 /Oracle/newtoroku.asp, line 133
- 締切済み
- その他(データベース)
- VBSの中で書くSQL文の記述方法
よろしくお願いします。 select * from db1 into id,password,date,name where db1_id = **** and db1_password = **** and db1_date = 99999999; (db1_id,db1_password,db1_dateがプライマリキー) db1という表からプライマリキーに該当したデータを取り出し、 変数に格納したいと思っています。 このSQL文をASPのVBSの中で実現する方法を教えてください。 <% Dim Conn,SQL,RS Dim id Dim password Dim date Dim name db1=server.mappath("db1.mdb") DBName="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & db1 Set Conn = Server.CreateObject("ADODB.Connection") Conn.open DBName SQL = "select * from db1" INTO = "into id,password,date,name" WHERE = "where db1_id = **** and db1_password = **** _ and db1_date = 99999999" SQL = SQL & INTO & WHERE & ";" Set RS = Server.CreateObject("ADODB.Recordset") RS.Open SQL, Conn,3,3 %> これでうまくいくかな~と思ったのですが・・・。
- 締切済み
- Microsoft ASP
- JScriptでDBへの接続方法について
JScriptを使用してDBへの接続方法を教えて下さい。 var conn = Server.CreateObject("ADODB.Connection"); var rs = Server.CreateObject("ADODB.Recordset"); conn.Open; rs.Open("sheet2", "DRIVER={Microsoft Excel Driver (*.xls)}; DBQ =" + Server.Mappath("tes.xls")); sSQL = "SELECT * FROM sheet2"; からの接続がうまくいきません。 どこがいけないのでしょうか? それとも、全てがいけないのでしょうか? よろしければ、どなたかご教授下さい。 お願いします。
- ベストアンサー
- Microsoft ASP
- Access VBA フォームに表示したい
Access2000で、帳票フォームにSQLの値を表示させたいのですが、 以下のコードだとフォーム上に最後のレコードしか表示されません。 取得したデータを正しく表示するにはどうしたらよいでしょうか。 ・フォームのtext1のコントロールソースにfld1と書く 以外の方法でできますか? できればすべて非連結で、作成したいです。 (本当はフォームのrecordsourceも設定したくないのですが それは無理でしょうか?) よろしくお願いいたします。 Private Sub Form_Load() Dim conn As Connection Dim rs As ADODB.Recordset Dim strSql As String Set conn = CurrentProject.Connection Set rs = New ADODB.Recordset strSql = "select * from table1" rs.Open strSql, conn, adOpenKeyset, adLockOptimistic, adCmdText Me.RecordSource = strSql rs.MoveFirst Do Until rs.EOF me!text1 = rs!fld1 rs.MoveNext Loop rs.Close Set rs = Nothing conn.Close Set connn = Nothing End Sub
- ベストアンサー
- Visual Basic
- EXCEL→SQL VBAを簡略化したい
EXCELのVBAを使って、SQLサーバーに接続し、 データテーブルを開き、データを200個(xが100個、yが100個) 書き込みさせたいプログラムを作っています。 強引に1つ1つ書き込みし、200個登録は出来たのですが、 もっとプログラムを簡略化したいと思います。 以下に内容をしめします。 ------------------------------------------- 'SQLデータベースに接続 Set rs = CreateObject("ADODB.RecordSet") Set CN = CreateObject("ADODB.Connection") CN.ConnectionString = "Driver={SQL Server};" & _ "server=○○○.○○.○○.○○; database=DB ; uid=ID; pwd=1234;" CN.Open '-----[Dataテーブル] ----- Set rs = New Recordset rs.Open "Data", CN, adOpenKeyset, adLockOptimistic '[Dataテーブル]に書込み rs.AddNew Worksheets("Sheet1").Select '☆☆☆ rs!x1 = Cells(10, 5).Value rs!x2 = Cells(11, 5).Value rs!x3 = Cells(12, 5).Value rs!x4 = Cells(13, 5).Value ・ ・ ・ ・ rs!x100 = Cells(109, 5).Value rs!y1 = Cells(10, 7).Value rs!y2 = Cells(11, 7).Value rs!y3 = Cells(12, 7).Value rs!y4 = Cells(13, 7).Value ・ ・ ・ ・ rs!y100 = Cells(109, 7).Value '☆☆☆ '保存 rs.Update MsgBox "追加しました" '後処理 rs.Close: Set rs = Nothing CN.Close: Set CN = Nothing '終了処理 Set rs = Nothing Set CN = Nothing ------------------------------------------- '☆☆☆で挟まれた部分を繰り返し文で簡潔にプログラミングしたいです。 自分が考えた以下の分では、エラーが出てしまいました。 i = 1 n = 10 Do While i < 100 rs!x"& i &" = Cells(n, 5) rs!y"& i &" = Cells(n, 7) i = i + 1 n = n + 1 Loop よりシンプルな構文にしたいと思います。 お手数おかけいたしますが、わかる方いらっしゃいましたら 教えて下さい。
- ベストアンサー
- Visual Basic
- ASPでRecordCountが使用できない
Accessの100件のデータを10行づつブラウザに表示したいのですが、rs.PageCount、rsRecordCountなど色々と試しましたが正しい結果が得られず 困っています。 rs.AbsolutePage、Rs.PageSizなども試してみましたが、やはり結果は-1しか返されません。 Set db1=Server.CreateObject("ADODB.Connection") db1.Provider="Microsoft.Jet.OLEDB.4.0" db1.ConnectionString=Server.MapPath("../master.mdb") db1.Mode=1 db1.Open ' Set rs1=Server.CreateObject("ADODB.Recordset") Rs1.Pagesize = 5 ' keyword = "ABC" ' SQL1a = "SELECT top 5 * FROM dbFILE WHERE dbTABEL Like '%" & Keyword & "%'" Set Rs1=db1.Execute(SQL1a) Response.Write rs1.PageCount このようなリストで試しています。よき方法があれば是非 教えて下さい。 最終的にはSQL 7.0に置き換える予定です。 宜しく、お願いします。
- ベストアンサー
- Microsoft ASP
- recordsetが取得できなかった場合
Access2003のVBAについて質問です。 recordsetが取得できなかった場合、どういった値が返されるのでしょうか? やりたい事は、recordsetが取得できた時、できなかった時でメッセージの表示を変えたいです。 ご教授よろしくおねがいします。 Public Sub Exsample() 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 クラス = 'TS'" RS.Open SQL, CN, adOpenKeyset, adLockOptimistic
- ベストアンサー
- その他(データベース)
- 【EXCEL VBA】ローカルmdbからデータを取得したい
(環境) WindowsXP Excel2003 Access2003 現在、SQLサーバーからデータを取得しています。 下記のソースです(一部抜粋) Private Const SRC_SQL = "Provider=SQLOLEDB.1;User ID=testid;Password=testpass;Data Source=TEST-DB-1;Initial Catalog=testDB" Private Const TBL_TEST = "TEST.テストテーブル" Public Sub TEST_PRO Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = CreateObject("ADODB.Connection") cn.CommandTimeout = 0 cn.Open SRC_SQL strSQL = "SELECT X.*, FROM " & TBL_TEST & " X" strSQL = strSQL & " WHERE X.担当者CD = '" & wNAME & "'" strSQL = strSQL & " AND X.オープン日 >= '" & start_dt & "'" strSQL = strSQL & " AND X.オープン日 < '" & end_dt & "'" strSQL = strSQL & " ORDER BY X.オープン日 ASC" Set rs = CreateObject("ADODB.Recordset") rs.Open strSQL, cn With rs ~~~ End With Set rs = Nothing End Sub これを、SQLサーバーではなく、 C:\TESTACCESS.mdbのテーブル:テストテーブル からデータを取得するように変更したいのですが、 どのようにコーディングすればよろしいでしょうか? よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 2次元のdictionary
こんにちは。 recordsetの結果をDictionaryにいれたいと思っています。 環境はWindows Vista、vbScriptで書いています。 set dc = createobject("scripting.dictionary") Set rs = Server.CreateObject("ADODB.Recordset") rs.open (sql文), con for i = 0 to rs.recordcount -1 for j = 0 to rs.fields.count - 1 dc.add rs.fields(j).name, rs.fields(j).value next next ちょっとイメージっぽく書きましたが(このままではエラーでます)、要は複数のフィールドを持つ複数のレコードを入れられないかということです。 色々調べたりしてみたのですがわかりません。 できないのかな?と思いました。 もしできるなら書き方をご教授頂けたらと思います。 よろしくお願いします。
- 締切済み
- Visual Basic
お礼
お世話になります。試してきました。 結論から言うと100%の解決は無理でした。 どうやら、HDR=YESとしてしまうと、IMEX=1としても 最初の数行の内で数の多い型になってしまうようです。 ※ 最初の10行の型部分をテキストに、 なおすとHRD=YESでもその後ろが混ざっていた場合もちゃんととれました。 仕方ないので、HRD=NO;IMEX=1;としタイトル部分を配列に格納しておいて、 2行目にSQLの結果を挿入した後、1行目にタイトルを手動で設定する方法で解決しました。 ありがとうございました。
補足
お世話になります! 困っていた内容はこれかもしれません! 月曜日に本物でも、試してみてまたご連絡させていただきます。 値として数値っぽものが入っているだけでその扱いはテキストなので その違いによる問題はでない見込みです。 その他、回答いただいた内容ですが、 QueryTablesでもSQLを指定できることは知りませんでした。 UNION ALLを利用したのは、ただの習慣です。