[三流君] −−> [ASPで遊ぶ、失敗する] −−> [バックナンバー一覧]
−−> No.056 Excel(*.xls)とADOで接続、.Addnewしてみた

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



本文(発行内容)


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

こんにちは、三流プログラマーのKen3です。 今回は、 Excel(*.xls)とADOで接続後、 レコードセット.Addnewでレコード追加、 rs.Fields("名前").Value = 値 rs.Fields(フィールド番号).Value = 値 とデータをセットして、 .Updateで更新 と 普通に操作してみます。 拾い読みして、何かの参考となれば幸いです。

/* * 1.今回のキッカケ */

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

/* * 2.普通に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を追加可能なカーソルにすればOKかなぁ。 カーソル関係の定数を見ると (ローカル内の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を使用すればOKだろうと軽く考えて、 '接続文字列、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 えっ、なんで、、、更新可能じゃないの??? う〜ん。

/* * 3.接続できたら、あとは簡単と豪語してたのに(笑) */

接続できたら、あとは追加・更新・削除は簡単と豪語してたのに(笑) やばいなぁ。。。 得意の人様のHPを観察して、パクるか。 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? 英語わからない、私でも、これって?サーバーの指定?かもと予想。 これを参考にして、 訪問者の時刻、IPアドレス、端末情報を保存するサンプルを作ってみました。 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アドレス(頭から9文字)ユーザーのブラウザ情報を書き込みました<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ファイルをダウンロードして、 データが追加されていることを確認してみてください。

/* * 4.終わりの挨拶 </HTML> */

今回は、 簡単に.Addnewの解説、、と行きたかったけど、 ハマってしまった、、なんて情けないお話でした(三流プログラマーらしいよね) and 英語読めないのに、ソースは読める不思議な自分に気がついて、変な気分。 ※で、調子に乗ってマイクロソフトの英語サイト読んだけど、よくわかんなかった(笑) 私の失敗談が、 ASPからADOを使用して、Excelファイルに書き込む そんな処理の参考となれば幸いです。 テストは、 http://www.ken3.org/cgi-bin/test/test056-1.asp で、可能です。 ASP、VBScript勉強中の三流プログラマーのKen3でした。


ページフッター

ここまで、読んでいただきどうもです。目的の情報が見つかったか?少々心配しつつ、、、

三流君へ メッセージを送る

感想や質問・要望・苦情など 三流君へメッセージを送る。
返信例 XXXXさんへ
下記のフォームからメッセージを送ることができます。


あなたのお名前(ニックネーム):さん
返信は?:

アドレス:に返事をもらいたい
感想や質問↓:


(感想や質問・要望・苦情はHPで記事に載せることがあります。)

種類別のリンク や 広告など

[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


Blogとリンク:[三流君のMemo別館]/ [ASP 三流君のソースコード置き場]/ [Ken3Video YouTubeで動画解説]
広告:

気になった ジャンル ↓を選択してください。

まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。

次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません

広告:



DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。1行読み込みなどを軽く

VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。



[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


広告: