お世話になります。
今、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
...
No.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を利用したのは、ただの習慣です。
お世話になります。試してきました。
結論から言うと100%の解決は無理でした。
どうやら、HDR=YESとしてしまうと、IMEX=1としても
最初の数行の内で数の多い型になってしまうようです。
※ 最初の10行の型部分をテキストに、
なおすとHRD=YESでもその後ろが混ざっていた場合もちゃんととれました。
仕方ないので、HRD=NO;IMEX=1;としタイトル部分を配列に格納しておいて、
2行目にSQLの結果を挿入した後、1行目にタイトルを手動で設定する方法で解決しました。
ありがとうございました。
No.4
- 回答日時:
間違いました..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
No.3
- 回答日時:
..ぅ。
失礼...orzQueryTableオブジェクトでも混在データは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メソッド([フィルタオプションの設定])を使うと良いです。
No.2
- 回答日時:
>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 & ")"
No.1
- 回答日時:
>書式が標準のセルで、文字列型の数値(文字列型のセルから値コピーした場所)が入っていると..
書式ではなく、データ型の混在が原因だと思われます。
[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が見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) access count数を変数に格納 2 2022/03/30 19:21
- Visual Basic(VBA) エクセルのマクロを使ってメールを送る方法について教えてください 2 2022/03/29 01:36
- Visual Basic(VBA) データベースから絞り込んでデータを読み込み 1 2023/02/21 19:51
- Visual Basic(VBA) Excelのマクロコードについて教えてください。 1 2022/03/27 13:25
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Excel(エクセル) Excelのマクロコードについて教えてください。 1 2022/03/27 10:47
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) 【前回の続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/16 16:44
- その他(Microsoft Office) マクロVBAについて 1 2022/09/06 18:12
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2022/10/13 08:41
このQ&Aを見た人はこんなQ&Aも見ています
-
外出時に「待たせる妻」vs イライラする「待つ夫」は日本だけ?見習いたい海外事情
夫の家事参加に積極的なイメージのある海外でも、同様の事例はあるのか。結婚カウンセラーの佐竹悦子さんに伺ってみた。
-
レコードセットにnullの場合
Visual Basic(VBA)
-
ADOでNullフィールドの抽出
Visual Basic(VBA)
-
アクセス ADO Null以外のレコードの件数を取得したい
Access(アクセス)
-
-
4
ADOのCursorLocationプロパティ
Visual Basic(VBA)
-
5
Accessのフィールド名に半角括弧を使ってしまった
Visual Basic(VBA)
-
6
エクセルVBAでエラー!
Excel(エクセル)
-
7
参照設定は2.8と6.0 6.1とどちらにチェッ
その他(Microsoft Office)
-
8
ACCESSのSQLで、NULLかNULLでないかの判定
その他(データベース)
-
9
VBAでエクセルシートを更新(リフレッシュ)する方法を教えて下さい。
Excel(エクセル)
-
10
Accessでテーブル名やクエリ名一覧の抜き出し
Access(アクセス)
-
11
ExcelのVBA。public変数の値が消える
Visual Basic(VBA)
-
12
日付型のフィールドに空白を入れる方法を教えてください
その他(データベース)
-
13
DAOでフィールドのデータ型を変更するには?
Visual Basic(VBA)
-
14
Accessデータベースの最適化をExcelVBAでやりたい
その他(データベース)
-
15
初歩的で申し訳ないですが…ADOでExcel検索しています。
Visual Basic(VBA)
-
16
ADOを使ったDBアクセス後のメモリ解放
その他(データベース)
-
17
Excel マクロ VBA プロシージャが大きすぎます のエラー対処方法
Visual Basic(VBA)
-
18
クエリ表示と、ADOで抽出したレコードセットが違う
Access(アクセス)
-
19
EXCEL VBAのユーザーフォームに引数を渡す方法について
Excel(エクセル)
-
20
Access ADOで文字列を日付型へinsert
その他(プログラミング・Web制作)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルVBAが途中で止まります
-
VBA シートをコピーする際に Co...
-
別ブックをダイアログボックス...
-
VBAで別ブックのシートを指定し...
-
ワイルドカード「*」を使うとう...
-
VBS Bookを閉じるコード
-
VBAで複数のブックを開かずに処...
-
エクセルのマクロについて教え...
-
VBA コードを実行すると画面が...
-
ADOで複数のBookから抽出
-
ExcelVBA:すでに開かれている...
-
複数のエクセルブックをひとつ...
-
VBA 実行時エラー 2147024893
-
ExcelのVBAです。フォルダ内の...
-
Excelファイルを開くとき、読み...
-
Excelブックがアクティブになっ...
-
vbaで他のブックに転記したい。...
-
【ExcelVBA】zip圧縮されたCSV...
-
エクセルのマクロについて教え...
-
Excelマクロ 該当する値の行番...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBA シートをコピーする際に Co...
-
エクセルVBAが途中で止まります
-
別ブックをダイアログボックス...
-
【ExcelVBA】インデックスが有...
-
ワイルドカード「*」を使うとう...
-
【ExcelVBA】VBA実行でダイアロ...
-
ExcelのVBAです。フォルダ内の...
-
フォルダ内の全てのファイルに...
-
VBA コードを実行すると画面が...
-
VBA 別ブックからコピペしたい...
-
VBAで別ブックのシートを指定し...
-
VBS Bookを閉じるコード
-
vbaでvbaProjectのパスワード解...
-
【VBA】全シートの計算式を全て...
-
VBA シート名が一致した場合の...
-
【ExcelVBA】zip圧縮されたCSV...
-
複数のエクセルブックをひとつ...
-
VBSでExcelのオープン確認
-
VBAで別のブックにシートをコピ...
-
【Excel VBA】書き込み先ブック...
おすすめ情報