ADO Excel(*.xls)接続 [Sheet1$]でシート指定、列名無しで遊ぶ

本文(発行内容)

＜ADO Excel(*.xls)接続 [Sheet1$]でシート指定、列名無しで遊ぶ＞
こんにちは、三流プログラマーのＫｅｎ３です。

今回は、ほったらかしの
ADO Excel(*.xls)接続を少し。
^^^^^^^^^^^^^^^^^^^^
あいかわらず、バラバラの解説だよね、ADO *.csvの行削除は？いつ？
ギク・・・

/*
 * １．今回のキッカケ
*/
掲示板で下記の質問をもらいました
------
>＜ADOでExcelファイル(*.xls)とやっと接続できた（ほっ）＞のページ、とって
>も役に立ちました！
>サーバーにエクセルが入ってないのに、一生懸命
>Set xlApp = Server.CreateObject("Excel.Application")
>でつなごうとしてました(^^*) 
>
>で、すみません、初歩的なことを教えてください・・・
>Set xlApp = Server.CreateObject("Excel.Application")では、セルの値を参照
>する時に、
><% Response.Write xlSht.Range("b3").Value %>
>でよかったのですが、このように、ＡＤＯを使う場合はどういう指定になるんで
>しょうか。。
>
>ＡＣＣＥＳＳのＤＢなら、
><%= rs.Fields("test").Value %>ですよね。
------
と、
b3のセルへのアクセス方法について、質問をもらいました。

>で、すみません、初歩的なことを教えてください・・・
なんて書かれているけど、、、う〜ん、私も初歩的なことわからないから、
メルマガのネタにして、やってみますか。
※最近、実力以上の質問が来て・・困っていたりする。
　掲示板だとみなさんの目に止まるが、メールで直の難しい質問も多数来てたり・・
  カッコつけられるように頑張らないとね。。。


/*
 * ２．Excel(*.xls)との接続復習
*/

b3セルの値を取りたいかぁ。

http://www.ken3.org/cgi-bin/group/asp_ado_excel.asp
に
ADO Excel接続関係の情報をまとめてます。
と
宣伝を入れつつ、
.xlsにアクセスしているサンプルを復習すると、
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

名前の範囲を使用して、アクセスしてみる方法は、
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No.53 ADOでExcelファイル(*.xls)とやっと接続できた（ほっ）
http://www.ken3.org/backno/backno_asp11.html#53
が参考元の解説で、

http://www.ken3.org/cgi-bin/test/test053-1.asp
で、テストできます。
ここでは、ブックtest053.xlsでシートに付けた名前の範囲、DATA_RANGEに対して、


↑表のイメージ、先頭行が列名となっている

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

  strSQL = "select * from DATA_RANGE"

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

Select文で、Excelで付けた名前の範囲DATA_RANGEをテーブルと見立てて、
select * from DATA_RANGEと書けることを説明しました。

データへのアクセスは、
Do While Rs.EOF = False
     For Each FieA In Rs.Fields
         strOUT = strOUT & FieA.Name & " -- " & FieA.Value & "<BR>"
     Next
     Rs.MoveNext  '次のレコードへ移動
Loop
と、
先頭行がフィールドして扱われていることがわかると思います。

シート名とセル範囲を指定したアクセス方法
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
名前範囲を事前に付けておく、そんなことやってらんないよ！！
なんて人達も多いと思います。
そんな人達にお薦めなのが、

Excel(*.xls)グラフ付雛形にADOで接続しデータ更新
http://www.ken3.org/cgi-bin/test/test057-1.asp
では、

↑シート名とイメージ

Select * from [DATA$A1:B9]
として、
シート名DATA A1:B9とセル範囲を自分で指定しました。

rs.Open "Select * from [DATA$A1:B9]", db, adOpenStatic, adLockOptimistic
で、レコードセットを開いて、
  For n = 1 To 8  '当日＋一週間の７日で８回まわる
    rs.Fields(0).Value = dHIZUKE(n) 
    rs.Fields(1).Value = PageCNT(n)
    rs.Update    '更新
    rs.MoveNext  '次のレコードへ（次の行へ）
  Next
と
ここでは、rs.Fields(0).Value,rs.Fields(1).Value
(0),(1)みたいにｎ番目のフィールドとフィールドの番号を指定して
アクセスしてました。

/*
 * ３．列名がもし無かったら？ And 遊びでsheetname$でアクセスする
*/

基本パターンは、
^^^^^^^^^^^^^^
ADO Excel接続の書き方は、テーブル指定の方法が３つあって、

ワークシート         sheetname$ 
名前付き範囲         rangename 
名前付きではない範囲 sheetname$rangeaddress 

です。

http://www.microsoft.com/japan/msdn/columns/office/office10052000.asp
がＭＳのページです。※よかったこれは日本語だよ（オイオイ）

さてと、b3へのアクセスかぁ・・・
名前付き範囲 rangename は、"select * from DATA_RANGE"とやったし、
名前付きではない範囲 sheetname$rangeaddress 
は、"Select * from [DATA$A1:B9]"でやったので、
Select * from [シート名$B3]
も考えたけど（列名無しで、単独指定できるのか興味ありの疑問だけど）
せっかくなので、
アト一つ残った、ワークシート sheetname$ でシートを指定を試してみたいと思います

