ADOでExcel(*.xls)に接続 指定したセルを更新する

本文(発行内容)

ADOでExcel(*.xls)に接続 指定したセルを更新する

こんにちは、三流プログラマーのＫｅｎ３です。

今回は、
ADOでExcel(*.xls)に接続して、
指定したセルを更新してみました。

いつもの三流的なアプローチなので、実際はアレンジして使ってください。
※一部でも参考になればいいんだけど・・・

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

ADO Excel接続関係のリンク情報
http://www.ken3.org/cgi-bin/group/asp_ado_excel.asp
から

>質問ですが、ＡＳＰからＡＤＯを使ってエクセルに接続し
>その後セルを指定してデータをエクセルにつっこみたくて
>色々調べたんですがさっぱりわかりませんでした。
>セルを指定するにはどうしたらいいんでしょうか〜〜？

と、質問をもらった。

/*
 * ２．不親切な回答
*/

不親切な できるだろう 的 な、手抜きの回答は、

Excel(*.xls)グラフ付雛形にADOで接続しデータ更新
http://www.ken3.org/cgi-bin/test/test057-1.asp
↑ASPからADOを使用してExcel(*.xls)と接続して、.Updateでレコードを更新する。

  '接続文字列作成、test057.xlsを指定する
  strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & Server.MapPath("test057.xls") & ";" & _
      "Extended Properties=Excel 8.0"
とかで接続して、

rs.Open "Select * from [DATA$A1:B9]", db, adOpenStatic, adLockOptimistic

と、指定範囲を開き、

  rs.MoveFirst '先頭へ(A2:B2の行)
  Response.Write "ループで配列からフィールド（セル）へセット<br>"
      'ループ
  For n = 1 To 8  '当日＋一週間の７日で８回まわる
    rs.Fields(0).Value = dHIZUKE(n) 
    rs.Fields(1).Value = PageCNT(n)
    rs.Update    '更新
    rs.MoveNext  '次のレコードへ（次の行へ）
  Next

と、できるので、

Select * from [DATA$A1:B9]
ヘッダー無しの接続指定にして、
こいつを
Select * from [DATA$E5:E5]
とかにして
E5を更新できればいいんだけど、、、

なんて感じの一方通行の想像／妄想の回答かなぁ

/*
 * ３．１つ１つつぶすかな Extended Properties=Excel 8.0;HDR=NO
*/

先輩や上司、はたまた、偉そうなHP/掲示板の常連から案をもらったら、
自分なりに１つ１つつぶしていくのがハヤイと思う。

不親切な三流君からのヒントは、
・ヘッダー無しで接続して、
・Select * from [DATA$E5:E5]、
とか言ってたっけ。ホントにできんのかよ・・・

ADOは、接続の文字列作りがポイントなんだっけ？

ASPからADOを使用してExcel(*.xls)に接続してみた
http://www.ken3.org/cgi-bin/test/test053-1.asp
作った(使った)接続文字列は
Driver={Microsoft Excel Driver (*.xls)};
DBQ=d:\users\ken3_org\Cgi-bin\test\test053.xls;
と、指定して接続はできるのね。
でも、
select * from DATA_RANGE
とか、事前にセル範囲の名前を定義(作ってた)よね。
これだと、使いにくいんだけど。

そこで、
ADOでExcel(*.xls)に接続 [Sheet1$]で列名無しテスト
http://www.ken3.org/cgi-bin/test/test084-1.asp
事前に名前の定義無しで、
select * from [Sheet1$]
とかやったよね。

でもさ、１行目がフィールド名に強制的になってたよね？
えっ、、そうだったっけ？

自分で作った、
ADOでExcel(*.xls)に接続 .GetRowsで全件読み込んでみた
http://www.ken3.org/cgi-bin/test/test085-1.asp
を見てみろよ、
１行目が列名として使用されてただろ？

ヤバイなぁ、
とすると、先頭行をヘッダーで使用しない、
そんなオプションが必要(オプションの有無を探さないとね)

似通った処理のCSV接続で、
ADO Extended Properties='text;HDR=NO' でヘッダー無しのCSVと接続
http://www.ken3.org/cgi-bin/test/test090-1.asp
ヘッダー(１行目)にフィールド名が無いCSVファイルと接続してみたので、
Extended Properties
で、何か(HDR=NO)できそうですね。

