VB2005でDAOを使い、Excelのデータを取得するには？

解決

TAKU 2007-10-25 01:58:35 No: 137920

VB6.0だと、以下の構文でうまくいったんですが、
VB2005だとうまく動作しないようです。
どなたかご教授お願い致します。

Dim DB          As DAO.Database
Dim RS          As DAO.Recordset
Dim xlFileName  As String
Dim xlSheetName As String

xlFileName = Form1.StatusBar1.Panels(12) & Dir(Form1.StatusBar1.Panels(12) & "ファイル名" & "*.xls")
xlSheetName = "シート名" & "$"

Set DB = OpenDatabase(xlFileName, False, False, "Excel 8.0;HDR=NO;IMEX=1")
Set RS = DB.OpenRecordset(xlSheetName)

魔界の仮面弁士 2007-10-25 02:08:33 No: 137921

文法の違いや、オブジェクト解放手順の差異などはありますが、
基本的な処理手順は同じであるはずです。

> VB2005だとうまく動作しないようです。

VB6 ではなく、2005 の方のコードを見せてください。
それが無いと、どこが間違っているのかを指摘できません。
(コードが正しいのに取得できないなら、I-ISAM ドライバの不足など、
「環境側の問題」を考慮せねばなりません)

TAKU 2007-10-25 02:31:54 No: 137922

ご指摘ありがとうございます！
VB2005だとこうなります。。
名前'OpenDatabase'は宣言されていません。というエラーになります。

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim xlFileName As String
Dim xlSheetName As String

xlFileName = DataSheetPath & Dir(DataSheetPath & "NodeB-DataSheet" & "*.xls")
xlSheetName = "統合諸元" & "$"

DB = OpenDatabase(xlFileName, False, False, "Excel 8.0;HDR=NO;IMEX=1")
RS = DB.OpenRecordset(xlSheetName) 'Recordsetオブジェクトのオープン

TAKU 2007-10-25 02:41:25 No: 137923

すみません、こうでした。。

名前'OpenDatabase'は宣言されていません。というエラーになります。

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim xlFileName As String
Dim xlSheetName As String

xlFileName = DataSheetPath & Dir(DataSheetPath & "ファイル名" & "*.xls")
xlSheetName = "シート名" & "$"

DB = OpenDatabase(xlFileName, False, False, "Excel 8.0;HDR=NO;IMEX=1")
RS = DB.OpenRecordset(xlSheetName) 'Recordsetオブジェクトのオープン

魔界の仮面弁士 2007-10-25 03:42:32 No: 137924

> 名前'OpenDatabase'は宣言されていません。というエラーになります。
こういう時は、「OpenDatabase が、どのオブジェクトのメソッドであるか」を
調べると解決しますよ。

DAO のヘルプやオブジェクトブラウザで確認すると、OpenRecordset が
DBEngine および Workspace のメンバである事を確認できるはずです。

DBEngine とは、DAO の最上位にあるオブジェクトなので、まずはそれを
Dim engine As New DAO.DBEngine()
として生成し、このオブジェクトから辿っていけば OK です。

本来であれば、(VB6でも)DBEngine オブジェクトを生成すべきなのですが、
DBEngine はグローバルオブジェクトになっているので、VB6 では
宣言を省略することができていたのです。

魔界の仮面弁士 2007-10-25 03:45:47 No: 137925

ということで、下記に具体例を示しておきます。

Marshal.ReleaseComObject による『オブジェクトの解放処理』も
書いてあるので、多少冗長なコードに見えるかもしれませんけれども。

