ExcelVBA-Excelファイルをデータベースとして使う-その9

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

クラスモジュールの解説

今回は、前回紹介したクラスの詳細を説明しようと思います。クラスモジュールのリストは、上記の前回記事をご確認ください。

・概要

 今回紹介したクラス-名付けてExcelデータベースクラス(clsExcelDbase)ですが、指定されたxlsx形式のファイルをデータベースとしてアクセスして接続、アクセスするためのクラスです。1つのデータベースに紐づいたオブジェクトを作成できます。構成は、以下のようになります。

・コンストラク

clsExcelDbaseのコンストラクタ(Class_Initialize)では、オブジェクトが生成された時の処理を記述しています。具体的にはADO接続オブジェクトを宣言してExcelファイル(xlsx)対象で設定。ADOレコードセットオブジェクトを宣言の、処理を行っています。

・デストラク

clsExcelDbaseのデストラクタ(Class_Terminate)では、オブジェクトが破棄される前の処理を記述しています。具体的にはトランザクションの終了処理と、データベース接続のクローズ処理です。(トランザクション処理については、別途トランザクションの項目を参照してください。)

・プロパティー

・dbFileName(データベースファイル名)

接続するデータベースファイル名です。このプロパティーによって接続するデータベースファイルを設定します。実在しないフィルは設定できません。また、一度設定すると変更することはできません。

・dbTableName(テーブル名)

接続するデータベースのテーブル名です。このプロパティーによってデータベースファイル内のどのテーブルに接続するかを設定します。先にdbFileNameを設定しないと設定することはできません。また、一度設定すると変更することはできません。dbTableNameが設定されると同時にデータベースへの接続処理が実行されます。また、トランザクション処理も開始されます。(トランザクション処理については、別途トランザクションの項目を参照してください。)接続が完了すると、接続したデータベースからフィールド名の一覧を取得してdbFieldListに格納します。

・dbFieldList(フィールド名リスト)

接続するデータベースのフィールド名リストです。バリアント形式の配列で返します。dbTableNameが設定されたときに接続したデータベースから取得されます。この値を変更することはできません。

・メソッド

メソッドは今のところ一つだけです。今後追加していく予定です。

・AddRecord(レコードの追加)

引数に指定したバリアント変数の1次配列をデータベースにレコードとして追加します。

使い方

 使い方については前回の記事で紹介したプロシージャAddDataByClassを見ていただければわかると思います。以下のような流れで使います。

  1. データベースオブジェクトを宣言して、New clsExcelDbaseをセットします。
  2. データベースのファイル名とテーブル名をプロパティーにセットしてます。
  3. メソッドを使用してデータベースにアクセスします。(今は追加だけです)
  4. オブジェクトを使用しているプロシージャが終了するなどしてオブジェクトの参照がなくなるとデストラクタが起動して処理を確定します。また、Set オブジェクト = Nothingとすると終了します。

1度接続してしまえば、複雑な処理(SQLで検索した結果を確認してから、編集や書き込みを行うなど)が可能です。

トランザクション

 上にも書いた通り、このクラスを使用したオブジェクトでは複雑な処理を纏めて実行することができます。でも、データベースでは複数の個所からデータベースにアクセスされることがあります。データ書き換え中にほかの端末から該当データにアクセスがあった場合、トラブルの原因になります。また、処理実行中にシステムに不具合(停電、LANケーブル抜け等々)が発生した場合データベースの内容が壊れてしまうと大変です。
 トランザクションは、このようなトラブルに対応するためのものです。このクラスでは、データベース接続時にトランザクションを開始。処理完了時に問題がなければ処理内容を有効にします。エラーが発生した場合には、それまでのアクセス処理を無効にして接続をOFFにします。
 つまり、このクラスモジュールを使用することによってデータベースアクセスの信頼性が増すってことですね!!(自我自賛!!)

以上、Excelファイルのデータベースアクセスクラスの説明でした。

次回はこのクラスにメソッドを追加して、より実用的に仕上げていこうと思います。お楽しみに!!

ExcelVBA-Excelファイルをデータベースとして使う連載です
その1:Excelファイルをデータベースファイルとして使えるよ!!
その2:データを追加してみよう!!
その3:SQLを使ってみる!!
その4:Excelファイルデータベースの良いところ/悪いところ
その5:Excelファイルデータベースの読込みスピードは?
その6:Excelファイルデータベースの書込みスピードは?
その7:書込みスピードをあげるには?
その8:クラスモジュール作ってみた!!
その9:クラスモジュールの解説

