Worksheetは最大何枚まで追加できるのか

以前の記事で新しいブックの作成時に表示できるWorksheetの最大値は255枚だと判明しました。

excellover.hatenablog.com

では新しいブックの作成時に関わらず、追加できるWorksheetの最大枚数は一体何枚なのでしょうか?

調べてみました。

マクロでシートを大量追加

早速調査開始です。

1枚1枚Worksheetを追加していては日が暮れますので、

VBAで簡単なマクロを作成しました。

Sub Woksheets_Infinity_Add()
  
    Do Until Worksheets.Count = 10000
        Worksheets.Add
        Debug.Print Worksheets.Count
    Loop
End Sub

このコードで問題なく10,000枚のシートが作成されるのか。

どこかでエラーが発生して止まるのか。

絶対使わないよね、この枚数

結果は10,000枚までは追加することが出来ずエラーが発生しました

では最終的に何枚追加できたかと言うと

f:id:ExcelLover:20180516200421j:plain

5,448枚!

使わねぇ。

絶対にこんな枚数使わない。

実際この枚数まで追加することは出来ましたが、Excelは不安定で

シートの移動どころかマウスカーソルの移動ですらまともに反応しません。

これだけのシートを活用することはないでしょうが、そもそも実用的ではありません。

シートの行・列の限界は聞いたことがあったけど、Worksheetの枚数の限界を知れたのは初めての事でしたので 調べるのは面白かった。

Workhsheetの最高枚数は5,448枚!

補足

自分で調べた後でネットで調べてみるとどうやらWorksheetの最高枚数は厳密に決まっているわけではないようです。

ブックのシート  使用可能メモリに依存 (既定値は 1 シート)

support.office.com

使用可能メモリの依存ということは僕の環境(8GB)より多くメモリを積んでいれば、

もっと開けられるのかな?

エクストリームなPC環境の人、試してみてくれないかなぁ。

新しいExcelをファイル開いた時のシート枚数を変更する

Excelの資料が送られてきて中身を確認。

f:id:ExcelLover:20180516105206j:plain

まずSheet1を見て次にSheet2を見たら

真っ白

さらにSheet3を見たら

また真っ白

そんな事ありますよね。

本当に些細なことではあるんですが

「ないんかよ!」と心の中でツッコんでしまうこともしばしばです。

送られてくる資料は仕方ないとしても、自分が作る資料で相手に心の中でツッコミをさせないようにするためにも対策をしましょう

シート枚数の初期設定は変更できる

なぜ最初に書いたような状態になるかと言うと

Excelを開いた時に最初からシートが3枚表示されているからなんですね。

f:id:ExcelLover:20180516105229j:plain

オプション⇛基本設定⇛新しいブックの作成時

ブックのシート数(S)

の設定が「3」になっているので新しいブックの作成時にシートが3枚ある状態になります。

なのでこの数字を3⇛1に変更してやると

f:id:ExcelLover:20180516105243j:plain

新しいブックの作成時にシートが1枚の状態になります。

f:id:ExcelLover:20180516105259j:plain

これで資料の送付先の人にツッコまれずに済みます。

補足

シート枚数の初期値の最大値は…

今回は3枚から1枚にシート枚数を減らしましたが、

反対に増やすことは出来るのか?

増やすことも出来ます。

しかも・・・

f:id:ExcelLover:20180516105317j:plain

255枚まで

試しに設定してみました。

f:id:ExcelLover:20180516105330j:plain

確かにExcelを新規で開くとシートが255枚作成されています。

データが記入されているわけではないので、特にExcelを開くのが遅くなるわけでは無いみたいです。

しかしこんだけシートが作れるからって、このシートの全てにデータが記入されているようなブックは管理したくないですね

ExcelからSQLite3を操作する方法(2.SQLite3.dllに接続)

前回まででSQLite For Excelを使用する準備が整いました。

今の状態はこんな感じでしょうか。 f:id:ExcelLover:20180513182925j:plain

今回は実際にSQLite For Excelを使用してSQLite3.dllへの接続までやってみたいと思います。

Sqlite3Demoに全てがある

これからやることは基本的に全てSqlite3Demoのコードを流用していきます。

これ以下を見なくてもSqlite3Demoのコードを読んで使いたいところをコピペすれば動きます。

Sqlite3Demoが参考書になります。

SQLite For Excelを読み込む

まずはSQLite3.dllを読み込みます。

読み込みますと書いたが、正直この辺の仕組みはよく理解していない。

この過程を踏むことでSQLite3 For Excelの機能が使用可能になる。

Sqlite3DemoではAllTests内の以下の部分です。

    Dim InitReturn As Long
    InitReturn = SQLite3Initialize
    If InitReturn <> SQLITE_INIT_OK Then
        Debug.Print "Error Initializing SQLite. Error: " & Err.LastDllError
        Exit Sub
    End If  

上記のコードを自分が作成しているプロシージャに組み込んでください。

ここからいよいよSqlite3.dllの機能を使っていきます。

上記2行目の InitReturn = SQLite3Initialize の右辺がSqlite3.dllの機能です。

SQLite3Initializeの部分にマウスカーソルを当てた状態で