'---------
Private ds As New DataSet()
Private table As DataTable

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
  'DAO のオブジェクト生成
  Dim engine As New dao.DBEngine()
  Dim db As dao.Database = engine.OpenDatabase("C:\test.xls", False, False, "Excel 8.0;HDR=NO;IMEX=1")
  Dim rs As dao.Recordset = db.OpenRecordset("Sheet1$")

  '画面表示用の DataTable の準備
  table = ds.Tables.Add("Sheet1")
  Dim fs As dao.Fields = rs.Fields
  Dim maxCount As Integer = fs.Count - 1
  Dim f(maxCount) As dao.Field
  For n As Integer = 0 To maxCount
    f(n) = fs(n)
    table.Columns.Add(f(n).Name)
  Next

  ' Recordset から読み込んで、DataTable に格納
  Do Until rs.EOF
    Dim items As New ArrayList()
    For n As Integer = 0 To maxCount
      items.Add(f(n).Value)
    Next
    table.Rows.Add(items.ToArray())
    rs.MoveNext()
  Loop

  '使い終わった DAO オブジェクトの解放
  For n As Integer = 0 To maxCount
    System.Runtime.InteropServices.Marshal.ReleaseComObject(f(n))
  Next
  System.Runtime.InteropServices.Marshal.ReleaseComObject(fs)
  rs.Close()
  System.Runtime.InteropServices.Marshal.ReleaseComObject(rs)
  db.Close()
  System.Runtime.InteropServices.Marshal.ReleaseComObject(db)
  System.Runtime.InteropServices.Marshal.ReleaseComObject(engine)

'画面に表示
DataGridView1.DataSource = table
End Sub

ただし本当は、DAO に頼るよりも、ADO.NET (あるいはせめて ADO)による
データアクセスに移行すべきです。その方が解放処理も簡単ですし。

魔界の仮面弁士 2007-10-25 03:56:34 No: 137926

> ただし本当は、DAO に頼るよりも、ADO.NET (あるいはせめて ADO)による
> データアクセスに移行すべきです。その方が解放処理も簡単ですし。

ということで、ADO 版です。
DataAdapter によるサポートがあるので、DAO よりも扱いやすいかと。

Private ds As New DataSet()
Private table As DataTable

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
  Dim conStr As New OleDb.OleDbConnectionStringBuilder()
  conStr.Provider = "Microsoft.JET.OLEDB.4.0"
  conStr.DataSource = "C:\test.xls"
  conStr("Extended Properties") = "Excel 8.0;HDR=NO;IMEX=1"

  'ADO のオブジェクト生成
  Dim rs As New ADODB.RecordsetClass()
  rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
  rs.Open("Sheet1$", conStr.ConnectionString, , , ADODB.CommandTypeEnum.adCmdTableDirect)

  '画面表示用の DataTable に読込
  Using da As New OleDb.OleDbDataAdapter()
    da.Fill(ds, rs, "Sheet1")
  End Using
  table = ds.Tables("Sheet1")

  '使い終わった ADO オブジェクトの解放
  If System.Runtime.InteropServices.Marshal.IsComObject(rs) Then
    System.Runtime.InteropServices.Marshal.ReleaseComObject(rs)
  End If

'画面に表示
DataGridView1.DataSource = table
End Sub

魔界の仮面弁士 2007-10-25 03:58:25 No: 137927

最後に、ADO.NET 版。
COM オブジェクトの解放処理が不要になるので、これが一番楽かと。

Private ds As New DataSet()
Private table As DataTable

  Dim sql As String = "SELECT * FROM [Sheet1$]"
  Using da As New OleDb.OleDbDataAdapter(sql, conStr.ConnectionString)
    da.Fill(ds, "Sheet1")
  End Using
  table = ds.Tables("Sheet1")

DataGridView1.DataSource = table
End Sub

TAKU 2007-10-25 19:15:32 No: 137928

>魔界の仮面弁士さん
ご説明ありがとうございました。
大変勉強になりました。
つきましては、言われるようにADO.NET 版へ移行しようかと
考えていますが、その場合エクセルのセル毎に値を取得
することは可能でしょうか？
因みにDAOでもうまく取得することができませんでした。
DAOで作成した構文は以下の通りです。

