【VBA】Excelで、ひとつ上の可視セルの値を取得して貼り付けする
Excelでフィルタの適用状態に関わらず、任意のセルの(見た目上の)ひとつ上のセルの値を取得しようというもの。
元ネタはこちら
koroko.hatenablog.com
別の方法で、こちらでも。
infoment.hatenablog.com
両者とは別のアプローチで・・・
目次
抑えておくべき事
Range.Areasオブジェクト
MSのサイトより抜粋。
選択範囲内にある領域 (隣接しているセルのブロック) のコレクションです。
Areasコレクションの各メンバーは、 Range オブジェクトです。 Areasコレクションには、選択範囲内の各セルの不連続な連続した範囲のrangeオブジェクトが1つ含まれています。 選択範囲に領域が1つしか含まれていない場合、 Areasコレクションには、その選択範囲に対応するRangeオブジェクトが1つだけ含まれます。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.areas
Rangeオブジェクトは、Range("A1:C3") といった指定をした場合のように、単一の矩形領域の場合だけでなく
Rrange("A1:C3,D2:F4")とか、Unionメソッドにより複数の矩形領域を纏めてひとつのRangeオブジェクトとして扱ったりする場合もあります。
それらの1個以上の矩形領域をCollectionとして纏められたものが、Range.Areasオブジェクトです。
Itemプロパティ
Areasオブジェクトに含まれているひとつの要素(Rangeオブジェクト)を返すプロパティです。
構文
Areas.Item(Index)
パラメーター
名前 | 必須/オプション | データ型 | 説明 |
---|---|---|---|
Index | 必須 | Long | オブジェクトのインデックス番号を指定する。 |
省略記法
Areas(Index)
という記述でも同様に処理できます。
Countプロパティ
Areasコレクションに含まれるオブジェクトの数を返すプロパティです。
これにより前述のItemプロパティに指定できるインデックスの上限値がわかります。
構文
Areas.Count
実際の例
領域が分かれることで、Areas.Countが増え、それぞれの要素(Rangeオブジェクト)の情報(下の例ではAddress)を取得できています。
フィルターを使用している場合
フィルター適用前の状態が下図の要なデータで見てみる。
C列のデータを選択した状態
B列をDDで抽出した状態
SpecialCells(xlCellTypeVisible)を指定しないと、非表示のセルも含まれてしまう。
SpecialCells(xlCellTypeVisible)を指定することで、可視セルのみを対象としている。
また、非表示セルが間に入ると、Areasオブジェクト内のItemプロパティで取得する領域が別々になっている。
処理
考え方
- 上記のフィルター適用時の結果から、基準となるセルの上の全てのセルのうち、表示されているセルと、基準のセルをUnionで纏める。
- 基準となるセルは、Unionで纏めたRangeの一番下なので、Areas.Item(Areas.Count) にある。
- Areas.Item(Areas.Count).Rows.Count が1ならば、基準セルのひとつ上のセル(見た目ではなく実際の1行上のセル)は非表示なので、Areas.Item(Areas.Count-1) の一番下のセルが求めるセル。
- Areas.Item(Areas.Count).Rows.Count が2以上なら、基準セルの1行上のセルは表示されているので、基準セルの1行上のセルが求めるセル。
コード
取得データ貼り付け処理
データ取得処理
コードをコピーしたい方へ
いつもの通り、コードはダブルクリックすれば選択できますので・・・
使い方
pasetFromAboveCellsValueをイミディエイトウィンドウから呼ぶなり、
適当なボタンに登録するなり、使いやすいようにして下さい。
実行サンプル
最後に
今回は、前回のやつがあったから気がつけた。
ちょっと満足。
貼り付けだけが目的であれば、プロシージャを分けずに1つにした方が
無駄なコピーが無くせるので、スッキリするかも。
単一のセルだけでなく、複数のセルに対応したのと
データの途中の行でも実行可能なので、少しは使えるか?
【VBA】ExcelのUnionで纏められたRangeの Areas.Count と Areas.Item(N)
- 初めに
- Union メソッド
- Areas.Count と Areas.Item(N)
- 複数の Range がひとつの Areas.Item( N ) に纏められるケース(具体例)
- 対象範囲が同じでも結果が変わる例
- コード(抜粋)
- 最後に
初めに
以前の記事で、Union メソッドで纏められた Range へのアクセス方法を書きました。
z1000s.hatenablog.com
その中で、
今回の場合、
赤で囲まれた範囲がArea(1)
青で囲まれた範囲がArea(2)
となり、
r.Areas(1).Address(False, False)は
B2:D4
r.Areas(2).Address(False, False)は
F3:G4
が返ります。
Unionで指定した順番に返ってくるようです。(そうでないと困ります。)
と書いたのですが、このようにならない場合があることがわかりました。
Union メソッドで、複数のRange を纏めた時、
項目 | 値 |
---|---|
Areas.Item(N) | 指定した個々の Range |
Areas.Count | 指定した Range の数 |
となるものと思い込んでいたのですが、そうならない場合があるようです。
また、Union メソッドの実行方法によっては、対象となる範囲が同じでも、Areas.Countや、Areas.Item( N ).Address が変わる場合があることがある事がわかりました。
Union メソッド
まず、改めて Union メソッドについて。
docs.microsoft.com
機能
2 つ以上のセル範囲の集合を返します
構文
expression.Union (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)
パラメータ
名前 | 必須/オプション | データ型 | 説明 |
---|---|---|---|
Arg1 | 必須 | Range | |
Arg2 | 必須 | Range | |
Arg3~Arg30 | 省略可 | Range | 範囲を指定する |
戻り値
Range
使用例
基本的な使い方は、多分以下の2通りと思われます。
対象となる Range の数が固定の場合は、このような書き方ができます。
Set r = Union(r1, r2, r3, r4)
一方で、対象となる Range が可変の場合や、ループ処理により纏めたいような場合には、こちらの方法が使いやすいと思います。
Set r = Union(r1, r2) Set r = Union(r, r3) Set r = Union(r, r4)
他にも、このような書き方も出来ますが、多分ほとんど使われることはないかと思います。
Set r = Union(Union(Union(r1, r2), r3), r4)
3番目の書き方は、最初の書き方を冗長にしただけのように見えますが、実はそうではありません。
(この件については、後述)
Areas.Count と Areas.Item(N)
実際に返ってくる値
項目 | 値 |
---|---|
Areas.Item(N) | パラメータで指定した1個以上の Range の纏まり |
Areas.Count | 1以上 指定した Range の数以下 |
Areas.Item(N)
コレクションから単一のオブジェクトを返します。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.areas.item
MSのサイトには、下記のような記述があります。
コレクションから単一のRangeオブジェクトを取得するのにには、 Areas (index) を使用します。_引数 index_には、area インデックス番号を指定します。 インデックス番号は、領域が選択された順序に対応します。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.areas
基本的には、この通りになるようなのですが、例外があります。
Union メソッドに指定する Range が、以下のような場合、Areas.Item(N) は、複数の Range を纏めた範囲になります。つまり、パラメータで指定された複数の Range が、ひとつの Areas.Item(N) に纏まってしまう場合があります。
「任意の複数の Range の集まりが矩形となる」場合に、
ひとつの Areas.Item(N) として纏まるようです。
その場合、領域が選択された順序と一致しない場合があります。
Areas.Count
コレクションに含まれるオブジェクトの数を表す長整数型 (Long) の値を返します。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.areas.count
ここで注意すべきは、
”コレクションに含まれるオブジェクトの数”
であって、
”パラメータで渡した( Range )オブジェクトの数”
ではないということです。
前述の通り、パラメータで指定した複数の Range が、ひとつの Areas.Item に纏められる場合、Areas.Count は、それに伴い パラメータの Range 数より少なくなることになります。
Range("A1") から Range("A30") までの30個の Range を Uniono で纏めると
Set r = Union( Range("A1"), Range("A2"), ・・・, Range("A30") )
纏められた Range r は、
Areas.Count | 1 |
Areas.Item(1).Address | A1:A30 |
Areas.Address | A1:A30 |
となります。
複数の Range がひとつの Areas.Item( N ) に纏められるケース(具体例)
Union( r1, r2, r3 )
の記述で、纏めてみた結果が下の図です。
下図のA列にアドレス単独で表示されているものは、Union メソッドに渡した Range であり、Union メソッドには、上から順に渡しています。
なお、下図の中の Areas.Address: の後ろに表示されている [ ] で囲まれたそれぞれの部分が Areas.Item( N ) .Address です。
隣接する2個以上の Range が矩形となる場合
Unionに指定するRangeの順番の影響は見られません。
2個以上の Range の一部が重なるが、全体が矩形となる場合
重なっている部分を含め、全体がひとつのAreas.Item( N ) となります。
Range A に、Range B が含まれる場合
サイズが、基準となる(大きい方の)Range以下の Range は無視されています。
対象範囲が同じでも結果が変わる例
記述の違い(実行方法の違い)によるもの
前述の通り、Union メソッドの書き方は、思いついただけで3パターンあります。
同じ Range を、同じ順番でメソッドに渡しても、返ってくる結果が異なる場合を見つけました。
Range r1、r2、r3 を以下の範囲とします。
変数 | Range |
---|---|
r1 | Range("D4:E5") |
r2 | Range("D2:E3") |
r3 | Range("B2:C3") |
これらの範囲を使用して、以下の式が返す結果を比較してみます。
No. | 式 |
---|---|
1 | Set r = Union(r1, r2, r3) |
2 | Set r = Union(r1, r2) Set r = Union(r, r3) |
3 | Set r = Union(Union(r1, r2), r3) |
No.1 のみが、他と結果が異なっているのがわかります。
No.1 が、r2 と r3 が纏まっているのに対し、
No.2とNo.3は、r1 と r2 が纏まっています。
No. | 式 | Areas .Count | Areas .Item(1) .Address | Areas .Item(2) .Address |
---|---|---|---|---|
1 | Set r = Union(r1, r2, r3) | 2 | D4:E5 | B2:E3 |
2 | Set r = Union(r1, r2)Set r = Union(r, r3) | 2 | D2:E5 | B2:C3 |
3 | Set r = Union(Union(r1, r2), r3) | 2 | D2:E5 | B2:C3 |
No.2、No.3では、順次Unionメソッドで Range を纏めているため、その都度 Areas.Item( N ) が更新されていくため、このような結果になると考えられます。
Union メソッドに渡すパラメータの順番による違い
変数 | Range |
---|---|
r1 | Range("B2:C3") |
r2 | Range("D2:E3") |
r3 | Range("B4:C5") |
として、
Union( Arg1, Arg2, Arg3 ) と呼び出してみました。
No. | Arg1 | Arg2 | Arg3 |
---|---|---|---|
1 | r1 | r2 | r3 |
2 | r3 | r2 | r1 |
No.1 では、縦方向に纏まっていますが、
No. 2 では、横方向に纏まっています。
コード(抜粋)
コードが欲しい方は、コードをダブルクリックすると、少しだけいいことがあるかもしれません
データ設定、描画処理呼び出し
描画処理
その他
最後に
処理方法によって結果が変わる場合があるのは分かった。
キーワードは、矩形
しかし、Union( r1, r2, ・・・, rn ) の場合の規則性が分からん。
Areas.Item( N ).Address が変わって困るような処理は今の所ないから、
今回はここまでで勘弁してやろう・・・ orz
m(_ _)m
【VBA】偶数判定、奇数判定
主に偶数判定のための私的メモ。
判定 | 式 | 結果 | |
---|---|---|---|
偶数の場合 | 奇数の場合 | ||
偶数判定 | ( N Eqv 0 ) And 1 | 0 | 1 |
( N And 1 ) = 0 | True | False | |
奇数判定 | N And 1 | 0 | 1 |
( N And 1 ) = 1 | True | False |
Eqv(XOr の逆パターン)
値1 | 値2 | 値1 Eqv 値2 |
---|---|---|
False | False | True |
False | True | False |
True | False | False |
True | True | True |
Bit版
値1 | 値2 | 値1 Eqv 値2 |
---|---|---|
0 | 0 | 1 |
0 | 1 | 0 |
1 | 0 | 0 |
1 | 1 | 1 |
整数版
値1 | 値2 | 値1 Eqv 値2 |
---|---|---|
0 | 0 | -1 (&HFFFF) |
0 | 1 | -2 (&HFFFE) |
0 | 2 | -3 (&HFFFD) |
1 | 0 | -2 (&HFFFE) |
2 | 0 | -3 (&HFFFD) |
&HFFFF | 0 | 0 |
&HFFFF | 1 | 1 |
&HFFFF | 2 | 2 |
&HFFFF | 3 | 3 |
&HFFFE | 0 | 0 |
&HFFFE | 1 | 0 |
&HFFFE | 2 | 3 |
&HFFFE | 3 | 2 |
【VBA】同じデータのセルを結合(Union未使用版)
下記のサイトで、同じデータのセルを結合するということで、Unionを使ってRangeをまとめて最後にMergeという処理を行っていました。
これはこれで良いのですが、非連続データならともかく、連続データである場合には、
個人的には、「何度もUnionせずに、範囲(開始行と終了行)を取得してMerge」とする方が好みなので、作ってみました。
Do Until の2重ループにして、以下のようなソースに。
(縦方向のマージ処理のみです)
外側のループで、基準となる行を進めていって、
内側のループで、基準となる値が終わる行を取得しています。
Public Sub doMerge() Dim r As Range Set r = Sheet1.Range("A1").CurrentRegion Set r = r.Resize(r.Rows.Count - 1).Offset(1) Call MergeSameValueCellsV(r) Debug.Print "Done." End Sub Private Sub MergeSameValueCellsV(ByRef rTarget As Range) Const TARGGET_COL As String = "A" Dim ws As Worksheet Dim lCurrentRow As Long Dim lNextRow As Long Dim lEndRow As Long Dim lMergeBeginRow As Long Dim lMergeEndRow As Long Dim vBaseValue As Variant If rTarget Is Nothing Then Exit Sub ElseIf rTarget.Rows.Count = 1 Then Exit Sub End If Application.DisplayAlerts = False lCurrentRow = rTarget.Row lEndRow = rTarget.Row + rTarget.Rows.Count - 1 Set ws = rTarget.Parent Do Until lCurrentRow > lEndRow lMergeBeginRow = lCurrentRow lMergeEndRow = lMergeBeginRow vBaseValue = ws.Range(TARGGET_COL & CStr(lCurrentRow)).Value lNextRow = lMergeBeginRow + 1 Do Until vBaseValue <> ws.Range("A" & CStr(lNextRow)).Value lMergeEndRow = lNextRow lNextRow = lNextRow + 1 Loop If lMergeEndRow > lMergeBeginRow Then 'マージ開始行と終了行が違っていたら、マージ処理 ws.Range(TARGGET_COL & CStr(lMergeBeginRow) & ":" & TARGGET_COL & CStr(lMergeEndRow)).Merge '次の処理は、マージした次の行から lCurrentRow = lCurrentRow + lMergeEndRow - lMergeBeginRow + 1 Else 'マージしないので次の行 lCurrentRow = lCurrentRow + 1 End If Loop Application.DisplayAlerts = True End Sub
今日は(も?)、人のネタを元にする日・・・
【VBA】種目別に自動採番(別解)
コロ子さん(id:SNegishi)のところで、自動採番処理をやっているのを見て、ちょっと気がついた点をコメントしました。
すぐに修正版がアップされて「おぉ~、仕事はえぇなぁ」と感心していました。
koroko.hatenablog.com
そんな中で、
「Worksheet_SelectionChangeイベントで変更前の値を保持しておいく」のところを静的変数Staticを使おうとしたけど、モジュール変数として使う事がでなかった。
「プローシシャーの外では使えません」のエラーメッセージが出た。
結局上手くできず、いつもの仮置き方式。
仮置き方式でないなら、どのように作るものなのでしょうか・・・?
多分、Worksheet_SelectionChangeの中にStatic変数を置いちゃったんでしょうね。
そのままでは、他のプロシージャからは、そのStatic変数にはアクセス出来ないので、
もう少し工夫が必要ですね。
私の場合、Static変数はほとんど使うことがなくて、大抵の場合、モジュールレベルのPrivate変数で処理しちゃいます。
下記のソースの vPrevValue_ がそれに当たります。
そいつに、Worksheet_SelectionChangeイベントで、必要な条件に一致した時に値を突っ込みます。
あと、Application.EnableEvents で処理中のイベントを抑止してあげないと、不要な処理が走るようです。
あと、先頭部分で処理不要なら、即 Exit Sub するように
以下の理由により、変えてあります。
- それ以降、何も処理が無いのが明確になる
- If でくくった場合、「Ifの後に何か処理があるかもしれない」ので確認が必要。プロシージャが長いほど、スクロールが面倒くさい(私の場合)
- If によるインデントが不要になる
Private変数を使って処理したソースがこれ
Private vPrevValue_ As Variant Private Sub Worksheet_Change(ByVal Target As Range) Dim buf As Range If Target.Column <> 1 Then '1列目(A列)以外でイベントが発生した場合は、処理を抜ける Exit Sub ElseIf vPrevValue_ = Target.Value Then 'データが変わっていなければ、処理を抜ける Exit Sub End If Application.ScreenUpdating = False Application.EnableEvents = False '初期化 Range("B" & Target.Row).Value = "" 'A列でフィルタ Range("A1").AutoFilter Field:=1, Criteria1:=Range("A" & Target.Row).Value 'アクティブセル領域の可視範囲を取得 Set buf = Range("A1").CurrentRegion.Columns(2).SpecialCells(xlCellTypeVisible) '最大値+1を取得 Range("B" & Target.Row).Value = Application.WorksheetFunction.Max(buf) + 1 'フィルター条件解除 ActiveSheet.ShowAllData Application.EnableEvents = True Application.ScreenUpdating = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) '複数セルを選択している中に、未入力セルが含まれていても処理できるように 'Target.Count = 1 'は含めない ' If Target.Column = 1 And Target.Count = 1 Then If Target.Column = 1 Then '複数のセルが選択されている場合、Target.Valueを代入すると、 'vPrevValueは配列になるのでActiveCellを指定する ' vPrevValue_ = Target.Value vPrevValue_ = ActiveCell.Value End If End Sub
【VBA】特定の範囲内の任意の整数をインクリメント、デクリメントして次の値を求める
条件
- 任意の3つの整数において、以下の条件を満たす事(負の値があっても可)
- 最小値 <= 現在値 <= 最大値
- 範囲の最大値をインクリメントした場合、範囲の最小値を返す事。
- 範囲の最小値をデクリメントした場合、範囲の最大値を返す事。
求める式
インクリメント、デクリメントというからには、普通に考えれば増減値は1なので、以下のようになる。
要素数 = 最大値 - 最小値 + 1 'インクリメントする場合 次の値 = ( ( 現在値 + 1 - 最小値 ) Mod 要素数 ) + 最小値 'デクリメントする場合 次の値 = ( ( 現在値 + ( 要素数 - 1 ) - 最小値 ) Mod 要素数 ) + 最小値
補足
インクリメントの場合
現在値に1を加算し、要素数の剰余を利用する。
範囲の最小値が0でない場合、最小値を0に補正して剰余を求めた後、補正した分を戻す。
デクリメントの場合
インクリメントでは、要素数に1を加算したが、デクリメントの場合は、
(要素数 - 1 )を加算し、同様に計算する。
増減値が2以上の場合、前述の式の1の部分を変えれば良い。
要素数 = 最大値 - 最小値 + 1 'インクリメントする場合 次の値 = ( ( 現在値 + 加算する値 - 最小値 ) Mod 要素数 ) + 最小値 'デクリメントする場合 次の値 = ( ( 現在値 + ( 要素数 - 減算する値 ) - 最小値 ) Mod 要素数 ) + 最小値
ただし、以下の条件を満たす必要がある。
加算する値 >= 0
減算する値 >= 0 かつ 減算する値 <= 要素数
コード
Private Const ERROR_INVALID_PARAMETER As Long = 87 Public Function incrementBetweenNM(ByVal lCurrentValue As Long, _ ByVal lMinValue As Long, _ ByVal lMaxValue As Long, _ Optional ByVal lOffsetValue As Long = 1) As Long Dim lElements As Long If lMinValue > lMaxValue Then Err.Raise ERROR_INVALID_PARAMETER ElseIf lMaxValue < lCurrentValue Then Err.Raise ERROR_INVALID_PARAMETER ElseIf lMinValue > lCurrentValue Then Err.Raise ERROR_INVALID_PARAMETER End If If lOffsetValue < 0 Then Err.Raise ERROR_INVALID_PARAMETER ElseIf lOffsetValue > lMaxValue - lMinValue + 1 Then Err.Raise ERROR_INVALID_PARAMETER End If lElements = lMaxValue - lMinValue + 1 incrementBetweenNM = ((lCurrentValue + lOffsetValue - lMinValue) Mod lElements) + lMinValue End Function Public Function decrementBetweenNM(ByVal lCurrentValue As Long, _ ByVal lMinValue As Long, _ ByVal lMaxValue As Long, _ Optional ByVal lOffsetValue As Long = 1) As Long Dim lElements As Long If lMinValue > lMaxValue Then Err.Raise ERROR_INVALID_PARAMETER ElseIf lMaxValue < lCurrentValue Then Err.Raise ERROR_INVALID_PARAMETER ElseIf lMinValue > lCurrentValue Then Err.Raise ERROR_INVALID_PARAMETER End If If lOffsetValue < 0 Then Err.Raise ERROR_INVALID_PARAMETER ElseIf lOffsetValue > lMaxValue - lMinValue + 1 Then Err.Raise ERROR_INVALID_PARAMETER End If lElements = lMaxValue - lMinValue + 1 decrementBetweenNM = ((lCurrentValue + (lElements - lOffsetValue) - lMinValue) Mod lElements) + lMinValue End Function
実行例
テストコード
Public Sub getNext() Dim l As Long Dim u As Long Dim i As Long l = -3 u = 2 Debug.Print "最小値:" & l Debug.Print "最大値:" & u Debug.Print "" Debug.Print "現在値", "次の値", "前の値" For i = l To u Debug.Print i, incrementBetweenNM(i, l, u), decrementBetweenNM(i, l, u) Next End Sub
実行結果
call getNext 最小値:-3 最大値:2 現在値 次の値 前の値 -3 -2 2 -2 -1 -3 -1 0 -2 0 1 -1 1 2 0 2 -3 1
おまけのテスト その1
call getNext 最小値:1 最大値:1 現在値 次の値 前の値 1 1 1
最大値 = 最小値 だと、次の値も前の値も、みんな同じ・・・
あたりまえか。
おまけのテスト その2
Debug.Print i, incrementBetweenNM(i, l, u, 2), decrementBetweenNM(i, l, u, 3)
として、加算、減算する値を変えてみると
call getNext 最小値:-3 最大値:2 現在値 次の値 前の値 -3 -1 0 -2 0 1 -1 1 2 0 2 -3 1 -3 -2 2 -2 -1
【VBA】ADOを使ったテキストファイル(CSV)の読み込みについて調べてみた。ついでに、Line Input と速度を比べてみた。
タイトルに「比べてみた」と書いてあるけど、ADOでの処理や設定についてがメインだったりするので、結果だけ知りたい場合は、ずっと下の結果へ・・・
処理の流れ
- 事前準備
- Schema.ini ファイルの作成
- Connection オブジェクトの生成
- プロバイダの指定
- 処理対象ファイルのあるフォルダの指定
- プロパティの指定
- Open
- Recordset オブジェクトの生成
- Cursorの指定
- 排他処理(Lock)の指定
- SQLの指定
- Open
- データの取得
- データ読み込み
- 後処理
- Recordset オブジェクト、Connection オブジェクトを閉じて、開放
ADO
Schema.ini ファイル (テキスト ファイル ドライバー)
Schema.ini ファイルとは
Scema.ini というテーブル情報の設定ファイルを併用することで、テキストファイルの定義情報を指定し、ADOでその定義情報に従った形で読み込むとが可能となる。
Schema.ini については、以下参照。
docs.microsoft.com
Schema.ini の設定については、以下参照。
docs.microsoft.com
INIファイルの構成、書き方
INIファイルは、
- セクション
- キー
- 値
から構成され、以下のように構成されている。
[セクション1] キー11=値11 キー12=値12 キー13=値13 ;コメント(必要であれば) ・ ・ ・ [セクション2] キー21=値21 キー22=値22 キー23=値23 ・ ・ ・
Schema.ini では、
セクション | ファイル名 |
---|---|
キー | 設定項目名 |
値 | 設定項目の設定値 |
を書く。
設定項目
キーと値については、以下のような項目を設定することが出来る。
項目 | キー | 値 | 備考 |
---|---|---|---|
文字セット | CharacterSet | 932 | Shift-JIS |
65001 | UTF-8 | ||
ファイル書式 | Format | CSVDelimited | CSV |
TabDelimited | TSV(Tab区切り) | ||
Delimited(custom character) | 任意の文字での区切り | ||
FixedLength | 固定長 | ||
ヘッダ行の有無 | ColNameHeader | True | 先頭行をフィールド名として扱う |
False | 先頭行をデータとして扱う | ||
フィールドのデータ型を決定するためスキャンするレコード数 | MaxScanRows | 0 | 全レコードをスキャンする |
n | 先頭からnレコード目までに格納されているレコードをスキャン | ||
フィールド定義 | Coln | フィールド名 | |
データ型 | 下表参照 | ||
長さ | 固定長の場合 Width nn |
Coln=フィールド名 データ型 [Width 長さ]
n:列番号(Col1、Col2・・・)
データ型
以下の型を指定できます。
種別 | 型 | 備考 |
---|---|---|
Microsoft Jet のデータ型 | Bit | |
Byte | ||
Short | ||
Long | ||
Currency | ||
Single | ||
Double | ||
DateTime | ||
Text | ||
Memo | ||
ODBC のデータ型 | Char | Text |
Float | Double | |
Integer | Long Integer | |
LongChar | Blob | |
Date |
MaxScanRowsについて
あくまでもフィールドのデータ型を決定するためにスキャンするレコード数であって、レコードセットに取得するレコード数の制限ではないようです。
デフォルト値は、25だそうです。
dobon.net
記入例
[CSV100000.csv] CharacterSet=932 Format=CSVDelimited ColNameHeader=True Col1=F1 Text Col2=F2 Text Col3=F3 Text Col4=F4 Text Col5=F5 Text Col6=F6 Text Col7=F7 Text Col8=F8 Text Col9=F9 Text Col10=F10 Text [CSV1000000_UTF8.csv] CharacterSet=65001 Format=CSVDelimited ColNameHeader=True Col1=F1 Text Col2=F2 Text Col3=F3 Text Col4=F4 Text Col5=F5 Text Col6=F6 Text Col7=F7 Text Col8=F8 Text Col9=F9 Text Col10=F10 Text [Sample1.csv] CharacterSet=932 Format=CSVDelimited ColNameHeader=True Col1=F1 Short Col2=F2 Long Col3=F3 Text [Sample2.ssv] CharacterSet=932 ;スペース区切り "("と")"の間に、半角スペースがある Format=Delimited( ) ColNameHeader=False Col1=ID Short Col2=品名 Text Width 20 Col3=金額 Integer
余談ですが、スペース区切りのフォーマットは、SSV と言われることもあるそうです。
TSVは知っていたけど、SSVとかDSVとかは知らなかった。
類似したフォーマットとして、タブで区切られた tab-separated values (TSV)や、欧文間隔 (いわゆる半角スペース) で区切られた space-separated values (SSV) などがあり、これらをまとめて character-separated values (CSV)、delimiter-separated values (DSV) とも呼ばれることも多い。
Comma-Separated Values - Wikipedia
Connection オブジェクト
Connection オブジェクトについては、以下参照。
docs.microsoft.com
ConnectionString
対象ファイルに接続するための文字列を設定する必要があります。
以下の項目を設定します。
項目 | 値 | 備考 |
---|---|---|
Provider | Microsoft.ACE.OLEDB.12.0 Microsoft.Jet.OLEDB.4.0 |
Office 2007以降は、ACE それより前は、Jet |
Data Source | 対象ファイルのあるフォルダパス | ファイルパスではない |
Extended Properties | Text | データベースの種類が、テキストファイル |
HDR=Yes | 1行目をヘッダとして扱う場合 Yesを指定する 1行目からデータの場合は、No を指定する |
|
FMT=Delimited | CSVファイルの場合は、Delimitedを指定 |
各値の間には、";" を挟む。
Extended Properties の値は、「"」で括る。
記入例
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Datas\;Extended Properties="Text;HDR=Yes;FMT=Delimited"
ConnectionString の HDR と Schema.ini の ColNameHeader
どちらも先頭行に対する扱いの指定ですが、双方の指定が異なった場合、Schema.ini の設定が優先されるようです。
結果としては、schema.iniファイルにフィールド定義情報がある場合は、そちらが優先される。
https://antonsan.net/vt/excel-db/heading-4/page-046
HDR=NO の設定がある場合、先頭行もデータとして取り込まれる。
Command オブジェクト
Command オブジェクトについては、以下を参照。
docs.microsoft.com
以下の項目を設定します。
プロパティ | 設定する値 | 備考 |
---|---|---|
ActiveConnection | Connection オブジェクト | |
CommandType | adCmdText | 引数をコマンド文字列として評価 |
CommandText | データを取得するためのSQL | テーブル名部分をファイル名とし、 [ ] で括る ファイル名と[ ] の間にスペースを含めない 例:SELECT * FROM [Sample.csv] |
SQL
ファイルから読み込むデータの指定を行う。
無条件に、全データを読み込む場合
SELECT * FROM [ファイル名]
特定のフィールドのみを指定して読み込む場合
SELECT フィールド名1,フィールド名2,・・・ FROM [ファイル名]
フィールド名nは、CSVファイルのヘッダ行に記載されている物を指定する。
HDR=No; を指定して読み込んだ場合には、ヘッダ行がないので、その場合には、
F1、F2 のように "F" + 何列目のデータかの番号 を指定する。
指定する順番は、ファイルの列の順番と一致していなくても構わない。(後ろの列を先に指定しても良い)
SELECT F1,F3 FROM [ファイル名] SELECT F5,F2 FROM [ファイル名]
SQLでは、いろいろな事が出来るので、興味がある方は、以下のようなキーワードを例にして調べてみて下さい。
抽出条件の指定:WHERE
並べ替え:ORDER BY
集計:GROUP BY
複数ファイルの結合:JOIN
サブクエリー
ワイルドカード
UNION
カーソル
カーソルについては、以下を参照。
docs.microsoft.com
CursorLocation
クライアント側カーソルの明確な利点の 1 つは、反応が速いことです。結果セットがクライアント コンピューターにダウンロードされた後は、非常に迅速に行を参照できます。
カーソル位置の重要性 | Microsoft Docs
ファイルサイズが大きいCSVファイルの場合、上記のダウンロード相当部分に時間がかかり、結果的にサーバー側カーソルの方が速い場合もあります。
実行環境に応じたカーソル位置を選択して下さい。
選択肢は以下の通りです。
CursorLocationEnum
定数 | 値 | 備考 |
---|---|---|
adUseServer | 2 | サーバー側 |
adUseClient | 3 | クライアント側 |
CursorType
カーソルの種類については、以下を参照。
docs.microsoft.com
選択肢は以下の通りです。
CursorTypeEnum
定数 | 値 | 備考 |
---|---|---|
adOpenForwardOnly | 0 | 前方スクロールタイプ 既定値 |
adOpenKeyset | 1 | キーセットカーソル |
adOpenDynamic | 2 | 動的カーソル |
adOpenStatic | 3 | 静的カーソル |
通常の読み込み作業(先頭から最後まで1回だけ読み込む)の場合には、adOpenForwardOnly で問題ないと思います。
Recordset オブジェクト
Recordset オブジェクトについては、以下参照。
docs.microsoft.com
カーソル情報(前述)及びレコードに適用されるロックの種類を設定します。
- CursorLocation
- CursorType
- LockType
LockType プロパティ
LockType プロパティについては、以下参照。
docs.microsoft.com
LockTypeEnum
定数 | 値 | 備考 |
---|---|---|
adLockReadOnly | 1 | 読み取り専用 既定値 |
adLockPessimistic | 2 | レコードごとの排他的ロック |
adLockOptimistic | 3 | レコードごとの共有的ロック |
adLockBatchOptimistic | 4 | 共有的バッチ更新 |
ローカルファイルの読み込み作業の場合には、adLockReadOnly で問題ないと思います。
RecordCount
RecordsetをOpenした後に、RecordCountプロパティにより、読み込んだレコードの件数を取得できる場合があります。
CursorLocation プロパティとCursorType プロパティの組み合わせにより、以下のようになります。
CursorLocation プロパティ | CursorType プロパティ | RecordCount 値 |
---|---|---|
adUseServer | adOpenForwardOnly | -1 |
adOpenKeyset | レコード数 | |
adOpenDynamic | -1 | |
adOpenStatic | レコード数 | adUseClient | adOpenForwardOnly | レコード数 |
adOpenKeyset | ||
adOpenDynamic | ||
adOpenStatic |
ソース
以下のコードを実行するには、予め、
Microsoft ActiveX Data Objects 6.X Library
を参照設定する必要があります。
Private Const TARGET_FOLDER As String = "C:\Datas\" Private Const TARGET_NAME As String = "CSV10.csv" Public Sub readByAdo() Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim lRecords As Long Dim i As Long Dim sgStart As Single Dim sgStop As Single sgStart = Timer On Error GoTo ERR_EXIT Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & TARGET_FOLDER & ";" _ & "Extended Properties=""Text;" _ & "HDR=Yes;" _ & "FMT=Delimited""" Set cmd = New ADODB.Command Set cmd.ActiveConnection = cn cmd.CommandType = adCmdText 'データを取得するためのSQL cmd.CommandText = "SELECT * FROM [" & TARGET_NAME & "]" Set rs = New ADODB.Recordset 'カーソルとロックの設定 rs.CursorLocation = adUseServer rs.CursorType = adOpenForwardOnly rs.LockType = adLockReadOnly rs.Open cmd 'rs.RecordCountを取得するには '1.adUseClientを使用する ' (CursorTypはどれを指定しても可) 'または '2.adUseServerを使用し、かつ ' CursorTypeを次のいずれかにする ' adOpenKeyset ' adOpenStatic ' lRecords = rs.RecordCount lRecords = 10 Do Until rs.EOF For i = 0 To rs.Fields.Count - 1 Debug.Print rs.Fields(i).Value & " "; Next i Debug.Print "" rs.MoveNext Loop ERR_EXIT: If Err.Number <> 0 Then 'エラーがあればログ出力 Debug.Print "[" & Err.Source & "]" & "[" & CStr(Err.Number) & "] " & Err.Description End If If Not rs Is Nothing Then If rs.State = adStateOpen Then 'レコードセットのインスタンスが生成されていて、かつ開いていたら、閉じる rs.Close End If Set rs = Nothing End If If Not cmd Is Nothing Then 'アクティブコネクションへの参照を破棄する Set cmd.ActiveConnection = Nothing Set cmd = Nothing End If If Not cn Is Nothing Then If cn.State = adStateOpen Then 'コネクションのインスタンスが生成されていて、かつ接続済みならば、閉じる cn.Close End If Set cn = Nothing End If sgStop = Timer Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]" End Sub
サンプルデータ
ヘッダ行あり。
10列×10レコード。
F1,F2,F3,F4,F5,F6,F7,F8,F9,F10
AA0000001,AB0000001,AC0000001,AD0000001,AE0000001,AF0000001,AG0000001,AH0000001,AI0000001,AJ0000001
AA0000002,AB0000002,AC0000002,AD0000002,AE0000002,AF0000002,AG0000002,AH0000002,AI0000002,AJ0000002
AA0000003,AB0000003,AC0000003,AD0000003,AE0000003,AF0000003,AG0000003,AH0000003,AI0000003,AJ0000003
AA0000004,AB0000004,AC0000004,AD0000004,AE0000004,AF0000004,AG0000004,AH0000004,AI0000004,AJ0000004
AA0000005,AB0000005,AC0000005,AD0000005,AE0000005,AF0000005,AG0000005,AH0000005,AI0000005,AJ0000005
AA0000006,AB0000006,AC0000006,AD0000006,AE0000006,AF0000006,AG0000006,AH0000006,AI0000006,AJ0000006
AA0000007,AB0000007,AC0000007,AD0000007,AE0000007,AF0000007,AG0000007,AH0000007,AI0000007,AJ0000007
AA0000008,AB0000008,AC0000008,AD0000008,AE0000008,AF0000008,AG0000008,AH0000008,AI0000008,AJ0000008
AA0000009,AB0000009,AC0000009,AD0000009,AE0000009,AF0000009,AG0000009,AH0000009,AI0000009,AJ0000009
AA0000010,AB0000010,AC0000010,AD0000010,AE0000010,AF0000010,AG0000010,AH0000010,AI0000010,AJ0000010
実行結果
call readByAdo
AA0000001 AB0000001 AC0000001 AD0000001 AE0000001 AF0000001 AG0000001 AH0000001 AI0000001 AJ0000001
AA0000002 AB0000002 AC0000002 AD0000002 AE0000002 AF0000002 AG0000002 AH0000002 AI0000002 AJ0000002
AA0000003 AB0000003 AC0000003 AD0000003 AE0000003 AF0000003 AG0000003 AH0000003 AI0000003 AJ0000003
AA0000004 AB0000004 AC0000004 AD0000004 AE0000004 AF0000004 AG0000004 AH0000004 AI0000004 AJ0000004
AA0000005 AB0000005 AC0000005 AD0000005 AE0000005 AF0000005 AG0000005 AH0000005 AI0000005 AJ0000005
AA0000006 AB0000006 AC0000006 AD0000006 AE0000006 AF0000006 AG0000006 AH0000006 AI0000006 AJ0000006
AA0000007 AB0000007 AC0000007 AD0000007 AE0000007 AF0000007 AG0000007 AH0000007 AI0000007 AJ0000007
AA0000008 AB0000008 AC0000008 AD0000008 AE0000008 AF0000008 AG0000008 AH0000008 AI0000008 AJ0000008
AA0000009 AB0000009 AC0000009 AD0000009 AE0000009 AF0000009 AG0000009 AH0000009 AI0000009 AJ0000009
AA0000010 AB0000010 AC0000010 AD0000010 AE0000010 AF0000010 AG0000010 AH0000010 AI0000010 AJ0000010
Done. [ 0.31 sec.][ 10 records.]
参考データ
Open + Line Input との速度比較
Open ステートメント + Line Input ステートメントを使用したコードと速度を比較してみた。
使用したコード
Public Sub readByOpen() Dim iFileNo As Integer Dim sLine As String Dim vLineItems As Variant Dim lRecords As Long Dim vRecords() As Variant Dim sgStart As Single Dim sgStop As Single sgStart = Timer lRecords = 0 ' ReDim vRecords(lRecords) ReDim vRecords(9) iFileNo = FreeFile Open TARGET_FOLDER & TARGET_NAME For Input As iFileNo 'ヘッダ行読み捨て Line Input #iFileNo, sLine Do Until EOF(iFileNo) Line Input #iFileNo, sLine vLineItems = Split(sLine, ",") ' ReDim Preserve vRecords(lRecords) vRecords(lRecords) = vLineItems lRecords = lRecords + 1 Loop Close iFileNo sgStop = Timer Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]" End Sub
結果
単位は秒。
各レコード、各フィールドの値のDebug.Printなし。
レコード数が少ない場合は、Open + Line Input の方が速い。
100,000件以上では、ADOの方が速く、件数が増えるほど差が大きくなっている。
レコード数 | readByAdo | readByOpen Preserveあり | readByOpen Preserveなし |
---|---|---|---|
10 | 0.28 | 0.00 | 0.00 |
10,000 | 0.33 | 0.11 | 0.11 |
100,000 | 0.72 | 1.09 | 0.92 |
500,000 | 2.43 | 10.77 | 4.53 |
1,000,000 | 4.54 | 33.50 | 9.09 |
カーソル、LockTypeによる影響
今回は、省略しましたが、
- CursorLocation
- CursorType
- LockType
が変わると、処理時間も変わるようです。
機会があれば、まとめて掲載するかも知れません。
類似記事
2019/10/26 追記
UTF-8 の読み込み処理として、ADODB.Recordset と ADODB.Stream の比較記事があります。
z1000s.hatenablog.com
z1000s.hatenablog.com