ExcelVBA-Excelファイルをデータベースとして使う-その8

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

クラスモジュール

今回は、前回構想を練ったクラスをご紹介します。

Option Explicit
'Excelファイルのデータベースコントロールのクラスです。

Private dbFileName_ As String 'データベースファイル名
Private dbTableName_ As String 'テーブル名
Private dbFieldList_() As Variant 'フィールドリスト

Private adoCn As Object
Private adoRs As Object

'初期化
Private Sub Class_Initialize()
  Set adoCn = CreateObject("ADODB.Connection")
  adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"
  adoCn.Properties("Extended Properties") = "Excel 12.0"
  Set adoRs = CreateObject("ADODB.Recordset")
  adoRs.CursorLocation = adUseClient
End Sub
 
'終了処理
Private Sub Class_Terminate()
  If Err.Number <> 0 Then
    adoCn.RollbackTrans
    MsgBox "データベース接続に失敗しました"
  Else
    adoCn.CommitTrans
  End If
  adoRs.Close
  adoCn.Close
End Sub

'ファイル名を設定します
Public Property Let dbFileName(ByVal fileName As String)
  Select Case True
  Case dbFileName_ <> ""
    MsgBox "ファイル名は途中で変更できません"
  Case Dir$(fileName) = ""
    MsgBox "ファイルが見つかりません"
  Case Else
    dbFileName_ = fileName
  End Select
End Property

'ファイル名を取得します
Public Property Get dbFileName() As String
  dbFileName = dbFileName_
End Property

'テーブル名を設定します
Public Property Let dbTableName(ByVal tableName As String)
  Select Case True
  Case Dir$(dbFileName_) = ""
    MsgBox "データベースファイルを指定してください"
  Case dbTableName_ <> ""
    MsgBox "テーブル名は途中で変更できません"
  Case Else
    adoCn.Open dbFileName_
    adoRs.Open "[" & tableName & "$]", adoCn, adOpenKeyset, adLockOptimistic
    dbTableName_ = tableName
    adoCn.BeginTrans
    'フィールドリストを設定します
    Dim adoField As ADODB.Field
    Dim i As Long: i = 0
    For Each adoField In adoRs.Fields
      ReDim Preserve dbFieldList_(i)
      dbFieldList_(i) = adoField.Name
      i = i + 1
    Next
  End Select
End Property

'テーブル名を取得します
Public Property Get dbTableName() As String
  dbTableName = dbTableName_
End Property

'フィールドリストを取得します
Public Property Get dbFieldList() As Variant()
  dbFieldList = dbFieldList_
End Property

'Excelデータベースにレコードを追加します
Public Sub AddRecord(recordData() As Variant)
  adoRs.AddNew dbFieldList_, recordData
  adoRs.Update
End Sub

動かしてみよう!!

では、動かしてみましょう!!準備は以下になります。データベースとして使う-その6と同じ条件で実行しますがおさらいです。
1.任意フォルダにxlsmファイルを作成して空のクラスモジュールを作成。上記クラスのコードをコピーします。
2.上記ファイルのSheet1に下記サイトから静岡県データ(CSVファイル-約3700件)をコピーして下さい。
jusyo.jp
3.Sheet1には、以下のコードを配置してください。クラスモジュールからデータベースコントロールオブジェクトを作成して操作するコードです。

Option Explicit
'データベースコントロールクラスを使用してデータをコピーします。
Private Sub AddDataByClass()
Dim recordData() As Variant
Dim i As Long
  Debug.Print Now
  Dim db As clsExcelDbase
  Set db = New clsExcelDbase
  db.dbFileName = ThisWorkbook.Path & "\Sizuoka.xlsx"
  db.dbTableName = "sizuoka"

  i = 2
  Do While Me.Cells(i, 1) <> ""
    recordData() = Range(Me.Cells(i, 1), Me.Cells(i, 22))
    recordData() = WorksheetFunction.Transpose(WorksheetFunction.Transpose(recordData))
    db.AddRecord recordData()
    i = i + 1
  Loop
  Debug.Print Now
End Sub

4.コピー先のファイル『Sizuoka.xlsx』を同じフォルダに作成します。シート名は『sizuoka』としてシートの1行目にはフィールド名のみ入力しておきます。

