Excel(*.xls)とADOで接続、.Addnewしてみた

本文(発行内容)

＜Excel(*.xls)とADOで接続、.Addnewしてみた＞
こんにちは、三流プログラマーのＫｅｎ３です。

今回は、
Excel(*.xls)とADOで接続後、
レコードセット.Addnewでレコード追加、
rs.Fields("名前").Value = 値
rs.Fields(フィールド番号).Value = 値
とデータをセットして、
.Updateで更新
と
普通に操作してみます。 

拾い読みして、何かの参考となれば幸いです。

/*
 * １．今回のキッカケ
*/

No.53 ADOでExcelファイル(*.xls)とやっと接続できた（ほっ）
http://www.ken3.org/backno/backno_asp11.html#53
で、
サーバーに置いた、*.xlsファイルに接続できたので、
読み込みが出来たら、書き込みでしょ、、
って流れで、
.AddNewでデータを追加してみたいと思います。

/*
 * ２．普通にAddNewで出来ないの？
*/

さてと、下記のような感じで、Excelファイルにアクセスできました。
http://www.ken3.org/cgi-bin/test/test053-1.asp
参照。

  'ADO DB Recordset オブジェクトを作成する、英文そのままじゃん
  Set rs=Server.CreateObject("ADODB.Recordset")

  strSQL = "select * from DATA_RANGE"
  Con = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & _
        Server.MapPath("test053.xls") & ";"

  Response.Write "作った、使った接続文字列は<br>" & Con & "<hr>"

  '接続文字列、SQLを渡して、レコードセットを開く
  Rs.Open strSQL, Con, 0  '0=adOpenForwardOnly

  strOUT = ""
  Do While Rs.EOF = False
      For Each FieA In Rs.Fields
          strOUT = strOUT & FieA.Name & " -- " & FieA.Value & "<BR>"
      Next
      Rs.MoveNext  '次のレコードへ移動
  Loop
  Rs.Close
  Set Rs = Nothing

パターン的には、
  '接続文字列、SQLを渡して、レコードセットを開く
  Rs.Open strSQL, Con, 0  '0=adOpenForwardOnly
の
0=adOpenForwardOnlyを追加可能なカーソルにすればＯＫかなぁ。
カーソル関係の定数を見ると
（ローカル内のadovbs.incを参照しました）

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

となっているから、adOpenDynamic = 2にして、

  '接続文字列、SQLを渡して、レコードセットを開く
  Rs.Open strSQL, Con, 2

  .AddNew  'レコードセットの追加

としたけど、、、あれ、
ADODB.Recordset エラー '800a0cb3' 

オブジェクトまたはプロバイダは要求された操作を実行できません。 

/cgi-bin/test/test056-1.asp, 行 31 

あっ、ロックのタイプ、書いてないや

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

は、adLockOptimistic = 3を使用すればＯＫだろうと軽く考えて、

  '接続文字列、SQLを渡して、レコードセットを開く
  Rs.Open strSQL, Con, 2, 3

Microsoft OLE DB Provider for ODBC Drivers エラー '80004005' 

[Microsoft][ODBC Excel Driver] 更新可能なクエリであることが必要です。 

/cgi-bin/test/test056-1.asp, 行 36 

えっ、なんで、、、更新可能じゃないの？？？

う〜ん。

/*
 * ３．接続できたら、あとは簡単と豪語してたのに（笑）
*/
接続できたら、あとは追加・更新・削除は簡単と豪語してたのに（笑）
やばいなぁ。。。
得意の人様のＨＰを観察して、パクるか。
AccessからADOでExcelを更新するページ見ても、できてるよなぁ普通に使って。。。

こりゃ、ハマったかな（爆）

で、マイクロソフトのサポートページを見ていて、
英語のページに紛れ込んだ。

http://support.microsoft.com/default.aspx?scid=kb;EN-US;314763

FIX: ADO Inserts Data into Wrong Columns in Excel
The information in this article applies to:

ActiveX Data Objects (ADO) 2.1
ActiveX Data Objects (ADO) 2.5
ActiveX Data Objects (ADO) 2.6
Microsoft OLE DB Provider for Jet 4.0

英語、読めないんだけど(情けないことに中学生以下じゃないたぶん（笑）)
おっ、と思うソースが目に飛び込んできた。

Private Sub Command1_Click()
   Dim strCn As String
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim fld As ADODB.Field

   'Open connection
   strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & App.Path & "\Test.xls;" & _
      "Extended Properties=Excel 8.0"
   Set cn = New ADODB.Connection
   cn.Open strCn
    
   'Add new values.
   Set rs = New ADODB.Recordset
   With rs
      .CursorLocation = adUseClient
      .Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
      .AddNew
      .Fields("ColumnA").Value = 3
      .Fields("ColumnB").Value = 3
      .Fields("ColumnC").Value = ""
      .Fields("ColumnD").Value = 3
      .Fields("ColumnE").Value = 3
      .Fields("ColumnF").Value = "testing"
      .Update
      .Close
   End With
   Set rs = Nothing
   cn.Close
   Set cn = Nothing
End Sub

英語、読めなくても、ソースって意外と読めるもんなんですよね。
※このサンプルソース、英語のコメントが無いのが良かったのかも