Shift + F2

を押すと

Public Function SQLite3Initialize(Optional ByVal libDir As String) As Long
    ' A nice option here is to call SetDllDirectory, but that API is only available since Windows XP SP1.
    If libDir = "" Then libDir = ThisWorkbook.Path
    If Right(libDir, 1) <> "\" Then libDir = libDir & "\"
    
    If hSQLiteLibrary = 0 Then
        hSQLiteLibrary = LoadLibrary(libDir + "SQLite3.dll")
        If hSQLiteLibrary = 0 Then
            Debug.Print "SQLite3Initialize Error Loading " + libDir + "SQLite3.dll:", Err.LastDllError
            SQLite3Initialize = SQLITE_INIT_ERROR
            Exit Function
        End If
    End If
        
    If hSQLiteStdCallLibrary = 0 Then
        hSQLiteStdCallLibrary = LoadLibrary(libDir + "SQLite3_StdCall.dll")
        If hSQLiteStdCallLibrary = 0 Then
            Debug.Print "SQLite3Initialize Error Loading " + libDir + "SQLite3_StdCall.dll:", Err.LastDllError
            SQLite3Initialize = SQLITE_INIT_ERROR
            Exit Function
        End If
    End If
    SQLite3Initialize = SQLITE_INIT_OK
End Function

上記のコードが表示されたでしょうか?(他にも前後にたくさん表示されてるとは思いますが)

正直内容がよくわからないとは思います。(僕もです)

でもよくはわからなくても何となくわかればOKです。

それがクラスを使うメリットです。

SQLite3Initialize関数の機能

Sqlite3クラスのSQLite3Initialize関数は以下の特徴があると思われます。

・引数の指定をしなくても動く

・SQLite3.dllとSQLite3_StdCall.dllを使用可能状態にする

・上記dllが使用可能状態になれば「SQLITE_INIT_OK」

なんらかのエラーがあれば「SQLITE_INIT_ERROR」を

関数の返り値として返す。

クラスが全てよろしく処理してくれる

取り敢えず自分のプロシージャに組み込んだコードを実行してみましょう。

エラーもなく特に何も起きませんでしたか?

であれば無事にSQLite3.dllとSQLite3_StdCall.dllが使用可能状態になっています。

エラーが出たり、イミディエイトウィンドウに何か表示されていますか?

であればまだSQLite3.dllとSQLite3_StdCall.dllが使える状態になっていません。

今の段階で問題があるとすれば

・Sqlite3のクラスが自作のExcelファイルに正しくインポートされていない。

・SQLite3.dllとSQLite3_StdCall.dllの2つのdllが自作Excelファイルと同じ場所に保存されていない。

いずれかを確認してみてください。

次回はデータベース作成・テーブルの作成にチャレンジしたいと思います。

補足

・便利な「クラス」ですが中身の見えない「クラス」を何の警戒もなく使うのは怖いということも。

ピボットテーブルのデータを手動で任意に並び替える方法

先日、書いたこちらの記事を作成中に気づいたんだけど、ピボットテーブルのデータは任意の位置に並び替えることが出来ます。

excellover.hatenablog.com

昇順・降順の基準でしか配置できないと思っていたのでビックリしました。

またもや行ラベルに答えあり!

前回と同じく行ラベルに設定がありました。

f:id:ExcelLover:20180512124731j:plain

行ラベルの下三角▼⇛その他の並び替えオプション(M)

f:id:ExcelLover:20180512124747j:plain

手動(アイテムをドラッグして並べ替える)(M)

にチェックが入っていればOK。

セルをドラッグで並び替え

f:id:ExcelLover:20180512124835j:plain

移動させたいセルをクリック

f:id:ExcelLover:20180512124849j:plain

マウスカーソルが上図のようになる位置に移動

f:id:ExcelLover:20180512124902j:plain

左ドラッグでデータを移動させたい位置までマウスカーソルを移動

f:id:ExcelLover:20180512124916j:plain

データを移動させることが出来ました。

補足

・今までは思ったとおりに並び替えることが出来なかったので、ピボットテーブルのデータをコピーして別のシートに値貼り付けをして並び替えをしたりしていました。

ピボットテーブルのデータが並び替えられない時に確認すること

ピボットテーブル便利ですよね。

こいつが使えるか使えないかで仕事の効率化にかなり差が出てきます。

今回はつい先日自分がおやっ?と思ったことをメモしておきます。

データが並び替えられない

f:id:ExcelLover:20180511174922j:plain

例えばピボットテーブルを作成した結果、上記のような状態になった時に、

総計を降順(大きい順)で並び替えたいときありますよね。

f:id:ExcelLover:20180511174942j:plain

そういう時は並び替える基準となる列の任意の場所(今回は総計のどこか)で右クリックして

並び替え⇛降順(O)

を選択すると並び替えられる。・・・

筈なんですが、何も変わりません。

行ラベルに答えあり!

結論から言うと

f:id:ExcelLover:20180511175001j:plain

行ラベルの下三角▼⇛その他の並び替えオプション(M)

f:id:ExcelLover:20180511175013j:plain

降順(D)にチェックを入れて、並び替えの基準としたいフィールドを選択。

(今回「合計/年齢」)

あら不思議

f:id:ExcelLover:20180511175025j:plain

先程は出来なかった、降順での並び替えを実行することが出来ました。

ピボットテーブルで並び替えが出来なかった時は、行ラベルのオプションを

確認してみてください。

補足

・今回のピボットテーブルの元データはこちらのサイトからを利用して作成したものに、ちょっとだけ加工しました。

なんちゃって個人情報

・どうも行ラベルが1列の時はこんなことしなくても普通に基準列で並び替えを実行すれば出来るみたいなんだけど、確かな条件がよくわからない。

・今回初めて知ったけど、ピボットテーブルのデータって強制的に何らかの条件で並び替えを強制されるもんだと思ってたけど、手動で並び替え出来るんだよな。

ExcelからSQLite3を操作する方法(1.使用準備)

前回までで

ExcelSQLiteを使用する excellover.hatenablog.com

・MP3Tagの抽出方法 excellover.hatenablog.com

を確認した。

今回はExcelからSQLiteを操作する方法を確認していく。

SQLite For ExcelSQLiteを操作

ExcelからSQLiteを操作する方法については、調べると色々出てくるが必須条件は

インストール不要(管理者権限がなくても使える)

これだ。

家でMusic Playerとして使うだけなら必須項目ではないが、せっかく勉強したSQLiteを操作するスキルを会社で使わない手はない。

ダウンロード禁止ぐらいなら回避方法はある(良い子はやっちゃダメだぞ。)が、ソフトインストールばかりは会社のシステム担当にお伺いを立てないとどうしようもない。

その点、今回使用するSQLite For Excelはダウンロードしたファイルをマクロを作成するExcelファイルと同じ場所に保存しておくだけで使用することが可能。

SQLite For Excelの準備

今回の記事を作成するに当たって、以下の記事を参考にさせて頂きました

ExcelからSQLiteを使う方法 | Gabekore Garage gabekore.org

まずは今回のキモ、SQLite For Excelのダウンロード

Releases · govert/SQLiteForExcel · GitHub github.com

f:id:ExcelLover:20180506181045j:plain

「SQLiteForExcel-1.0zip」をクリックしてダウンロード。 f:id:ExcelLover:20180506181213j:plain

ダウンロードした圧縮ファイルを解凍して

SQLiteForExcel-1.0.zip→Distributionの中身の内

・sqlite3.dll

・SQLite3_StdCall.dll

上記2つを、マクロを作成するExcelファイルと同じ場所に保存。 f:id:ExcelLover:20180506181325j:plain

次にSQLiteForExcel.xls(もしくはSQLiteForExcel_64.xlsm)を開き、Visual Basic Editorを開く。

標準モジュール内のSqlite3をエクスポートしてから自分のExcelファイルにインポートする。

もちろんSqlite3内のコードをコピーして、自分のExcelファイルにペーストでもOK。 (※sqlite3demoも同じようにインポートしておくと後で楽できます。)

使用準備は以上で完了。

とっても簡単。

次回は実際にSQlite For Excelを使ってSQLite3のデータベース作成、

テーブル作成を試してみます。

MP3からTag情報を抽出する方法

データベースに登録するTag情報をどうやって楽曲から抽出するか。 旧版のExcel Playerでは以下の方法で取得

ShellオブジェクトのNamespaceメソッドでFolderオブジェクトを作成。
FolderオブジェクトのGetdetailsofメソッドでファイルのプロパティを取得

Office TANAKA - 番外編[MP3の「曲の長さ」を調べる]

Rebuild版作成に当たり調べてみたが、他に良さそうな手段が見当たらなかったので、引き続きGetdetailsofメソッドで取得してみる。

Sub Song_Property()
    Dim FSO As Object
    Dim objShell As Variant
    Dim objFolder As Variant
    Dim i As Long
    Dim Song As Variant
    
    Set FSO = CreateObject("Scripting.Filesystemobject")
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace("G:\Music\Frost\Milliontown\")
    Song = FSO.getfile("G:\Music\Frost\Milliontown\01 Hyperventilate.mp3").Name
    For i = 0 To 1000
        Cells(i + 1, 1).Value = i
        
        'ファイルのプロパティ名を取得
        Cells(i + 1, 2).Value = objFolder.getdetailsof(objFolder.parsename(objFolder), i)
        
        'ファイルのプロパティ値を取得
        Cells(i + 1, 3).Value = objFolder.getdetailsof(objFolder.parsename(Song), i)
    Next i  
End Sub  
  

試しに1曲情報を取得してみると、僕の環境(Win2007・Excel2013)、音楽ファイル運用方では
・288種類のプロパティ
・上記の内36種類のプロパティに値有り

36種類のプロパティの内、Excel Playerに使えそうなのは以下の通り
ID プロパティ
14 アルバム
15 年
16 ジャンル
20 作成者(※アーティスト名)
21 タイトル(※曲名)
26 トラック番号
27 長さ(※再生時間)
180 パス
195 発行元(※発売レーベル)
220 作曲者

このプロパティをデータベースに設定することにする