この状態で、Sheet1のAddDataByClassを実行してみましょう。結果は、……約3秒!!。爆速です!!
やっぱり、レコードごとにデータベース接続ON/OFFしなければデータベースの圧勝です!!
クラスモジュールはちょっと長めになっちゃいましたけれど、一度作ってしまうと色々応用がきくんですよ!!

と、いう訳で次回はクラスの詳細解説をします。お楽しみに!!
hirocom777.hatenadiary.org

ExcelVBA-Excelファイルをデータベースとして使う連載です
その1:Excelファイルをデータベースファイルとして使えるよ!!
その2:データを追加してみよう!!
その3:SQLを使ってみる!!
その4:Excelファイルデータベースの良いところ/悪いところ
その5:Excelファイルデータベースの読込みスピードは?
その6:Excelファイルデータベースの書込みスピードは?
その7:書込みスピードをあげるには?
その8:クラスモジュール作ってみた!!
その9:クラスモジュールの解説

ExcelVBA-Excelファイルをデータベースとして使う-その7

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

対策を考える

ADOでExcelファイルデータベース書き込みのスピードを測ってみたもののスピードは激遅!!原因は簡単で1レコード書き込む度にデータベース接続をON/OFFやっているから、ってのが前回まででした。書き込む量が1レコードだけだったらこれでもいいのかもしれないけれど、まとまった量をアクセスする場合は困る。CPUへの負担もかなりのものでしょうね。

こういう場合は、

・データベースを開く
・まとまったデータを書き込む
・データベースを閉じる

という手順でプログラムを組んでいけば、それなりのスピードが出ると思われます。でもね、そんなことやっても応用の利かないプログラムができるだけ。開くと閉じるの間には、検索したり、読んだり、編集したり色々複雑な処理が出てくることもある,って言うかそのほうが多い。処理が変わるたびにプログラムを組みなおさなければなりません。使い勝手がよくなければナイスなプログラムじゃないですよね。

そこで考えました・・・

ひょっとして、これってクラスモジュールで何とかならないかな?

でた、クラスモジュール。今まで何度も勉強してきましたが理解には及ばず。それでも地道に取り組み続けて最近ちょっとだけわかったような気になっている。これって、クラスモジュールの使いどころじゃないのでしょうか?

クラスって??

さて、その前にクラスとは何なのか僕なりの解釈で書きたいと思います。当然僕の解釈ですのであてずっぽうです。鵜呑みにしないでくださいね!!あと、間違いがわかる人教えてください。よろしくお願いします!!

クラスとは・・・プログラム生産、管理を効率化するためのプログラムの設計図または、設計図を作る仕組み。データを記憶する部分(変数、プロパティー?)と動くコード(メソッド?)の組み合わせ方を設計図として書いてあります。

クラスをもとに作られた実際のプログラム(実体化されたもの)がオブジェクトと言われます。この時、オブジェクトはクラスのインスタンス(実体化例)であるといわれます。実体化されたオブジェクトには、プロパティー(データ)やメソッド(プログラム)が組み込まれていて、それらを使ってオブジェクトを操作することができます。

クラスの中には実際にオブジェクトを作る際に使用するプログラムが入っていて、それをコンストラクタと呼びます。クラスはコンストラクタを使ってオブジェクトを作ります。また、オブジェクトが役割を終えて消去されるときに使用されるプログラムもあって、デストラクタと呼ばれます。クラスはデストラクタを使ってオブジェクト消去時に必要な処理を実行します。コンストラクタもデストラクタもメソッドの一種です。

どうでしょう!?

作戦!!

どんなクラスを作るのか作戦です。データベース(ファイルとテーブル)を指定してコントロールできるオブジェクトを作ろうと思います。データベースコントロールのオブジェクトを作るにあたって、最初に必要な処理をオブジェクト作成時(コンストラクタ実行時)にまとめてしまいます。残念ながら、VBAではコンストラクタで値(この場合はデータベースのファイル名とテーブル名)を指定することができませんので、これらは別途設定できるプロパティーを持とうと思います。

プロパティー(ファイル名とテーブル名)が決まればデータベース接続の準備は出来上がりですので、直ちにデータベースに接続します。

データベース接続が終わったら、オブジェクトのメソッドを使用してデータベースにアクセスしたり、検索したりします。
この時に、呼び出すメソッドの種類、メソッドに使用する設定などを組み合わせることで様々なデータベース操作を実現します。
書き込みのメソッドを繰り返せば大量の書き込みもできますし、データベースの接続ON/OFFが間に発生しないからスピードも速いはずです。