これって、見るからに、Excelファイルに追加してる、そんな感じするよね（笑）

自分のソースとの違いを見る。
まず、接続の作り方が、いきなりレコードセットじゃなく、
   'Open connection
   strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & App.Path & "\Test.xls;" & _
      "Extended Properties=Excel 8.0"
とADODB.Connectionを開いてから、
   Set rs = New ADODB.Recordset
   With rs
      .CursorLocation = adUseClient
      .Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
      .AddNew
と、レコードセットを開いて、.Addnewしてる。

.CursorLocation = adUseClient
ってなんだ？
定数のファイル（ローカル内のadovbs.incを参照しました）
を見てみると、
'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3
となっていて、
adUseClientの上に、adUseServer？
英語わからない、私でも、これって？サーバーの指定？かもと予想。

これを参考にして、
訪問者の時刻、ＩＰアドレス、端末情報を保存するサンプルを作ってみました。

Excelファイルのフォーマットと名前の定義は、

↑みたいに作成しました。

<%@LANGUAGE=VBScript%>
<html>
<head>
<title>Excel(*.xls)とADOで接続、.Addnewしてみた</title>
</head>
<body>
<h2>Excel(*.xls)とADOで接続、.Addnewしてみた</h2>
<!-- Ken3 --><!--  #include file="info.inc" -->
test056-1.asp<br>
ASPからADOを使用してExcel(*.xls)と接続して、<br>
.AddNewでレコードを追加する。<br>

<hr>
<%
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

  'ADO DB Connection オブジェクトを作成する、英文そのままじゃん
  Set db=Server.CreateObject("ADODB.Connection")

  '接続文字列
  strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & Server.MapPath("test056.xls") & ";" & _
      "Extended Properties=Excel 8.0"

  Response.Write "接続文字列は<br><b>"
  Response.Write strCon & "</b><br>です<br>"

  'データベース(xls)を開く
  db.open strCon

  Response.Write "その次はレコードセット<br>"

  'ADO DB Recordset オブジェクトを作成する、英文そのままじゃん
  Set rs=Server.CreateObject("ADODB.Recordset")

  rs.CursorLocation = adUseServer  'カーソルはサーバーの指定

  Response.Write "Select * from FIELD_RANGE, db, 〜<br>"
  rs.Open "Select * from FIELD_RANGE", db, adOpenStatic, adLockOptimistic

  Response.Write "開いたレコードセットに対して.AddNew<br>"
  rs.AddNew
  Response.Write "フィールドにアクセスする<br>"
      '↓フィールド名でアクセス
  rs.Fields("F_HIZUKE").Value = Now() 
  rs.Fields("F_IP").Value = Left(Request.ServerVariables("REMOTE_ADDR"), 9)
      '↓番号でアクセスも出来るよ
  rs.Fields(2).Value = Request.ServerVariables("HTTP_REFERER")
  rs.Fields(3).Value = Request.ServerVariables("HTTP_USER_AGENT")

  Response.Write ".Updateで更新（レコード確定）<br>"
  rs.Update

  Response.Write "rs.Close でレコードセットを閉じる<br>"
  rs.Close
  Set rs = Nothing  'オブジェクト変数を開放する

  Response.Write "db.Close でxlsとの接続を閉じる<br>"
  db.Close
  Set db = Nothing  'オブジェクト変数も開放する

%>
<hr>
時刻、IPアドレス（頭から９文字）ユーザーのブラウザ情報を書き込みました<br>
[<a Href="test056.xls" TARGET="_blank">書き込んだtest056.xlsを開く</a>]<br>
↑書き込まれたデータの確認<br>
</body>
</html>

テストは、
http://www.ken3.org/cgi-bin/test/test056-1.asp
で、可能です。
アクセスすると、ログ情報をtest056.xlsに書き込みます。
アクセス後、Excelファイルをダウンロードして、
データが追加されていることを確認してみてください。

/*
 * ４．終わりの挨拶　＜／ＨＴＭＬ＞
*/

今回は、
簡単に.Addnewの解説、、と行きたかったけど、
ハマってしまった、、なんて情けないお話でした（三流プログラマーらしいよね）
and
英語読めないのに、ソースは読める不思議な自分に気がついて、変な気分。
※で、調子に乗ってマイクロソフトの英語サイト読んだけど、よくわかんなかった（笑）
私の失敗談が、
ASPからADOを使用して、Excelファイルに書き込む
そんな処理の参考となれば幸いです。

テストは、
http://www.ken3.org/cgi-bin/test/test056-1.asp
で、可能です。

素朴な疑問やリクエスト、今回みたいなクレームなどがあったら、
メール : qaqaqa@ken3.org
掲示板 : http://www.ken3.org/cgi-bin/bbs/asp/wforum.cgi
に気軽に書き込んでください。

ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。

フィードバック

評価・感想

三流君の主なリンク先

ＡＳＰで遊ぶ、失敗する	掲示板	バックナンバー	登録と解除
三流プログラマー 業務の愚痴	掲示板	バックナンバー	登録と解除
ＶＢＡで楽しくプログラミング	掲示板	バックナンバー	登録と解除
コンビニのオモテとウラ話	掲示板	バックナンバー	登録と解除
www.ken3.org(サイト内)から を利用して、 を

その他 宣伝広告