ADO xls HDR=NOの３つのキーワードでMSを検索すると、
[SAMPLE] ADO を使用して Excel ブックのデータの読み取り
および書き込みを行う方法 (ExcelADO)
http://support.microsoft.com/default.aspx?scid=kb;ja;278973
がみつかります。
この中で、
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Book1.xls;" & _
           "Extended Properties=""Excel 8.0;"""
oConn.Close
を発見しました。

これを使い、
ADO Excel(*.xls) 接続 Extended Properties=Excel 8.0;HDR=NO
で ヘッダー行を無しにする
http://www.ken3.org/cgi-bin/test/test103-1.asp
テストで、接続文字を
  '接続文字
  Con = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & Server.MapPath("test084.xls") & ";" & _
      "Extended Properties='Excel 8.0;HDR=NO'"
として作成、テストしてみました。
ポイントは、
Extended Properties='Excel 8.0;HDR=NO
です、
※無事に先頭行から(左上のA1セル)をデータとして取り込めました。

/*
 * ４．個別につぶし終わったので、つなげたテストプログラムを組む
*/

接続文字の問題が解決したので、
test103.xls
の
Sheet1,Sheet2,Sheet3 を選択、
A B C D E 列を選択
1〜5行を選択
データを入力
させて、xlsファイルを更新してみたいと思います。

仕事人のサブ関数が受け取りたいのは、
・Excelファイル名
・シート名
・セルのアドレス
・書き込みデータ
の４つです。

関数は、引数４つを受け取って仕事をする、そんな関数を作成します。
Sub TestExcelDATASet(strXLSNAME, strSHEETNAME, strRANGE, strDATA)
    '４つの引数を受け取り
    '指定されたxlsファイルを開き
    '指定したシート、セル範囲にデータを書き込む(更新する)
End Sub

まぁ、上記のように日本語で処理を書いてから、作成するとなれないうちはいいのかも
※通常は、関数仕様書ってヤツを先に書くんだけどね・・・

無駄な確認メッセージが付いてますが、
下記のような感じで、
分岐も無く、直線的に作成してみました。

Sub TestExcelDATASet(strXLSNAME, strSHEETNAME, strRANGE, strDATA)

'---- 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=" & strXLSNAME & ";" & _
      "Extended Properties='Excel 8.0;HDR=NO'"
  Response.Write "作った、使った接続文字列は<br>"
  Response.Write "<CODE>Con = <b>" & strCon & "</b></CODE><hr>"

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

  'SQL文 Sheet$範囲 として設定
  strSQL = "select * from "
  strSQL = strSQL & "[" & strSHEETNAME & "$" & strRANGE & ":" & strRANGE & "]"
  Response.Write "作った、使ったSQL文字列は<br>"
  Response.Write "<CODE>strSQL = <b>" & strSQL & "</b></CODE><hr>"

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

  rs.Open strSQL, db, adOpenStatic, adLockOptimistic 'レコードセットを開く

  Response.Write "開いたレコードセットに対して.MoveFirst<br>"
  rs.MoveFirst '先頭へ ※イラナイかもね

  Response.Write "データ[" & strDATA & "]をセット後.Updateする" 
  rs.Fields(0).Value = strDATA  'データのセット
  rs.Update    '更新(データ確定)

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

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

End Sub

ポイントは、
Extended Properties='Excel 8.0;HDR=NO'
で、ヘッダー無しのExcelファイル形式として、
select * from [シート名$範囲:範囲]
として位置を指定、

OPEN後、下記の位置を移動、データをセット、更新(データ確定)の３命令
rs.MoveFirst '先頭へ ※イラナイかもね
rs.Fields(0).Value = strDATA  'データのセット
rs.Update    '更新(データ確定)

後始末で、
rs.Close  'レコードセット閉じる
db.Close  'DBを閉じる

なんて感じの直線的な流れです。
※xlsファイル無し、シート名見つからないなどエラーを考慮していない手抜きですが

ADOでExcel(*.xls)に接続 指定したセルを更新する
http://www.ken3.org/cgi-bin/test/test103-2.asp
↑でテストできます、試して遊んでみてください。

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

今回は、
ADOでxlsに接続して、１つのセルを更新してみました。
ポイントは、ヘッダー無しの指定の
Extended Properties='Excel 8.0;HDR=NO'
かな。

でも、１つのセルだけを更新するってたぶんそんな処理は無いと思うので、
実際の処理では、一工夫・二工夫必要なのかな。
※この関数を複数回実行するとレスポンスが悪いし、
　連続更新には向かないかな。

何かの参考となれば幸いです。

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

フィードバック

評価・感想

三流君の主なリンク先

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

その他 宣伝広告