Excelファイルデータベースの良いところ/悪いところ(Excelデータベースその4)

ExcelVBA-Excelファイルをデータベースとして使う-その4です。 前回の記事はこちらになります。 hirocom777.hatenadiary.org

今まで紹介してきたExcelファイルをデータベースとして使う方法は、実際に僕も業務に応用してきていて、大きな成果を得ています。今回は、実際に運用するときに僕が気が付いた点について書きたいと思います。

Excelファイルをデータベースとして使う方法の特徴とはなんでしょうか?
また、実際に確認、応用する方法についても考えてみたいと思います。

①Excelだけで簡単なデータベースシステムが構築できる。

　この効果はとても大きいです。そもそもAccess持っている人が少数派。
Excelは日常業務で標準になっている職場も多いので導入のハードルはとても低いです。
システムの構築も、一度コツをつかんでしまえば比較的簡単です。

②データベースファイルの内容を簡単に確認、編集、活用できる。

　この効果も大きいですね。Excelファイルならば、データの活用方法も
無限大といっていいのではありませんか?要求したデータからVBAやExcelの関数使って整理したり、ピボットテーブル、グラフの作成なども考えられますね。
この辺は、想像力の勝負です。

③SQLを使用して複雑な検索をすることができる。

　これもすごいですね。SQLで検索、抽出したデータをVBAそのほかの方法で応用することも当然できるわけです。
前回は簡単なSQLを試してみましたがもっと複雑な例も試してみようと思います。

④同一ネットワーク内で複数の端末から安全にアクセス可能。

　これも素晴らしいです。複数の端末から安全にアクセス出来るのですから小規模なシステムの構築ならばこれで十分です。
残念ながら手元に適切なネットワーク環境が無いので確認できていないのですが、おそらく大丈夫でしょう(汗)
複数のExcelツールからアクセスすることで、使い勝手を確認することはできそうなので試してみましょう！！

⑤アクセススピードが速い。

　スピードは正義！！データベース接続ですから普通にExcelファイルを開くよりも早いというのは魅力です。でも、実際のところどうなんでしょうか?一度大きなデータを使って確認してみたいです。

などの利点があると思われます。いいことだらけですね。では、欠点はどうでしょう?

⑥扱えるレコード数が少ない(約100万行)

　レコード数が少ないといっても約100万行。でも、僕にはこれを上回るデータ量が必要となるシチュエーションはないですね。

⑦レコードの編集、削除ができない

　これはちょっと面倒ですね。データを管理する方法を考えないといけません。
ここは、Excelファイルをデータベースとして使う際の肝になるのではないのでしょうか?

つまり、これらの特徴を生かして使えばExcelファイルをデータベースとして使用することは、とても有効な方法といえると思います。

次回以降、これを踏まえて動作確認と応用方法を検討していきたいと思います。
hirocom777.hatenadiary.org
ExcelVBA-Excelファイルをデータベースとして使う連載はコチラからどうぞ！！

パーフェクトExcel VBAに取り組む！！

半年ほど前からノンプロ研なるコミュニティーに参加させていたているのですが
その縁で最近出版された「パーフェクトExcel VBA」なる書籍を手にすることができました。現在取り組んでいる真っ最中です。

今回は、この本を紹介させていただきます。

抜群の重厚感！！

・VBAの本なんてもう買わない！！

僕は仕事でExcelVBAを使うことが非常に多いです。
しかしながら、ここ暫くこのプログラム言語に関する書籍を購入していませんでした。どの本も書いてる内容は似たり寄ったりだし、知りたい情報は大概ネットを探せば見つかるから。

もうだいぶ前になるのですが、ExcelVBAの洋書を手に入れたことがあります。
今は最新版が出ています。

https://www.amazon.com/Excel-Power-Programming-Spreadsheets-Bookshelf/dp/1119067723/ref=cm_cr_arp_d_product_top?ie=UTF8
www.amazon.com

この本には、日本語の書籍にはない大切なことが沢山書かれていて僕に多くの学びをもたらしてくれましたが、最後まで読み進めることができませんでした。

　・全部英語(当たり前って言うか英語力ない)
　・かなり癖がある。アプローチ方法が独特！！欧米の感覚だとこれが当たり前なのか？
　・ボリュームがある。(厚さ約6センチ、1000ページ以上！！)

そして、これに相当する日本語の本を当時見つけることができませんでした。
本はいいや。もう、ネットで十分！！

・やっと見つけたよ！！