一通りのデータベース処理が終了して用済みになったら、デストラクタを使用してオブジェクトを消去します。この時に、データベースを閉じる作業も一緒に実施します。


どうでしょうか?正直初めて自分でクラスというものを考えてみたのですが合ってますかね?
達人たちは、どうやってクラスを考えているのでしょうか?よかったら教えてください。

次回は、今回立てた作戦をもとにデータベースコントロールクラスを作成してリベンジしようと思います。お楽しみに!!
hirocom777.hatenadiary.org
ExcelVBA-Excelファイルをデータベースとして使う連載です
その1:Excelファイルをデータベースファイルとして使えるよ!!
その2:データを追加してみよう!!
その3:SQLを使ってみる!!
その4:Excelファイルデータベースの良いところ/悪いところ
その5:Excelファイルデータベースの読込みスピードは?
その6:Excelファイルデータベースの書込みスピードは?
その7:書込みスピードをあげるには?
その8:クラスモジュール作ってみた!!
その9:クラスモジュールの解説

英語って便利でしょ?

僕は今まで暇なときには英語に触れるよう気にかけてきました。後述しますが、そうすると人生が少しだけ充実している気がするから・・・怠けていないよっていう言い訳かもしれません。

僕が所属しているノンプロ研でも英語熱が高まってきたみたいで先日集まりがありました。今日は、そこで聞いたり話したりしたことをメモしておこうと思います。

僕の英語の実力は……

 以前は頑張って取り組んでいた時期もあったのですが、今はさっぱりですね。仕事で英語を使うことも殆んどないです。職場全体でも英語を使っている仕事はないです。たまーに海外からお客さんが来るくらいですかね。ま、その時は駆り出されるんですが……年に1回あるか無いかですね。英語を使う機会はほとんどありません。

それじゃなぜ学ぶのか

 それじゃ、なぜ使いもしない英語を学ぶのか?それは、気休めです(笑)いやね、英語って便利ですよ!!だって、何やったって勉強していることになるでしょ。マンガ読んでても、音楽聞いてても、映画見てても、海外に遊びに行っても、ゲームにうつつを抜かしていても、ぜーんぶ英語勉強していますって説明出来ちゃいます!!猛勉強中!!いやー英語って便利ですねぇ!!

よかった事、悪かった事

 『英語をやっててよかったな』ってこともあるにはあるんですよ。一番は上司から『英語勉強しろ!!』って圧がかからないことですかね。若い人から『英語勉強したほうがいいですかね?』って聞かれた時も『勉強しておくと、上司から勉強しろって言われなくなるよ!!』って答えています。(本末転倒)

調べものする時も英語のサイトも含めて調べるから速いですよ。実際読むのはWebの翻訳サービス使ってはしょっちゃうんですけれど、周りの人たちはそんなこともしないんです。で。面倒くさいと訳さない文書そのまま付けて書類出しちゃう。すると、上のチェックが甘くなります!!ね、英語って便利でしょ(笑)

上にも述べた通り、海外からお客さんが来ると駆り出されちゃうのが嫌ですけれどね。イレギュラーな仕事だからギャラを増やしてほしいです。英語重要だって思うんだったら英語仕事のギャラ増やしてよ!!

どうやって取り組んだらいいんだろ

 勿論、そんな適当な調子で臨んだのは僕だけ。ほかの皆さんは、実際に仕事に使ったり、海外で生活したことがあったりなので真剣です。でも、僕みたいにそんなしっかりした目的を持っていない人はどうしたらいいんでしょうか?

漠然とですが、思うんです。みんな英語と勉強を結び付けすぎちゃってません?

僕が思うに、英語って勉強するものじゃなくって、喋る(聞く/読む/書く)ものなんじゃないかって。

勿論、それらの行為をより良く実行するには勉強が必要です。結局勉強なんですが、一拍置くことで気持ちが変わりませんか?

そんなことを考えました。

最後に、以下の本を紹介させてください。

edmm.jp

関西出身の著者が関西の方をターゲットに書かれたとのことですが、それ以外の人でもすごく楽しめます。

こんなに面白い本を読むだけでも勉強になるって、英語って便利でしょ(笑)