'DAO のオブジェクト生成
Dim engine As New DAO.DBEngine()
Dim db As DAO.Database = engine.OpenDatabase(xlFileName, False, False, "Excel 8.0;HDR=NO;IMEX=1")
Dim rs As DAO.Recordset = db.OpenRecordset(xlSheetName)

Do Until rs.EOF

With rs

変数=.Fields(0)

.MoveNext() '次のレコードに移動

End With

Loop

db.Close()

rs = Nothing
db = Nothing

魔界の仮面弁士 2007-10-25 20:49:42 No: 137929

> 考えていますが、その場合エクセルのセル毎に値を取得
> することは可能でしょうか？

指定する SQL コマンドに、
SELECT * FROM [Sheet1$B1:C3]
のような構文を使えば、「Sheet1 上の B1〜C3 セル範囲」になりますよ。

> 変数=.Fields(0)
これはマズイです。

DAO の「.Fields」は COM オブジェクトなので、「.Fields(0)」を
使う前に、.Fields を変数に受け、その変数から (0) を得るようにし、
最後に、その変数を「Marshal.ReleaseComObject」で解放せねばなりません。
(ADO.NET の場合は COM オブジェクトではないので、そうした手間は不要です)

http://support.microsoft.com/kb/317109/ja
http://support.microsoft.com/kb/321415/ja

先の私のサンプルでは、rs.Fields(0).Value の構文を使わず、わざわざ
rs.Fields を「dao.Fields 型の変数」fs に入れておき、そこからさらに、
fs(0) で得たオブジェクトを「dao.Field 型の変数」配列 f に受け、
最後に、それらのオブジェクトを「Marshal.ReleaseComObject」していますよね。

TAKU 2007-10-25 23:47:52 No: 137930

回答ありがとうございます。
DAOはかなり手間があるようなので、ADO.NET でやろうと思います。

ご教授頂いた構文で、DataGridViewにデータを反映するとこまでは
確認できたんですが、この値を変数に入れて、ピンポイントで
その値を参照することは可能でしょうか？

例えば、SELECT * FROM [Sheet1$B1:CC3000]だとその範囲全ての値ですが、
K列とAA列のみがほしいという場合。そして、そのK列の値を変数例えば、
変数(3000)とういう配列？に入れ、そこから参照するということは可能
でしょうか？

質問ばかりで申し訳ありませんが、宜しくお願い致します。

魔界の仮面弁士 2007-10-26 00:24:45 No: 137931

> K列とAA列のみがほしいという場合。
あとから、table.Columns.Remove するとか、
K 列だけ / AA 列だけの 2回にわけて取得するとか。

> そのK列の値を変数例えば、変数(3000)とういう配列？に入れ、そこから参照
ごめんなさい。質問の意味がわかりません。

TAKU 2007-10-26 00:58:54 No: 137932

質問の書き方が悪かったですね。すみません。。

> そのK列の値を変数例えば、変数(3000)とういう配列？に入れ、そこから参照
については、DataGridViewより参照する方法で回避できました。

最後に一つだけ質問させて下さい。

>K 列だけ / AA 列だけの 2回にわけて取得するとか。

ですが、2回に分けて取得し、DataGridViewには一回で反映する、ということでしょうか？
でしたら、2回に分けて取得する方法をご教授いただけますでしょうか？

魔界の仮面弁士 2007-10-26 01:11:56 No: 137933

> については、DataGridViewより参照する方法で回避できました。
DataGridView というか…。

DataTable に保存しているのですから、
value = table.Rows(行番号)(列番号)
で読み書きできますよ。

> でしたら、2回に分けて取得する方法をご教授いただけますでしょうか？
別々の DataTable に Fill すれば OK です。

一度、Excel から VB 側に取り込んでしまえば、
後はどうとでもマージできますよね。

TAKU 2007-10-26 01:38:47 No: 137934

なんとか求めているものができました！
色々お教え頂いて有難う御座いました。