「パーフェクトExcel VBA」は、この無念を晴らせる内容になっていました。

ExcelVBAの詳細について、とても丁寧に書かれています。ExcelVBAは細かいところを省略しても何とか動くことが多いので、この辺のところをあまり確認せずに何となく使っている人(僕)が多いのですが、この抜け落ちたピースを一つずつ慎重に埋めていってる感じです。

一つ一つ確認しながら読み進めていくと、今まで漠然と覚えて使っていた技術が筋道を立てて整理されていく感覚が得られます。同じ量の知識でも整理されている物といない物では価値が全然違いますよ！！

当然、ボリュームはあります(575ページ！！)ので、ExcelVBAの勉強を始めて間もない人には厳しい内容です。簡単な入門書で一通り勉強してから取り組むのがよいと思います。
(著者のブログ『いつも隣にITのお仕事』https://tonari-it.com/vba-manual/　なんかお勧めです)

そして、これが一番大事なこと。
最初から一つずつ丁寧に読み進めることをお勧めします。

・なぜ今頃になって

でも、ここで一つの疑問が浮かび上がります。
それは、なぜこのような本が今までなかったのかということ。
書いている内容についても、ExcelVBAという比較的歴史のあるプログラミング言語の仕様についての説明が中心。つまり、その殆んどはかなり前から存在していたということ。

よく解らないけれど、ひょっとして時代が大きく変わってきて本物を求め始めたからかな?
比較的簡単に結果を出せるツールとして重宝されているExcelVBA。
すぐに結果が出せるから、今まではとりあえず結果の出る内容だけでよかった。
でも、より複雑化していくに世の中にあって本当の結果が求められることが多くなった。
そして実はExcelVBAには、もっと秘めた大きな力を持っていた。

このことに気が付いて、求める人が増えてきたからじゃないかな？

・そうとわかったら

はじめに述べた通り、僕は今この本に取り組んでいる真っ最中。
そして、合わせて再チャレンジしていることがあります。
それは、先の洋書にもう一度トライすること。今度は頼りになる水先案内人がいるから多分大丈夫。
何か得られることがあったら、このブログにも書きますね！！

ところで、本書の著者は英語の勉強を始めたと聞きましたが
(https://it-english.jp/reasons-to-study-english/)
この本、英訳して海外で出版したら売れるんじゃないのかな?

ご検討よろしくお願いいたします(笑)

SQLを使ってみる！！(Excelデータベースその3)

ExcelVBA-Excelファイルをデータベースとして使う-その3です。 前回の記事はこちらになります。 hirocom777.hatenadiary.org
さて、データベースらしくSQLを使ったデータの検索を説明したいと思います。
Microsoft ActiveX Data Objects 2.8 Libraryを参照設定して試してみてください。 ソースリストは此方です。

Public Function SelectRecord(strFileName As String, strSQL As String, Optional rngRange As Range = Nothing) As Long
On Error GoTo HrrorHandler
  Dim adoCn As Object
  Set adoCn = CreateObject("ADODB.Connection")
  
  With adoCn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties") = "Excel 12.0"
    .Open strFileName
  End With
  
  Dim adoRs As Object
  Set adoRs = CreateObject("ADODB.Recordset")
  adoRs.CursorLocation = adUseClient
  
  adoRs.Open strSQL, adoCn
  
  If Not rngRange Is Nothing Then rngRange.CopyFromRecordset adoRs
  SelectRecord = adoRs.RecordCount
  
  adoRs.Close
  adoCn.Close

HrrorHandler:
  If Err.Number <> 0 Then MsgBox "データベース接続に失敗しました"

End Function

まずは、簡単に動かしてみましょう前回の名簿データベースにデータを追加してみました。

次に、動作確認用のプロシージャを用意しました。SelectDataを標準モジュールに組み込んだExcelファイル(名簿データベースとは別のファイル)のシートSheet1内に追加します。 SQLでデータを絞るプロシージャです。今回は、簡単な男性のデータのみに絞るSQLですね。

Private Sub SelectData()
Const SQL As String = "SELECT * FROM [Sheet1$] WHERE 性別 = '男性'"
  MsgBox SelectRecord(ThisWorkbook.Path & "\データ.xlsx", SQL, Sheet1.Range("A1")) & "件見つかりました"
End Sub

このプロシージャSelectDataを実行すると以下のレスポンスが返ってくるはずです。

　　　　　　　　　　　　

そしてSheet1には以下のように表示されます。

男性のデータのみ2件に絞って表示されました。

次に、プロシージャSelectRecordのポイントを解説します。
引数として使用する変数は以下の通りです。

　strFileName　データベースのファイル名
　strSQL 検索に使用するSQLの文字
　rngRange 　結果を表示するセルの起点(オプション)

データベースに接続する方法の基本は前回のプロシージャAddRecordと同じですね。
でも、今回は引数にシートの指定がありません。シートの指定はSQLの文字列内で指定するんです。
シート名の最後に"＄"をつけて"[]"で囲んで指定します。

プロシージャSelectRecordは関数です。成功すると、絞ったレコードの数を返します。
また、最後にシートのセルを指定するとセルを起点に絞ったレコードを全部書き出してくれます。
動作の確認に便利です。

SQLの内容を変えて色々試してみると面白いと思います。

次回は、実際に運用するときに僕が気が付いた点について書きたいと思います。
hirocom777.hatenadiary.org
よろしくお願いします！！
ExcelVBA-Excelファイルをデータベースとして使う連載はコチラからどうぞ！！

「超」整理法にもう一度向き合ってみた

野口悠紀雄　先生の「超」整理法---結構有名なこのワード。そして、紹介されている整理法『押し出しファイリング』。僕は、この方法をちょっと応用してPC上のデータ管理に使っています。

縁あって、この方法を他の方々に紹介する機会があったのですが少しだけ反応いただきました。

いい機会なので、長年お世話になっているこれらの方法にもう一度向き合ってみたいと思います。本記事は僕の所属しているコミュニティ--ノンプロ研 の『ノンプロ研 Advent Calendar 2019』adventar.org11日目の記事です。

・押し出しファイリング

そもそも、「超」整理法-『押し出しファイリング』が世に紹介されたのは以下の本。

「超」整理法―情報検索と発想の新システム(1993/11/1)

因みにこの記事を書いているのが2019年12月。25年以上前に提案されていたんです。
僕は、今も『押し出しファイリング』を運用し続けているのですが、とても有効です。

本書の中の序章で一般粋な整理法に対する問題定義がなされています。要旨は
　・情報を分類することは不可能
　・すべての情報に共通する唯一のキーは『時間軸』

そして第一章にて、『押し出しファイリング』が紹介されています。

『押し出しファイリング』とは
・角2サイズの封筒（A4書類が入るもの）を複数用意する
・書類をひとかたまりずつ封筒に入れて書棚に並べていく
・紙の書類はすべてここに集中させ、他の場所におかない
・新しく追加した封筒や取り出して使用した封筒は左端に置く
・使われない封筒は徐々に右に溜まっていくので、しかるべきタイミングで中身を捨てるか保存するか決める

そもそも、複雑化した情報社会の今、情報を完全に分類することなど不可能です。
分類しようとすると様々な問題が出てきます。(コウモリ問題--どの分野に分類していいか解らない　その他問題--分類できない情報が山のようにたまる　誤入問題--間違えて分類してしまったが最後、2度と探すことができなくなる)
そして、そんな不完全な状態では効率のいい情報の取り出しなど出来ない。

そこで、情報は分類せずに1か所にまとめて時間軸で管理する。
(ポケット一つ原則--上着にポケットが一つしかなければ、どのポケットに入れたのか迷うことはない)

でも、ちょっと待って！！野口先生は本書の中で分類はムダと述べているけれど
書類を封筒に入れていく段階で分類しているんじゃないの？そんな疑問を持っていました。分類しなくていいのであれば、封筒もいらないでしょ！！

僕は長年この方法を使ってきましたが、やはり封筒は必要です。
封筒がないと、雑然として書類の束がだきるだけ。情報の検索なんて絶対できません。

つまり、
分類は厳密でなく緩やかで構わない。全体は時間軸で管理
っていうことではないのかな！！

この『穏やかな分類』のさじ加減が勘所だと思うのですが、当然人によります。
野口先生はこの辺どう思っているんでしょう？

・パソコンによる「超」整理法

続いて第二章はパソコンによる「超」整理法となっています。
こんなに昔からパソコンを使った情報管理に着目するなんて、野口先生さすがです！！

因みにこの時、野口先生のパソコンHDD容量は80MB！！
1か月に約1MB強の文章を書く(通信文や既存ファイルの修正も含む)
標準的なボリュームの新書である「超」整理法の原稿容量は約0.2MBとのことです。

運用方法は、HDDの中を年月別にディレクトリを作って該当年月のファイルを保管。
作業はフロッピーディスク(容量は約1MB、今となっては死語)ベース。ファイルは基本的にテキストファイル。

HDD中のファイルは以下をキーにして検索していく。

　第一キー⇒時間軸
　第二キー⇒拡張子　論文の場合はRON、メモの場合はMEMなどとしておく
　第三キー⇒ファイル名　最初の3文字を相手先(出版社など)としておく
　第四キー⇒ファイル中のワード

あれ？拡張子やファイル名ををキーにするって、分類してるよね？
やっぱり少しは分類しないといけないのかな？

・「超」AI整理法

　確かに便利！！でも改めて見直してみるとちょっと疑問が残る。25年以上前のことだしパソコンの性能は爆発的に向上してるし、野口先生は今、どう考えているんですかね。

　と、言う訳で以下を取り寄せてみました。

「超」AI整理法 無限にためて瞬時に引き出す(2019/6/29)

　半年前！！っていうか野口先生、来年80歳ですよね？

で、書いている内容はというと・・・

・コンピューター(スマホ)は大幅に性能アップ！！音声入力や画像認識が可能になったので使いやすくなった。
・情報量は爆発的に増えたので、捨てたり分類せずに検索する方法が適切
・「超」整理法-『押し出しファイリング』は情報爆発時代で重要な考え方だ！！
・文章のアーカイブはＧメールの送信記録で！！
・音声入力、画像認識であんなことや、こんなことが出来る！！

『押し出しファイリング』については、同じ内容が書かれているだけでした。残念。
先生はそんな些末な(？)ことより音声入力や画像認識に夢中みたいです。お若いですな。
80MBのハードディスクはＧメールに置き換わったんですね・・・時の流れを感じます。
そして、これらを駆使して原稿の執筆スピードは数倍に跳ね上がってる！！

『押し出しファイリング』の内容自体は変わっていないので、やはりある程度の分類は必要なようで、それは使っていきながら勘所を養っていく形になりそうです。また、よく読んでみると検索キーワードの重要性が述べられています。やっぱり、ただデータを放り込んで置くだけじゃダメなんですね。

・やはり一筋縄ではいかない

　『押し出しファイリング』自体はとてもシンプルなのですが、それゆえ奥が深く簡単には結論出ないようです。でも、それを再認識できたのは良かったです。

　さて、冒頭で簡単にお話したのですが、僕は、この方法を応用してPC上のデータを管理するツールを作って使っています。『押し出しファイリング』の構成要素を以下のように置き換えて管理するツールです。

　　書棚→親フォルダ
　　角2の封筒→親フォルダ下のフォルダ群
　　書類→データファイル

　開いた日時を記録しておいてその順番に並べて表示する。
　各フォルダには、インデックスつけて検索できる。

自分で言うのも何なんですが、これが「超」便利。今回学んだポイントも踏まえて更にパワーアップしたら、皆さんにも紹介できたらいいなと思います。
最後までお読みいただきありがとうございました！！

データを追加してみよう！！(Excelデータベースその2)

ExcelVBA-Excelファイルをデータベースとして使う---の2回目です。
前回の記事はこちらです。

hirocom777.hatenadiary.org

今回は、データの追加を実行するプロシージャAddRecordについて説明していきたいと思います。
Microsoft ActiveX Data Objects 2.8 Libraryを参照設定して試してみてください。

ソースリストは此方です。

Public Function AddRecord(strFileName As String, strSheetName As String, vrnField() As Variant, vrnData() As Variant) As Boolean
On Error GoTo ErrorHandler
  Dim adoCn As Object
  Set adoCn = CreateObject("ADODB.Connection")
  
  With adoCn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties") = "Excel 12.0"
    .Open strFileName
  End With
  
  Dim adoRs As Object
  Set adoRs = CreateObject("ADODB.Recordset")
  
  With adoRs
    .Open "[" & strSheetName & "$]", adoCn, adOpenKeyset, adLockOptimistic
    .AddNew vrnField, vrnData
    .Update
  End With
  
  adoRs.Close
  adoCn.Close

ErrorHandler:
  If Err.Number <> 0 Then
    MsgBox "データベース接続に失敗しました"
  Else
    AddRecord = True
  End If

End Function

まずは、簡単に動かしてみましょう。簡単な名簿のデータベースを作ってみます。

次に、動作確認用のプロシージャを用意しました。AddRecordを標準モジュールに組み込んだExcelファイルに別の標準モジュールを用意して以下のプロシージャを追加します。データベースファイルに一人分のデータを追加するプロシージャです。

Private Sub AddData()
Dim vrnField() As Variant
Dim vrnData() As Variant
  vrnField() = Array("登録日時", "氏名", "性別", "血液型", "年齢")
  vrnData() = Array(Format$(Now, "YYYY/MM/DD hh:mm:ss"), "山田　太郎", "男性", "O", 25)
  If AddRecord(ThisWorkbook.Path & "\データ.xlsx", "Sheet1", vrnField(), vrnData()) Then
    MsgBox "登録しました"
  Else
    MsgBox "登録に失敗しました"
  End If
End Sub

このプロシージャAddDataを実行すると名簿ファイルは以下のようになっているはずです。

山田さんのデータが追加されました。

次に、プロシージャAddRecordのポイントを解説します。
引数として使用する変数は以下の通りです。

　strFileName　データベースのファイル名
　strSheetName　データベースファイル内のアクセスするシート名
　vrnField()　データベースのフィールド名が入る配列
　vrnData()　データベースに書き込むデータが入る配列

このプロシージャはADODB.Connectionオブジェクトを使用してExcelファイルをデータベースとして接続してデータを追加していきます。
基本はAccessファイルに接続する場合と同じなのですが(https://tonari-it.com/excel-vba-access-database-connection/)
ポイントは、Properties("Extended Properties") の指定。"Excel 12.0"とするとExcel2007以降で作成したブックと接続することができます。
また、Excelファイルの場合シートをデータベースのテーブルとして扱います。(セル範囲での指定もできるようですが、ここでは複雑になるので割愛します)その際には、
シート名の最後に"＄"をつけて"[]"で囲みます。

その他は、Accessデータベースに接続する方法と同じですね。
因みに、Excelファイルを対象とした場合レコードの削除はできないようです。
この辺は運用方法を工夫する必要があります。

次回は、データベースらしくSQLを使ったデータの検索を説明したいと思います。
hirocom777.hatenadiary.org

お楽しみに！！
ExcelVBA-Excelファイルをデータベースとして使う連載はコチラからどうぞ！！

Excelファイルをデータベースとして使えるよ！！(Excelデータベースその1)

ADODB.Connectionオブジェクトを使用すると、ExcelVBAから、Accessデータベースを操作することが出来ます。(https://tonari-it.com/excel-vba-access-database/　他)

この方法、便利なんですが結局どこかでAccessが必要になってきます。

そんなに本格的なデータベースが欲しいわけではないけれど、ちょっとしたデータの管理システムを構築したい時もある。

そんな時は、ADODB.Connectionオブジェクトで制御する対象をExcelファイルに指定すると、Excelファイルをデータベースファイルとして使用することができます。

すると、以下のような利点があります。

・Excelだけで簡単なデータベースシステムが構築できる。

・Excelファイルなので、データベースファイルの内容を簡単に確認できる。

また、普通にExcelファイルを開いてアクセスするよりも

・アクセススピードが速い。

・同一ネットワーク内で複数の端末から安全にアクセス可能。

と、いう利点もあります。

そこで、Excelファイルでデータベースアクセスする標準モジュールを作ってみました。Microsoft ActiveX Data Objects 2.8 Libraryを参照設定して使用します。

尚、Excel形式をデータベースファイルとして使用した場合、レコードの削除は出来ません。

次回以降で、使い方、ソースリストの解説を書こうと思います。

hirocom777.hatenadiary.org

 お楽しみに！！

ExcelVBA-Excelファイルをデータベースとして使う連載はコチラからどうぞ！！

ブログを始めてみようと思います

   HiroCom777のブログです。最近いろいろな分野に手を出すようになって来たので、学習記録をつけてみようと思います。

　僕も、今まで色々な人のブログに書いてある情報に助けられてきました。

 このブログの情報も誰かの役に立てれば嬉しいです。

以下のような項目について学習しながら書いていきたいと思います。

ソフトウェア

　ExcelVBA、Python、JavaScriptなど、実際の活用方法を追求

ハードウェア

　マイコンボードArduino UNOを中心に基本と応用方法について探る

インターフェース

　ネットワーク、USB、シリアル通信、バーコード等の知識まとめていく

人工知能(深層学習)

　これからの世界を探っていく

雑感

　その他思ったこと。これが一番多いかな

以上、脈絡もなく並べてみました。勿論、進めていくに従って項目は増減していくと思います。そして特に、これらの項目を連携させてさらに応用していく方法を模索していきたいです。

その前に、まずブログ書く練習ですな😁

よろしくお願いします。