度が過ぎた、遊びで、列名も付けない表を作成してみました。

   A列        B列         C列              D列
1: 列の項目名がなかったら？                        
2:                        c2どうなるの？  
3:            b3のセルはここ          
4: a4のせるはここ                  
5:                                         d5に１つ端っこ
6: 名前       三流君          
7: 住所       東京都の田舎町          
8: 好きな球団 横浜ベイスターズ                
9:
10: 名前範囲も付けてません                 END?
※シート名はデフォルトのよくみかける[Sheet1]に作りました、
　ブック名はtest084.xlsで、aspと同じディレクトリにFTPでUPしてあります


↑が作成した表のイメージです。

なんだ、このシートは？テストデータぐらいまともにつくれよ！！
まぁまぁ、まともじゃない表に対してのアクセステストになるんじゃない？

こんな表、オレは認めないよ。勝手にやってよ。
ハイハイ。

では、ワークシート sheetname$で単純にアクセスしてみます。

  'SQL文 Sheet1$として、シートのみ指定してみます
  strSQL = "select * from Sheet1$"
  '接続文字列、SQLを渡して、レコードセットを開く
  Rs.Open strSQL, Con, 0  '0=adOpenForwardOnly
とすると・・あらら、ダメでした。

Microsoft OLE DB Provider for ODBC Drivers エラー '80040e14' 
[Microsoft][ODBC Excel Driver] FROM 句の構文エラーです。 
/cgi-bin/test/test084-1.asp, 行 36 

それなら、select * from [Sheet1$]と[]で囲うのが正解でした。
気を取り直して、

strSQL = "select * from [Sheet1$]"
Rs.Open strSQL, Con, 0で、レコードセットを作成しました

作成したレコードセットのフィールド名を下記のプログラムで表示してみると、

  '項目名の表示（シートしか指定してないのに？）
  Response.Write "<TR>"
  For Each FldA In Rs.Fields 'フィールド分ループする
      Response.Write "<TH>" & FldA.Name & "</TH>" '.Nameでフィールド名を表示
  Next
  Response.Write "</TR>"

列の項目名がなかったら？ F2 F3 F4 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
とＡ１の文字と空白はF2,F3,F4と勝手に作成された。
どうやら１行目をフィールド名として強引にあてはめるらしい。

シートしか指定していないので、レコードの終わり判断できるか？
少し不安だったけど、
  'レコードが終了するまでループ（シートしか指定してないけど大丈夫？）
  Do While Rs.EOF = False
      Response.Write "<TR>"
      For Each FldA In Rs.Fields 'フィールド分ループする
          Response.Write "<TD>" & FldA.Value & "</TD>"  '.Valueで値を表示
      Next
      Response.Write "</TR>"
      Rs.MoveNext  '次のレコードへ移動
  Loop
で、
無事、データが表示されました。

http://www.ken3.org/cgi-bin/test/test084-1.asp
で、テスト結果と全てのソースを見ることが出来ます。
なんか不思議な感じがするけど、なんとか表示できました。

あっ、質問はb3へのアクセスだった。

列名無しで接続できるが、先頭行が強引にフィールド名となってしまう。
B3のセルに対して、アクセスするには、
先頭行(A1やB1,C1,D1)がフィールド名となっているので、
Open時のカーソルは、A2,B2,C2と２行目からのスタートなので、
3行目に行くために、.MoveNextで１行空読みして、
Rs.Fields(1)でＯＫかなぁ。

   Response.Write "Rs.Open strSQL, Con, 0<br>"
   Rs.Open strSQL, Con, 0  '0=adOpenForwardOnly

   Response.Write "Rs.MoveNext<br>"
   Rs.MoveNext  '１行飛ばす
   Response.Write "Rs.Fields(1)は<big>[" & Rs.Fields(1) & "]</big>です<br>"

   Response.Write "Rs.MoveNext<br>"
   Rs.MoveNext  '１行飛ばす
   Response.Write "現在のRs.Fields(0)は<big>[" & Rs.Fields(0) & "]</big>です"

なんて感じで、下のほうでテストしてみました。

ADOでExcel(*.xls)に接続 [Sheet1$]で列名無しテスト
http://www.ken3.org/cgi-bin/test/test084-1.asp
で、確認してみてください。

なんかなぁ・・・イマイチ（笑）

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

今回は、
ASPでADOを使用して*.xlsと接続、
[Sheet1$]とシート名だけで接続したときのフィールド名について少し遊んでみました。
なんか、サクっとxxxx!B3みたいに出来なかったけど、
参考になったかなぁ・・・・

いろいろと問題点を残しつつ、脱線・寄り道の多いメルマガですが、
今後とも、よろしくお願いします。

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

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

ページフッター

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

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

感想や質問・要望・苦情など 三流君へメッセージを送る。

下記のフォームからメッセージを送ることができます。

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

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

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

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

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

広告:

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

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

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