人に聞けない痔の悩み、これでスッキリ >>

お世話になります。
今、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
...

このQ&Aに関連する最新のQ&A

A 回答 (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;"""

この回答への補足

お世話になります!
困っていた内容はこれかもしれません!

月曜日に本物でも、試してみてまたご連絡させていただきます。
値として数値っぽものが入っているだけでその扱いはテキストなので
その違いによる問題はでない見込みです。

その他、回答いただいた内容ですが、
QueryTablesでもSQLを指定できることは知りませんでした。
UNION ALLを利用したのは、ただの習慣です。

補足日時:2011/03/26 22:15
    • good
    • 3
この回答へのお礼

お世話になります。試してきました。

結論から言うと100%の解決は無理でした。
どうやら、HDR=YESとしてしまうと、IMEX=1としても
最初の数行の内で数の多い型になってしまうようです。

※ 最初の10行の型部分をテキストに、
  なおすとHRD=YESでもその後ろが混ざっていた場合もちゃんととれました。

仕方ないので、HRD=NO;IMEX=1;としタイトル部分を配列に格納しておいて、
2行目にSQLの結果を挿入した後、1行目にタイトルを手動で設定する方法で解決しました。

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

お礼日時:2011/03/28 19:41

間違いました..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
    • good
    • 0

..ぅ。

失礼...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メソッド([フィルタオプションの設定])を使うと良いです。
    • good
    • 0

>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 & ")"
    • good
    • 0

>書式が標準のセルで、文字列型の数値(文字列型のセルから値コピーした場所)が入っていると..


書式ではなく、データ型の混在が原因だと思われます。

[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ファイルを読み込めば良いと思いますが、
何か事情があるのでしょうか?
    • good
    • 0
この回答へのお礼

ありがとうございます。
やっぱり、混ざっていることが原因ですか。。。
できれば、エラーとして検出するよりも許容する方向へ倒したいです

> そもそも、ADODB.RecordSetを経由せず、
> QueryTablesでダイレクトにxlsファイルを読み込めば良いと思いますが、
> 何か事情があるのでしょうか?

手抜きといってしまえばそれまでなのですが、
抽出する条件が、複数パターンあってそれを結合して取り込む必要があるためです。

QueryTablesでは全部を取り込んでしまうので条件指定は出来ず
また、オートフィルタをかけたものを取り込むにしても複数ある関係で
毎回、1行目のタイトル行を削除したりしなければならないのかなーと

SQLはたとえばこんな具合のものです。
SELECT * FROM [住所録$] WHERE 区分='追加' AND 都道府県コード=?
UNION ALL
SELECT * FROM [住所録$] WHERE 区分='更新' AND 都道府県コード=?
UNION ALL
SELECT * FROM [住所録$] WHERE 区分='削除' AND 都道府県コード=?

お礼日時:2011/03/26 00:14

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q抽出条件でデータ型が一致しません。のエラーメッセージが出る

フォームで入力された値を、
次のファイルでクエリを読み込むときに代入するASPを作成しましたが、実行しようとすると、
「データ型が一致しません。」のエラーメッセージが出ます。
DBはACCESSを使用しています。ACCESSの対象テーブルで、
データ型を「テキスト型」にすると問題ないのですが、
「数値型」にすると、「データ型が一致しません」の
エラーメッセージになります。
フォームでは、プルダウンで「数値」を選択するようになっています。
宜しくお願いします。

Aベストアンサー

Where区に指定した条件のフィールドが数値なら『'』でくくる必要がありません

Set rs = db.Execute("SELECT テーブル3.* FROM テーブル3 WHERE (テーブル3.番号)=" & bangou)
といった具合に修正してみましょう

『'』でくくる必要があるのは対象のフィールドが文字列の場合です

QEXCEL VBAで計算値を四捨五入、切り上げ、切捨てする方法

ネットで探してみたのですが、計算結果を四捨五入して特定のセルを
返すにはどうしたらいいのでしょうか?

Sub hokangosa()

Dim ZPS As Double
Dim ZPOS As Double
Dim DMN As Double
MsgBox (" >>> 補間誤差自動計算 <<< ")
MsgBox (" >>> 初期値入力します <<< ")
ZPS = InputBox(">>> ステップを入力してください<<<")
ZPOS = Sheet1.Cells(22, 4).Value
DMN = ZPOS / ZPS
Sheet1.Cells(23, 6).Value = DMN
End Sub

ここでDMNの値を四捨五入したいです。

またこれとは別に切上げ、切捨ても教えていただけるとありがたいです。

Aベストアンサー

DMN = Application.WorksheetFunction.Round(ZPOS / ZPS, 0)
で、四捨五入
DMN = Application.RoundDown(ZPOS / ZPS, 0)
で切り捨て
DMN = Application.RoundUp(ZPOS / ZPS, 0)
で切り上げです。

引数で、対象桁を変更できます。

Q「Nullの使い方が不正です」のエラー

AccsessとVB間でデータの参照・登録・削除を行うプログラムを作成中です。
テキストボックスにデータを入力せずに更新するとAccess側はNull値が入るので、参照をした際に
「Nullの使い方が不正です」のエラーが表示されます。

Null値が表示可能ならその方法を教えてください。

Aベストアンサー

読み込んだ際にIsNull()で判断してみては?
If IsNull(Fields) Then
Text1.Text = ""
Else
Text1.Text = Fields
End If

QVBA オブジェクトが空かどうか判定する

皆様のお知恵を拝借させてください。

エクセルVBAでオブジェクトを入れる変数を定義し、その変数にオブジェクト
が入っているかどうか検査したいのですがどうしたらいいでしょうか。

例えば---
Dim a As Workbook
If a <> nothing then ←この部分が分からない。このままだとエラー。
処理
End if
---------
環境
エクセル2003
WinXPsp1

Aベストアンサー

もし、aが空だったら
If a Is Nothing Then 

もし、aが空じゃなかったら
If Not a Is Nothing Then

QADOでNullフィールドの抽出

VB6.0+ADO2.7で、mdbファイルを扱っています。
.filterプロパティを使ってabcフィールドの値がnullまたは空のテーブルを抽出したく
recordset.filter="abc= null or abc=''"
としました。この場合は問題ないのですが、更にdefフィールドの条件を追加し
recordset.filter="(abc= null or abc='') AND def= 'xyz'"
としたところ
「実行時エラー3001
引数が間違った型、許容範囲外、または競合しています。」
というエラーが発生してしまいます。
また、
recordset.filter="abc= null AND def= 'xyz'"
或いは
recordset.filter="abc='' AND def= 'xyz'"
では問題ありません。
何が悪いのでしょうか?
よろしくお願いします。

Aベストアンサー

http://msdn.microsoft.com/library/ja/default.asp?url=/library/ja/jpado260/htm/mdprofilter.asp

>次のように、OR で句を結合してできた句のグループを、AND を使ってさらに別の句と結合することはできません。
>(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

こちらの情報によると、ダメみたいですね。
Filter プロパティは制約が多いみたいで、where句のように自由に条件は書けないみたいですね。

> Null を使うことはできません。
さらに、こういう記述もあります。

SQL文のwhere句を利用した方が良いかも知れませんね。

QExcel VBA ADOでのCSV取込みについて

下記は、Excel VBAでADOを使って、CSVデータを取り出すソースです。ソースは、とあるサイトからほぼ丸写しです。

Sub main()
  Const DRIVER As String = "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ="
  Const PROVIDER As String = "Provider=MSDASQL;Extended Properties="""
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim idx As Integer
  Dim strSQL As String

  cn.ConnectionString = PROVIDER & DRIVER & "C:\"""
  cn.Open

  '全件数取得
  strSQL = "SELECT * FROM Sample.csv"

  'CSVファイルの内容を取得
  Set rs = cn.Execute(strSQL)

  rs.MoveFirst

  Do Until rs.EOF
    For idx = 0 To rs.Fields.Count - 1
      Debug.Print rs.Fields(idx).Value '←ここ
    Next idx
    rs.MoveNext
  Loop

  Set rs = Nothing
  cn.Close
  Set cn = Nothing
End Sub


ここで、「'←ここ」と示した行のrs.Fields(idx).Valueって、実際には「001」と書かれた値は、ダブルクォーテーションでも入ってない限りは「1」と変換されちゃいますよね?これをちゃんと、実際の値「001」のまま取得することって出来ないのでしょうか?

下記は、Excel VBAでADOを使って、CSVデータを取り出すソースです。ソースは、とあるサイトからほぼ丸写しです。

Sub main()
  Const DRIVER As String = "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ="
  Const PROVIDER As String = "Provider=MSDASQL;Extended Properties="""
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim idx As Integer
  Dim strSQL As String

  cn.ConnectionString = PROVIDER & DRIVER & "C:\"""
  cn.Open

  '全件数取得
 ...続きを読む

Aベストアンサー

schema.ini ファイル(データ型定義)を作ってやれば安心かと。
http://www.ken3.org/cgi-bin/test/test102-1.asp

http://www.geocities.co.jp/SiliconValley/4805/vbtips/vbtips118.htm

PS.
CopyFromRecordset についても調べると、吉かも?

QACCESSのSQLで数値型に変換するには

こんにちわ。今SQLの書き方が分からなくて大変困っております。
ACCESS2000で、テーブルが一つあるとします。
テーブルの内容は、氏名・点数の2項目だとします。

点数が50点より大きい人を抽出したいのですが、
項目は全てSTRING型にしないといけないため、
単純に比較できません。抽出した項目を数値型に
変換して、比較したいのですが、どのように書いたらいいのでしょうか。

SELECT  Shimei , CAST(Tensuu AS NUMERIC)
FROM   Table1
WHERE   Tensuu > 50;

このようなクエリを書いてみましたがエラーが出てしまいます。
ORACLEではTO_NUMBERなどの関数があると思うのですが、
ACCESSではどのようにしたらいいのでしょうか??
よろしくお願いします。

Aベストアンサー

この程度か?

SELECT テーブル1.Shimei, CInt([Tensuu]) AS 得点
FROM テーブル1
WHERE (((CInt([Tensuu]))>50));

それではヘルプでCIntでも確認してね。

Q初歩的で申し訳ないですが…ADOでExcel検索しています。

質問です。
ADOでExcel内を検索するコードを書いています。
開発環境:Win XP,Excel2003,VB6.0(SP5)
A列が検索列で,検索でヒットした行を抽出します。
Filterプロパティを使って検索していますが、数値を入れるとOKなのに文字列を入れると"種類が一致しません"というエラーが発生してしまいます。
どんな些細な点でもよろしいのでご教授、ご指摘を宜しくお願い申し上げます。

Aベストアンサー

あれこれ実験していたら不可解な事が起きました。
一応報告しますね。

EXCEL Sheet1のA1の列に、
FLD1
----
1
2
3
と入力します。
普通にやると、1,2,3は右揃えに表示されますよね。
この状態でRS.Filter="FLD1='2'"とすると問題のエラーが起きます。
書式設定を文字列に変えても。

そうではなくて、
先にA1列の書式設定を文字列に変更。
上の通り入力。
(こうすると左揃えになります。)
そして、その後書式を標準に戻します。
するとなぜか左揃えのままになります。

という状態でRS.Filter="FLD1='2'"をしても問題のエラーは起きません。
一体何がなんだか??
データを書く前に書式を文字列にしておく必要がある・・??という事なんですかね。

あと、参考URLのページにあるデータ型というところにEXCELの場合のデータ型の扱いがかかれてます。
書式設定がどうって事は書いてなかったですが、データ型を指定する方法は無いって書いてあります。

参考URL:http://support.microsoft.com/default.aspx?scid=kb;ja;278973

あれこれ実験していたら不可解な事が起きました。
一応報告しますね。

EXCEL Sheet1のA1の列に、
FLD1
----
1
2
3
と入力します。
普通にやると、1,2,3は右揃えに表示されますよね。
この状態でRS.Filter="FLD1='2'"とすると問題のエラーが起きます。
書式設定を文字列に変えても。

そうではなくて、
先にA1列の書式設定を文字列に変更。
上の通り入力。
(こうすると左揃えになります。)
そして、その後書式を標準に戻します。
するとなぜか左揃えのままになります。

という状態でRS.Fi...続きを読む

QEXCELファイルのカレントフォルダを取得するには?

EXCELファイルのカレントフォルダを取得するには?

C:\経理\予算.xls

D:\2005年度\予算.xls

EXCEL97ファイルがあります。

VBAで
  カレントフォルダ名
(C:\経理\,D:\2005年度\)
を取得する事は可能でしょうか?

CURDIRでは上手い方法が見つかりませんでした。

Aベストアンサー

こんばんは。
Excel97 でも、同じですね。以下で試してみてください。

Sub test()
'このブックのパス
a = ThisWorkbook.Path
'アクティブブックのパス
b = ActiveWorkbook.Path
'Excelで設定されたデフォルトパス
c = Application.DefaultFilePath
'カレントディレクトリ
d = CurDir
MsgBox "このブックのパス   : " & a & Chr(13) & _
   "アクティブブックのパス: " & b & Chr(13) & _
   "デフォルトパス    : " & c & Chr(13) & _
   "カレントディレクトリ : " & d & Chr(13)
End Sub

QExcelVBAにて外部データ(*.csv)をSQL文を使って抽出する方法

こんにちは

VBAインポート問題で日々悩んでいるものです。

CSV形式のデータをODBCのシステムDSNに登録し、それをDAOでSQL要求しデータを抽出する方法がよくわかりません。

(1)データベースの定義記述内容
(2)レコードセットにSQL命令をかける記述
(3)抽出された内容をワークシートに貼る記述

等がよくわかりません。

DAOの場合、レコードセットを定義するのに
set DB=DBEngine.OpenDatabase("******")があったり、なかったりする理由がわかりません。

突然、set rs = CurrentDb.OpenRecordset("*****")
しているのはなぜでしょうか。

Aベストアンサー

田吾作7@38.6度の風邪引きです。。。
返事が遅くなってすいません。昨日ほとんど寝てました。。。


作りなおしました。

質問の仕様が
1.ODBC
2.EXCELに貼り付け

とあったので、すでにODBCは無視してますが、EXCELに貼り付けは忠実に守ろうとしていました。
今回のは、完全に仕様を無視したつくりになってます。(スピード重視のため)

作りとしては
1.CSVのデータをAccessにテーブルとして取り込む
2.EXCEL形式でエクスポート&ワークブックを開く

もしこれでもいいのであれば、こちらの方が処理が早いと思います。


Sub Main2()
  'CSVファイルのフルパス
  Const csvFullPath  As String = "c:\Folder1\dmy\test.csv"
  'EXCELファイルのパス
  Const excelFullPath As String = "c:\test.xls"
  'CSVファイルを取り込むテーブル名
  Const DmyTbl    As String = "DmyTbl"
  
  
  'CSVファイルを取り込む
  Call inCsv(csvFullPath, DmyTbl)
  
  'EXCEL形式でエクスポート
  Call outExcel(DmyTbl, excelFullPath)
End Sub

Private Sub outExcel(inTblName As String, inXlsFile As String)
  Dim xlsApp   As Excel.Application
  Dim xlsBook   As Excel.Workbook
  
  'エクセル形式でエクスポート
  DoCmd.TransferSpreadsheet acExport, 8, inTblName, inXlsFile, True
  
  
  'エクセル起動をしてエクスポートされたかを確認
  Set xlsApp = New Excel.Application
  Set xlsBook = xlsApp.Workbooks.Open(inXlsFile)
  
  xlsBook.Worksheets(inTblName).Select
  
  xlsApp.Visible = True

  Set xlsBook = Nothing
  Set xlsApp = Nothing
End Sub
Private Sub inCsv(inFileName As String, inTblName As String)
  Dim Db   As DAO.Database
  Dim strSQL As String
  
  Dim strFile As String
  Dim strPath As String
  
  Dim wkVal  As Variant
  
  Set Db = CurrentDb
  
  'とりあえずダミーを削除
  On Error Resume Next
  Db.TableDefs.Delete inTblName
  On Error GoTo 0
  
  'パスとファイル名に分解
  wkVal = Split(inFileName, "\")
  strFile = wkVal(UBound(wkVal))
  strPath = Left(inFileName, Len(inFileName) - Len(strFile))
  
  'SQL文(テーブル作成用)を作成
  strSQL = "select * into " & inTblName & _
      " from " & "[Text;DATABASE=" & strPath & "].[" & strFile & "]"
  Db.Execute strSQL
  
  Set Db = Nothing
End Sub

田吾作7@38.6度の風邪引きです。。。
返事が遅くなってすいません。昨日ほとんど寝てました。。。


作りなおしました。

質問の仕様が
1.ODBC
2.EXCELに貼り付け

とあったので、すでにODBCは無視してますが、EXCELに貼り付けは忠実に守ろうとしていました。
今回のは、完全に仕様を無視したつくりになってます。(スピード重視のため)

作りとしては
1.CSVのデータをAccessにテーブルとして取り込む
2.EXCEL形式でエクスポート&ワークブックを開く

もしこれでもいいのであれ...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング