Excel VBA

2023年8月30日 (水)

[Excel VBA]CSVファイルを高速で読み込む

QueryTablesオブジェクトのAddメソッドを使う。まずは動作確認のための巨大なCSVファイル(qt.csv)を作成する。PowerShellで以下のコマンドを実行すると100万行からなるCSVファイル(ファイルサイズ11,000,000バイト(約10.5メガバイト))が作成される。

PS > "A,B,C,1,2`r`n" * 1000000 | Out-File -Encoding default -NoNewline qt.csv
PS > Get-ChildItem .\qt.csv
ディレクトリ: ○○○
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 2023/07/01 22:33 11000000 qt.csv
PS > Get-Content -path .\qt.csv -TotalCount 3
A,B,C,1,2
A,B,C,1,2
A,B,C,1,2

以下のプログラムを実行する。ワークシートSheet1の中身を強制的にすべて削除し、A1セルからファイルの中身を強制的に入力するので注意。

Dim filepath As String
Dim ws As Worksheet
Dim qt As QueryTable
Dim dtstart As Date
Dim dtend As Date
filepath = Application.ThisWorkbook.Path & "\qt.csv"
Set ws = Worksheets("Sheet1")
ws.Cells.Clear
dtstart = Time
Debug.Print "開始時刻: " & dtstart
Set qt = ws.QueryTables.Add(Connection:="TEXT;" & filepath, Destination:=ws.Range("A1"))
With qt
.TextFileCommaDelimiter = True
.TextFileParseType = xlDelimited
.TextFilePlatform = 932
.RefreshStyle = xlOverwriteCells
.Refresh
.Delete
End With
dtend = Time
Debug.Print "終了時刻: " & dtend
Debug.Print "処理時間: " & DateDiff("s", dtstart, dtend) & "秒"

イミディエイトの出力

開始時刻: 23:26:05
終了時刻: 23:26:11
処理時間: 6秒

Qt

10.5メガバイトのCSVファイルを約6秒で読み込んで各セルに入力していることが分かる。

2022年11月10日 (木)

[ExcelVBA]文字コードがutf-8のテキストファイルを出力する

ADOのStreamオブジェクトを使う。以下のようなワークシートを開いた状態で、以下のSubプロシージャを実行する。

Streamwritetest

Sub StreamWriteTest()
Dim i As Long
Dim s As String
Dim adodbsm As Object
Set adodbsm = CreateObject("ADODB.Stream")
adodbsm.Charset = "utf-8"
adodbsm.Open
For i = 1 To 6
s = ActiveWorkbook.ActiveSheet.Cells(i, 1).Value
adodbsm.WriteText s, 1
Next i
adodbsm.SaveToFile ActiveWorkbook.Path & "\stream.txt", 2
adodbsm.Close
Set adodbsm = Nothing
End Sub

そのブックが置かれたフォルダーに、ファイルstream.txtが作成されて、メモ帳で開くと、中身は以下のようになっているはず。

ABC
123
あいう
辰𠮷𠀋一郎
𩸽(ホッケ)を食べる
𩹉(トビウオ)を捕まえる

2021年9月17日 (金)

[Excel VBA]カレントディレクトリを取得する

CurDir関数を使う。戻り値のデータ型は文字列。以下は、起動した直後のExcelで実行した例。なお、これを実行したパソコンはDドライブがある。

Debug.Print "カレントディレクトリ→" & CurDir()
Debug.Print "カレントディレクトリ→" & CurDir("C")
Debug.Print "カレントディレクトリ→" & CurDir("D")

カレントディレクトリ→C:\Users\○○\Documents
カレントディレクトリ→C:\Users\○○\Documents
カレントディレクトリ→D:\

Excelは特に指定をしないと、起動直後はC:\Users\○○\Documents(○○はアカウント名)がカレントディレクトリになる。

引数に設定していないドライブ名を指定すると、エラー「実行時エラー '68': デバイスが準備されていません。」が発生して、プログラムが停止するので注意。

2020年3月22日 (日)

[Excel VBA]FileSystemObjectを使えるようにする

  1. Microsoft Visual Basic for Applicationsウィンドウのメニュー「ツール」→「参照設定」。
  2. 「Microsoft Scripting Runtime」を探す。初期状態ではチェックされていないはず。
  3. これにチェックをつけて、OKボタンを押す。

これで使えるようになる。

1

2

2019年10月10日 (木)

[Excel VBA]フォルダー内のサブフォルダー一覧を得る

ファイルシステムオブジェクトのGetFolderメソッドを使用する。以下はC:\Windowsフォルダー内のサブフォルダーをすべて表示させる例。

Dim obj As Object
With CreateObject("Scripting.FileSystemObject")
For Each obj In .GetFolder("C:\Windows").SubFolders
Debug.Print obj.name
Next obj
End With

 

2019年7月12日 (金)

[Excel VBA]フォルダー内のファイル一覧を得る

ファイルシステムオブジェクトのGetFolderメソッドを使用する。以下はC:\Windowsフォルダー内のファイルをすべて表示させる例。

Dim obj As Object
With CreateObject("Scripting.FileSystemObject")
For Each obj In .GetFolder("C:\Windows").Files
Debug.Print obj.name
Next obj
End With

2019年5月29日 (水)

[Excel VBA]そのモジュールやクラスが含まれているファイルが置かれているパスを得る

ApplicationオブジェクトのThisWorkbookプロパティのPathプロパティには、そのモジュールやクラスが含まれているファイル(.xlsm)が置かれているパスを返す。

Debug.Print Application.ThisWorkbook.Path

○:\○○\○○\…

Applicationオブジェクトは省略可能。通常は省略する。省略しても、結果は同じ。

Debug.Print ThisWorkbook.Path

○:\○○\○○\…

2019年5月24日 (金)

[Excel VBA]変数の宣言を強制にする(宣言していない変数を使えなくする)

Excel VBAは、初期状態ではDimステートメントで宣言をしなくても変数は使うことができるが、未宣言の変数の使用は、バグの温床になりやすい。そのため、Dimステートメントで宣言をしていない変数は使えないようにするべき。

モジュールの先頭部(宣言セクション、サブルーチンや関数の前)で、Option Explicitステートメントを使用する。

Option Explicit

2019年5月22日 (水)

[Excel VBA]そのモジュールやクラスが含まれているファイルのファイル名を得る

ApplicationオブジェクトのThisWorkbookプロパティのNameプロパティは、そのモジュールやクラスが含まれているファイルのファイル名を返す。

Debug.Print Application.ThisWorkbook.Name

○○.xlsm

Applicationオブジェクトは省略可能。通常は省略する。省略しても、結果は同じ。

Debug.Print ThisWorkbook.Name

○○.xlsm

2019年5月13日 (月)

[Excel VBA]そのモジュールやクラスが含まれているファイルのファイル名を得る

ApplicationオブジェクトのThisWorkbookプロパティのNameプロパティは、そのモジュールやクラスが含まれているファイルのファイル名を返す。

Debug.Print Application.ThisWorkbook.Name

○○.xlsm

Applicationオブジェクトは省略可能。通常は省略する。省略しても、結果は同じ。

Debug.Print ThisWorkbook.Name

○○.xlsm