ExcelVBA-Excelファイルをデータベースとして使う-その6

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

書き込みスピードを確認する

さて、今回はデータベース接続の書き込みスピードについて確認してみたいと思います。
読込ではデータベース接続のほうが速かったのですが、書き込みではどうでしょう?
同じデータにアクセスしてスピードを比べてみようと思います。

今回も、以下のサイトからデータをいただいて使わせてもらおうと思います。
jusyo.jp

こちらの静岡県データ(CSVファイル-約3700件)をシートにコピーして使わせていただきます。ありがとうございます!!
今回は、シート上のデータをフィールドだけ入力した『Sizuoka.xlsx』のシート『sizuoka』にコピーする時間を比べたいと思います。

ADOでやってみる

それではやってみましょう。書き込みには以下で紹介したAddRecordを使えばいいですね。
hirocom777.hatenadiary.org
データをコピーしたシートに以下のプロシージャをコピーしてください。

Option Explicit

Private Sub AddData_ADO()
Dim vrnField() As Variant
Dim vrnData() As Variant
Dim i As Long
  Debug.Print Now
  vrnField() = Me.Range("$A$1:$V$1")
  vrnField() = WorksheetFunction.Transpose(WorksheetFunction.Transpose(vrnField))

  i = 2
  Do While Me.Cells(i, 1) <> ""
    vrnData() = Range(Me.Cells(i, 1), Me.Cells(i, 22))
    vrnData() = WorksheetFunction.Transpose(WorksheetFunction.Transpose(vrnData))
    AddRecord ThisWorkbook.Path & "\Sizuoka.xlsx", "sizuoka", vrnField(), vrnData()
    i = i + 1
  Loop
  Debug.Print Now
End Sub

『 WorksheetFunction.Transpose(WorksheetFunction.Transpose(vrnData))』ですが、これは2次元配列を1次元配列に直す裏ワザです。
レンジ範囲を配列にコピーすると1行でも2次元配列になっちゃうんです。1レコードのデータを表すので1次元配列じゃないといけません。
結果は?……約12分!!え?すごい遅い!!秒じゃなくて分!!どうしてこうなった??

VBAでは?

VBAではどうなんでしょ。今度はシートに以下のプロシージャを配置して実行してみてください。

Option Explicit

Sub AddData_VBA()
Dim wbk As Workbook
Dim rngRange As Range
Dim i As Long

  Debug.Print Now
  Application.ScreenUpdating = False
  
  Set wbk = Workbooks.Open(FileName:=ThisWorkbook.Path & "\Sizuoka.xlsx")
  
  i = 2
  For i = 2 To Me.Range("A:A").Count
    If Me.Cells(i, 1) = "" Then Exit For
    Set rngRange = Range(Me.Cells(i, 1), Me.Cells(i, 22))
    rngRange.Copy wbk.Sheets("sizuoka").Range(rngRange.Address)
  Next
  wbk.Close SaveChanges:=True
  
  Application.ScreenUpdating = True
  Debug.Print Now
End Sub

結果は、……約50秒!!。て、言うかこれやってることは前回とほぼ一緒。データを別のファイルにコピーしているだけだからあまり変わらないんですよね。検索していない分こちらのほうがちょっとだけ早いかな。

どうしてこうなった

遅い。なんでこんなに遅いんだ・・って原因は分かってます。レコードの書き込み1回ごとにデータベース開いて閉じてますからね。前回の読込の時はSQLで絞ったデータを1度に読んでるから早いんです。

データベースを開く⇒データをまとめて書き込む⇒データベースを閉じる

って手順でやれば早くなるはずです。でも、データの渡し方とか考えるとちょっとめんどくさいんですよね。

まぁ、データ登録なんて大概1レコードずつだし、まとめて書き込むことなんてあまりないから・・・って
悔しいな!!
なんかいい方法はないのかね。

と、いう訳で次回はこの問題の解決方法について検討したいと思います。
hirocom777.hatenadiary.org
ExcelVBA-Excelファイルをデータベースとして使う連載です
その1:Excelファイルをデータベースファイルとして使えるよ!!
その2:データを追加してみよう!!
その3:SQLを使ってみる!!
その4:Excelファイルデータベースの良いところ/悪いところ
その5:Excelファイルデータベースの読込みスピードは?
その6:Excelファイルデータベースの書込みスピードは?
その7:書込みスピードをあげるには?
その8:クラスモジュール作ってみた!!
その9:クラスモジュールの解説

