こんにちは
VBAインポート問題で日々悩んでいるものです。
CSV形式のデータをODBCのシステムDSNに登録し、それをDAOでSQL要求しデータを抽出する方法がよくわかりません。
(1)データベースの定義記述内容
(2)レコードセットにSQL命令をかける記述
(3)抽出された内容をワークシートに貼る記述
等がよくわかりません。
DAOの場合、レコードセットを定義するのに
set DB=DBEngine.OpenDatabase("******")があったり、なかったりする理由がわかりません。
突然、set rs = CurrentDb.OpenRecordset("*****")
しているのはなぜでしょうか。
No.4ベストアンサー
- 回答日時:
田吾作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
お体大丈夫ですか。
今まで、いろいろとアドバイスいただきほんとうにありがとうございます。
参考にさせていただきます。
また、わからないことがありましたらお助けください。
本件の質問はこれで終了させていただきます。
No.3
- 回答日時:
> Set xlsApp = New Excel.Application
?上記の記述ですと、VBAでなくて、VBからの実行ということになるのでしょうか。
?希望としましては、VBAからの実行をしたいのですが。
VBAで大丈夫ですよ。(^^;)
何らかのモジュールを開いて、ツールの参照設定で
[Microsoft Excel x.0 Object Library]
を指定したらアクセスからエクセルを操作できます。
ちなみにWord/PowerPointを参照設定してたら、それらのソフトを操作することもできますよ。これはVBもVBAも一緒です。
ステップ実行してみてください。
コメントがプログラムに書いてあると思いますが、処理としては
1.CSVファイルのフルパスから、DB名とTABLE名を取得
2.DB(ファイルパスのディレクトリ)/TABLE(CSVファイル)に接続
3.エクセルの起動
4.テーブルのフィールド名を、エクセルに出力
5.各フィールドの持つ値を、エクセルに出力
っていう感じです。
もしかして、hoomaさんの考えている処理は、クエリを作成して、DoCmd(アクセスの持ってる命令)でエクセル形式でエクスポートしたいのですか?
この回答への補足
お世話になってます。
実行したら下記のエラーがでました。
実行時エラー'3061':
ラメータが少なすぎます。1を指定してください。
変更した点は
Const FileFullPath As String = "c:\db4\0110itmz.csv"
です。
なにか、足りないのでしょうか。
>もしかして、hoomaさんの考えている処理は、クエリを作成して、
>DoCmd(アクセスの持ってる命令)でエクセル形式でエクスポートしたいのですか?
今のところ、SQLでできればクエリは必要ないです。
ひとまずありがとうございました。
VBAの参照設定でDAO のバージョンが違ったためエラーになりましたが、正しいものに変えてCSVデータのインポートができるようになりました。
しかし、途中で値がオーバーフローになってしまい完全にインポートできません。99レコード目でいつも止まります。
さらに、速度が遅いんですが、速くはならないのでしょうか。
いろいろお願いしてすみません。
No.2
- 回答日時:
ODBCじゃなきゃダメですか?
DAOで普通にCSVファイルに接続できるので、その方法のサンプルを載せておきます。
※注意点1.普通はRs.Recordcountとするとレコード件数がわかりますが、テキストに接続した場合はRs.Recordcountは常に[1]を返します。
※注意点2.DB名/テーブル名はテキストの場合は扱いが特別です。
たとえば
[c:\test.mdb] ← DB名
[TBL_商品] ← TABLE名
こういった扱いが普通ですが、テキストファイルの場合は
c:\Folder1\dmy\test.csvを分解して
[c:\Folder1\dmy\] ← DB名
[test#csv] ← TABLE名
となります。
注意すべきはテーブル名は、ピリオドが含む場合シャープ記号に変換して使用することです。
注意点3.GetRowsメソッドを使うとレコードセットのポインタが破棄されるため、使用不可能です。
当然ですが、ツールの参照設定で
[Microsoft Excel x.0 Object Library]
[Microsoft DAO x.x Object Library]
を指定してくださいね
Sub Main()
'CSVファイルのフルパス(任意です)
Const FileFullPath As String = "c:\Folder1\dmy\test.csv"
'エクセルに貼り付ける時の開始行(任意です)
Const lngPasteRow As Long = 2
Dim Ws As DAO.Workspace
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim strSQL As String
Dim strDbName As String
Dim strTblName As String
Dim xlsApp As Excel.Application
Dim xlsBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim i As Long
Dim j As Long
Dim cntFld As Long
'DB名とTABLE名を取得する
Call getDbTbl_for_File(FileFullPath, strDbName, strTblName)
'テーブルオープン
Set Ws = DBEngine.Workspaces(0)
Set Db = Ws.OpenDatabase(strDbName, True, False, "Text;")
strSQL = "select * from [" & strTblName & "] where Field1='hoge'"
Set Rs = Db.OpenRecordset(strSQL)
'エクセル起動
Set xlsApp = New Excel.Application
Set xlsBook = xlsApp.Workbooks.Add
Set xlsSheet = xlsBook.Worksheets(1)
xlsApp.Visible = True
'書き込み
With xlsSheet
'フィールド数取得
cntFld = Rs.Fields.Count
'フィールド名の書き込み
For i = 0 To cntFld - 1
.Cells(lngPasteRow, i + 1).Value = Rs.Fields(i).Name
Next i
'各値の書き込み
i = 0
Do Until Rs.EOF
For j = 0 To cntFld - 1
.Cells(lngPasteRow + 1 + i, j + 1).Value = Rs(j).Value
Next j
i = i + 1
Rs.MoveNext
Loop
End With
xlsBook.Saved = True '更新情報を破棄する(閉じるときの「保存しますか?」のダイアログを表示させない)
Set xlsSheet = Nothing
Set xlsBook = Nothing
Set xlsApp = Nothing
Rs.Close
Db.Close
Ws.Close
Set Rs = Nothing
Set Db = Nothing
Set Ws = Nothing
End Sub
'テキストのファイルパスを分解して、データベース名(パス)とテーブル名(ファイル名)に分ける。
Private Sub getDbTbl_for_File(inFilepath As String, outDbName As String, outTblName As String)
Dim wkVal As Variant
wkVal = Split(inFilepath, "\")
outTblName = wkVal(UBound(wkVal))
outDbName = Left(inFilepath, Len(inFilepath) - Len(outTblName))
outTblName = Replace(outTblName, ".", "#")
End Sub
この回答への補足
まだ試してませんが、
細かいことですが、ひとつ確認させていただきます。
'エクセル起動
Set xlsApp = New Excel.Application
上記の記述ですと、VBAでなくて、VBからの実行ということになるのでしょうか。
希望としましては、VBAからの実行をしたいのですが。
No.1
- 回答日時:
とりあえず後半部分だけにお答えします。
前半部分については、他の方にお任せ(^^;
(解答がつかなければ、補足とかで催促してください。)
>DAOの場合、レコードセットを定義するのに
set DB=DBEngine.OpenDatabase("******")があったり、なかったりする理由がわかりません。
いわゆる「オブジェクト指向」ってやつが関係しています。
まず、OpenRecordsetっていうのは、Databaseオブジェクトに対して、レコードセットを作成せよって命令なのです。
顧客DBと売上DBの二つのデータベースがあるとして、
Dim DB_Kokyaku As DAO.Database 'Database型の変数を宣言する
Dim DB_Uriage As DAO.Database
Dim RS_Kokyaku As DAO.Recordset 'Recordset型の変数を宣言
Dim RS_Uriage as DAO.Recordset
Set DB_Kokyaku = DBEngine.OpenDatabase("顧客DB")
Set DB_Uriage = DBEngine.OpenDatabase("売上DB")
Set RS_Kokyaku = DB_Kokyaku.OpenRecordset("顧客テーブル")
Set RS_Uriage = DB_Uriage.OpenRecordset("売上テーブル")
RS_Kokyaku.MoveLast
Msgbox "顧客テーブルの件数は" & RS_Kokyaku.RecordCount & "件です。"
RS_Uriage.MoveLast
Msgbox "売上テーブルの件数は" & RS_Uriage.RecordCount & "件です。"
上記のコードを読めば、Set DB =… の意義がわかっていただけると思います。
でも、一々、Database型の変数を宣言して、Set DB = としてデータベースを開くなんて、メンドクサイですよね。開きたいテーブル/クエリ/SQLは「今使ってるデータベース」の中にあるのに…
そこで CurrentDBメソッドの登場。
CurrentDBはAccessに最初から組み込まれている関数で、
「Access ウィンドウで現在開かれているデータベース (Databaseオブジェクト) のオブジェクト変数を返します。」(ヘルプより)
つまり、CurrentDB.なんたら とするのは
Dim DB As Database
Set DB = OpenDatabase("現在Accessで開いているDBの名前")
DB.なんたら
とするのと同じです。加えて動作も高速ですしね。
ってことで、普段はCurrentDBの方を使っとけばいいんです。
で、現在Accessで開いていないDBを使うときだけ、
Set DB = …
としてやればいいと。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
アクセスマクロで、csv データ...
-
ACCESS テーブルに2行目から取...
-
エクセルからアクセスにインポ...
-
EXCELで帳票フォームをつくりそ...
-
Access2019でフォームから入力...
-
jspのプログラムについて
-
ACCESSのVBAを利用して、Excel...
-
異なるスキーマからデータを抽...
-
統計情報について
-
GROUP BY で列名は指定できない...
-
INDEXの無効化
-
データを削除しても表領域の使...
-
可変個数のデータはどう納める?
-
DELETE文でFROM句を省略した場合
-
Access インポート上限について
-
oracleのimpdpでORA-39166
-
accessでイベントを中止するよ...
-
create table時のINITIALとMAXE...
-
viewのバックアップ
-
同じSELECT文同士でのデ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルからアクセスにインポ...
-
ACCESS テーブルに2行目から取...
-
テーブルのリンク(ACCESS)
-
C#でaccdbファイルのテーブルの...
-
他ファイルのテーブルの情報を...
-
(ACCESS)複数mdbからのデータ...
-
アクセスマクロで、csv データ...
-
Access2019でフォームから入力...
-
グループ単位にCSVファイル...
-
データ読込時のタイムアウト
-
VBでCSVデータをMDB
-
アクセス初心者に注意点を教え...
-
画面から入力した値がDBに登録...
-
リソースファイルとロケールに...
-
ACCESSのVBAを利用して、Excel...
-
Accessでボタンを押したらエク...
-
ACCESSとエクセルのリン...
-
[PG]AccessVBAでファイルのイン...
-
データベースの保存と読み込み...
-
アクセス2007 エクセルへエク...
おすすめ情報