VBでEXCELデータの操作について
解決はじめまして。いろいろと参考にさせていただいています。
VCからVBに転向し数ヶ月、まさかVB・EXCELとの組み合わせでちょっとしたものを
作っています。以前まではEXCELとアプリケーションを絡み合わすなど考えてもおらず、データ取得といったらORACLEばかり頭に浮かべていました^^;
正直、EXCELを使うなんぞ邪道という考えを持っていたバカ者ですが、
いざ、直面すると意外に難しく感じ、またちょっとした検証ツール等では使えるなと思い始めた今日この頃です。
さて前置きが長くなってしまったのですが、色々と過去ログや別サイトで情報収集していたのですが、
VBとEXCEL間での接続方法(読込み方法等)がけっこう人によって違いますよね。
まだ、VB・EXCELに精通しているわけではないので深いところまで理解していないというのが現状ですが、
DAOによるアクセス( AS DAO.Database等 )と普通(??)に AS OBjectで定義する
方法と、AS Excel.Application で定義する方法と・・・まだ他にもあるかと思いますが
これらの使い分け方というのは何かあるのでしょうか。
例えば、今私は顧客に提案するデモ用のある機能部分のみを簡単に作るため
VB・EXCELでやってみようと思っています。
データ量も作成している機能では、さほど多くはないし、顧客側の端末にもEXCELだけは入っているので、けるかなとは思っているのですが、
色々と使い分けが必要なのかどうか(DAOやその他のアクセス法)アドバイスお願いします。
VBを長くやられている方にとっては初歩的なこと聞くな！と怒られてしまうかと思いますが宜しくお願い致します。
納品前提で、Sheet操作やExcelのメソッド類を利用したいなら、
バージョンの影響を受けにくい
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
がいいでしょう。
開発マシンと納品マシンのExcelのバージョンが同一で、
開発時にインテリセンスを利用したいなら
Microsoft Excel Object Libraryを参照設定して
Dim objExcel As New Excel.Application
または
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
DAO/ADOは
xlsファイルをDBとして使いたいとき、
Excelのインストールされていないマシンで
扱いたいときに用います。
ただし、開発時にExcel用DBライブラリを追加インストールしないと
「ISAMライブラリがない」とおこられます。
コードはこんな感じ。
Dim daoDB As DAO.Database
Dim daoRs As DAO.Recordset
Dim strXLSName As String
Dim strSQL As String
strXLSName = App.Path & "\DBTest.xls"
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strXLSName, _
False, False, "EXCEL 8.0; HDR=NO;")
strSQL = " Select * From [Sheet1$] "
Set daoRs = daoDB.OpenRecordset(strSQL,dbOpenDynaset)
Sayさん、ご教授ありがとうございます。
大変参考になりました。
実際の納品システムでのＤＢはOracleですので、現状のEXCELをＤＢ扱いとしたものは納品いたしません。
まだ顧客との正式契約に至っていなく(提案の段階)、ましてや顧客先にはORACLEが入っていない為、
少ないデータ量でデモを行なうにはEXCELで行なったほうが良いかと考えついたのです。(更にグラフ表示等の付加機能も提案できる可能性もあるため)
Sayさんからのサンプルを実行してみました。
普通にＳＱＬを打ち込むことも出来たんですね、勉強になりました。
ここのＳＱＬにはＤＢがEXCELだからという特別な制約はあるのでしょうか？
ＳＱＬが使えるなら楽ですね。
Sheet内に空行や空列があるとうまく動作しないことがあります。
特に1行目は空セルがあるとまずいようです。
HDR=NOの場合、フィールドは[F1],{F2]などで指定しますが、
HDR=YESにして1行目をフィールド名として使用したほうがいいでしょう。
余談ですが、csvファイルを直接テキストDBとして参照することもできます。
ファイル名がTest.csvならこんな感じ。
Set daoDB = DBEngine.Workspaces(0).OpenDatabase( _
App.Path, False, False, "Text;DATABASE=" & App.Path & "\;HDR=NO")
strSQL = "Select * from [Test#csv] "
Sayさん、ありがとうございます。
ひとつ聞き忘れたことがありました。
>strSQL = " Select * From [Sheet1$] "
これで条件指定や指定項目のみの抽出も可能でしょうか。
試しにEXCEL内の全列に名前をつけて、その列名の抽出や条件指定をして
みたのですが、以下のパターン時でそれぞれのエラーが表示されます。
パターン1："SELECT 列名称A FROM [Sheet1$] "
→データ取得時(strData = RS.Fields(5))に
「このコレクションには項目がありません」とエラー表示されます。
パターン2："SELECT * FROM [Sheet1$] WHERE 列名称C = " + strCode
→エラー内容はパターン１と同様でしたので、パターン1が解決すれば
解決になるのかなと思っています。
※列名称Cは文字列型(数値)に設定。strCodeも文字列変換した数値です。
たびたびで申し訳ありませんが、ご教授お願いいたします。
どうコーディングされているか知りませんが、
(1)先頭行が項目行の例
Sheetデータ
A B C
--------------
ID NAME DATA
1 AAA 111
2 AAB 112
3 ABB 122
4 BBB 222
5 ABA 121
6 BAB 212
--------------
コード例
Dim daoDB As DAO.Database
Dim daoRs As DAO.Recordset
Dim strXLSName As String
Dim strSQL As String
strXLSName = App.Path & "\DBTest.xls"
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strXLSName, _
False, False, "EXCEL 8.0; HDR=YES;")
strSQL = "Select ID,NAME,DATA FROM [Sheet1$] " & _
"WHERE NAME LIKE 'A*' "
Set daoRs = daoDB.OpenRecordset(strSQL, dbOpenDynaset)
Do Until daoRs.EOF
List1.AddItem daoRs("ID").Value & vbTab & _
daoRs("NAME").Value & vbTab & daoRs("DATA").Value
daoRs.MoveNext
Loop
(2)先頭行が項目行でない例
Sheetデータ
A B C
--------------
1 AAA 111
2 AAB 112
3 ABB 122
4 BBB 222
5 ABA 121
6 BAB 212
--------------
コード例
Dim daoDB As DAO.Database
Dim daoRs As DAO.Recordset
Dim strXLSName As String
Dim strSQL As String
strXLSName = App.Path & "\DBTest.xls"
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strXLSName, _
False, False, "EXCEL 8.0; HDR=NO;")
strSQL = "Select [F1],[F2],[F3] FROM [Sheet2$] " & _
"WHERE [F2] LIKE 'A*' "
Set daoRs = daoDB.OpenRecordset(strSQL, dbOpenDynaset)
Do Until daoRs.EOF
List1.AddItem daoRs("[F1]").Value & vbTab & _
daoRs("[F2]").Value & vbTab & daoRs("[F3]").Value
daoRs.MoveNext
Loop
この件に関し理解することができたと思っています。
Sayさんありがとうございました。
またよろしくお願いいたします。
| ツイート |  |