ExcelVBA-Excelファイルをデータベースとして使う-その5

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

本当にスピードが速いのか比べてみる

さて、今回はデータベース接続したときの読込スピードについて確認してみたいと思います。
VBAでファイルを開いて読むよりも、データベース接続のほうが速いって本当なんですかね。
同じデータにを読み込んでスピードを比べてみようと思います。

とはいっても、多少のデータではスピードの差なんて測れませんね。そこで、以下のサイトからデータをいただいて使わせてもらおうと思います。
jusyo.jp

こちらの全国データ(CSVファイル-約15万件分)をxlsx形式に変換して使わせていただきます。ありがとうございます!!
ファイル名は『zenkoku.xlsx』シート名も『zenkoku』としています。

今回は、このデータから都道府県名が『静岡県』のデータのみ抽出して速さを比べてみようと思います。

VBAで検索、コピーした場合

まずは、VBAだけでやってみましょう。『zenkoku.xlsx』と同じファルダに別のxlsmファイルを作成してシートモジュールに以下のプロシージャを置いて実行してみてください。ファイルを開いてからフィールド『都道府県』を検索して『静岡県』とある行をコピーしていきます。

Option Explicit

Sub VBA_Test()
Dim wbk As Workbook
Dim rngArea As Range
Dim rngRange As Range
Dim strAddress As String
Dim i As Long

  Debug.Print Now
  Application.ScreenUpdating = False
  
  Set wbk = Workbooks.Open(Filename:=ThisWorkbook.Path & "\zenkoku.xlsx")
  
  wbk.Sheets("zenkoku").Range("1:1").Copy Me.Range("1:1")
  
  Set rngArea = wbk.Sheets("zenkoku").Range("H:H")
  Set rngRange = rngArea.Find(What:="静岡県", LookAt:=xlWhole)
  strAddress = rngRange.Address
  i = 2
  Do
    rngRange.EntireRow.Copy Me.Cells(i, 1).EntireRow
    Set rngRange = rngArea.FindNext(rngRange)
    i = i + 1
  Loop While strAddress <> rngRange.Address
  wbk.Close
  
  Application.ScreenUpdating = True
  Debug.Print Now
End Sub

シートには、静岡県のデータ(約3700件)だけコピーされたと思います。


    f:id:HiroCom777:20200117214650j:plain
かかった時間は僕の環境で……約50秒!!まずまずの時間です。
因みに高速化のために定番の"Application.ScreenUpdating = False"を記述しています。これを記述しないと約55秒となりました。

ADOを使った場合

お次は、いよいよADOを使った場合!!以下でご紹介したSelectRecordを使えばいいですね。
hirocom777.hatenadiary.org
でもその前に、VBAだけのコードではフィールド名の表示もしています。SelectRecordはレコードの取得だけなので、別途フィールド名を取得して表示させましょう。以下が、フィールド一覧を取得するプロシージャです。標準モジュールに追加してください。

Function GetFieldList(strFileName As String, strSheetName As String) As Variant
On Error GoTo HrrorHandler
  Dim strField As String
  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.Open "[" & strSheetName & "$]", adoCn
  
  Dim adoField As ADODB.Field
  For Each adoField In adoRs.Fields
    strField = strField & "," & adoField.Name
  Next
  
  GetFieldList = Split(Mid$(strField, 2), ",")
  
  adoRs.Close
  adoCn.Close

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

End Function

データベースのファイル名と、対象となるテーブルのシート名を指定するとVariantの配列でフィールド名の一覧を返します。
その上で、シートモジュールに以下のプロシージャを置いて実行してみてください。SQLでフィールド『都道府県』が『静岡県』となっているレコードを抽出してコピーします。

Option Explicit

Sub ADO_Test()
Dim strSQL As String
Dim vrnField As Variant
  Debug.Print Now

  vrnField = GetFieldList(ThisWorkbook.Path & "\zenkoku.xlsx", "zenkoku")
  Me.Range(Me.Range("A1"), Me.Range("A1").Offset(0, UBound(vrnField))) = vrnField

  strSQL = "SELECT * FROM [zenkoku$]  WHERE 都道府県 = '静岡県'"
  SelectRecord ThisWorkbook.Path & "\zenkoku.xlsx", strSQL, Me.Range("A2")

  Debug.Print Now
End Sub

すごくシンプルですね。さて、かかった時間は……6秒!!速い!!
あと、このプロシージャ'には"Application.ScreenUpdating = False"を入れていません。入れても入れなくても時間に変わりはありませんでした。

やっぱり速かった

やっぱり速かったです(笑)この差は馬鹿にできませんね。今回は検索条件もシンプルでしたが、さらに複雑な検索条件だとますます差は開くのではないのでしょうか。
積極的に使っていきたいところです。

今回は読み込みのスピードを見てみました。次回は書込みについて確認してみたいと思います。
hirocom777.hatenadiary.org

ExcelVBA-Excelファイルをデータベースとして使う連載です
その1:Excelファイルをデータベースファイルとして使えるよ!!
その2:データを追加してみよう!!
その3:SQLを使ってみる!!
その4:Excelファイルデータベースの良いところ/悪いところ
その5:Excelファイルデータベースの読込みスピードは?
その6:Excelファイルデータベースの書込みスピードは?
その7:書込みスピードをあげるには?
その8:クラスモジュール作ってみた!!
その9:クラスモジュールの解説

時間を作ろう!!

2020年も、もう十日が過ぎました。僕の周りの人たちも今年の目標を出している人達がいます。

僕にはそういう習慣が今までありませんでしたが、今年は少し目標を立ててみようと思います。どんな目標がいいのかちょっと考えてみたのですが……

よくある『今年こそ何々するぞ!!』『今年は何々を目指すぞ!!』的なものも素晴らしいのですが、残念ながら、今の僕にはそういう明確に成し遂げたいというものがないんですよね……
どんな目標がいいかな?

こんな考え方もあるかな

僕は山崎 元さんという経済評論家が大好きなのですが、彼の本にこう書いてあるのを読んだことがあります。

流れ星を見るときに願い事を3回唱えることができるとその願いはかなうといいますが、山崎さんは流れ星が流れたときに唱えるお願いをあらかじめ決めているそうです。それは……

『カネ、カネ、カネ!!』(笑)

これなら短い間に唱えることが可能。カネがあればかなえられる願いの幅も広がる。
細かいことは後回しにしてとりあえずカネ!!割り切りの良さ!!

そして、流れ星にすら準備をしておく用意周到さ!!見習いたいものです。
流石、経済評論家!!

それじゃあ僕は

お金と並んで無い無いといいがちなもの……

そう、時間!!

時間がないから願いがかなわないなんて事よくある!!

今年の目標は『時間を作る!!』にしたいと思います。
出来た時間でやることは、後から考える。とりあえず時間を確保する!!
出来た時間で新しいものに挑戦するもよし!!のんびりしてもよし!!

これに決めました!!でも、どうやったら時間を作れるかな?

時間を作る方法

で、時間を作る方法----考えてみました。

健康

 風邪ひいたり体調悪かったりしたときの時間ってマジ無駄!!
去年は風邪ひかなかったのでラッキーだったけど問題は二日酔いですね。
酔わない飲み方を研究しないと(飲まないという選択肢はない)

投資

 少し貯金があるので生かし方を見直そうと思います。自分以外にも働いていただきましょう!!
ただし、株価とにらめっこしたりする時間は無駄なので無し!!

無駄を捨てる

 いわゆる断捨離。身の回りはだいぶ片付いてきたけれどまだまだ物がいっぱい溢れている。ものを減らして身軽になりたい!!
身軽になれば、無駄がなくなって時間も増えるかも!!

人に任せる

 自分が苦手なことは、人に振れるようにする。苦手なことを無理してやるのは時間の無駄。お願いできるようにならなければ。まず信頼関係を築けないとだめだね。

予測する

 予測して行動できれば、無駄な時間を過ごさずに済むことあるよね。
必ず当たるわけではないから、外れた時の記録もつけて予測の仕方も研究しよう。

準備する

 予測できたら準備。どんな準備したらいいのかも研究必要か?準備して良かったこと、悪かったことも記録に残そうかな。

プログラミング

 これこれ、去年はこれが一番効果あった。引き続き取り組まねば!!

そして見極める

 以上の取り組みを行いながら、正しい方向を見極めたいと思います。

その他にも考えれば色々アイディア出てくるかも。

以上のような感じで行きたいと思います

 今年は毎日、これらの項目を注力していきたいと思います。気になることがあったらこのブログに書きますね!!

最後までお付き合いいただいてありがとうございました。