vbaでファイル名の昇順にソートしたい

Question

現在、dir関数で下記のようなファイル一覧を取得して、エクセルシートに一つずつ入力しています。

佐藤1-10
田中11-35
松本36-48
竹岡49-59
田中60-100
隅田101-103

(名前は仮です)

フォルダのファイル名の昇順だと上記の順番になるのですが、

エクセルの通常の昇順だと

佐藤1-10
田中11-35
隅田101-103
松本36-48
竹岡49-59
田中60-100

こんな感じになっています。

色々調べていくとファイル名の昇順になるようソートをすればいいということですがいろんな回答例があり、結局どれを行えばよいかわかりません。

この処理ができれば、dir関数でなくともfilesystemobjectでも大丈夫です。

なにかアドバイスがありましたら宜しくお願いします。

追記
hope_mucci様
記載頂いたURLを確認いたました。

私には少し難しそうだったので、最も簡単そうに見えた下記URLの「ファイル名でソート」の部分を参考に、filesystemobjectとADOを使ってソートしてみたいと思います。

https://kuroeveryday.blogspot.com/2013/10/FileSystemObjectSort.html?m=1

こちらで実装可能そうでしょうか。アドバイスがありましたら、ご教授いただきたいと思います。

Answer 1

前回の回答コメントを受けて。

エクスプローラーのファイル名ソート順を決定づけているのは StrCmpLogicalW というWin32APIです。

StrCmpLogicalW (Microsoft)

これをVBAから呼び出してソートの比較関数に使うのですが、Win32APIを呼ぶにはいろいろ作法があるので下記の記事を参照してください。
ソートはバブルソートが一番簡単で良いかと。

VBAでWindowsAPIを使うには

特に後者のqiitaの記事はかなりわかりやすく説明されていますが、上記2記事の説明で実装方法がわからない、という場合はこの方法を導入するのはやめておいたほうが良いかもしれません。割とハードルは高いです。

まずはどうしてもエクスプローラのような挙動じゃないとダメなのかどうか、客先とよく相談されてみては。

Comments

[Tetsuya3456]

URLまで提示して頂き、ありがとうございます。自分でも実装できるか読んでみます。

[ExcelVBAer]

ソートアルゴリズムは、クイックソートでも、バブルソートでも探せば転がってると思いますよ。
後はあなたのやる気次第でしょう。

[Tetsuya3456]

回答ありがとうございます。

一旦ファイル名から数値を抜き出す方法でできないか検討してみて、無理そうならAPIを使って実装してみたいと思います。

[hope_mucci]

これを機にWin32API呼び出しの実装を経験してみるのも悪くないと思いますよ。
世界観とあなたの実装能力が広がります :)

Answer 2

私には少し難しそうだったので、最も簡単そうに見えた下記URLの「ファイル名でソート」の部分を参考に、filesystemobjectとADOを使ってソートしてみたいと思います。

https://kuroeveryday.blogspot.com/2013/10/FileSystemObjectSort.html?m=1

こちらで実装可能そうでしょうか。アドバイスがありましたら、ご教授いただきたいと思います。

ADOのソートも文字コード基準のエクセルのソートと同じようなものしかできません。
hope_mucciさんの回答のAPIを使わずにエクスプローラーと同じ仕様のソートにするのは、無理でしょう。

APIなしでやるなら、まずはファイル名を、Dir関数なりfilesystemobjectなりでシートに書き出して、
その隣のセルに、数値を0埋めしたものに変換したのを出力して、それを基準にソートするということになるでしょうね。（あるいは、hope_mucciさんの最初の回答の数値も別のセルに書き出す方法とか）
APIを使った方法が楽か、こちらが楽かどうかは、ファイル名のフォーマットがどの程度複雑か、によるでしょうね。

Comments

[Tetsuya3456]

回答頂きありがとうございます。

ADOではできないのですね。
やる前に教えて頂き、ありがとうございます。

であれば一旦数字だけ抜き取れないか等検討してみて、それが無理そうならAPIを使ってやってみたいと思います。

丁寧に教えて頂きありがとうございますm(_ _)m

Answer 3

名前 数値1 - 数値2 というフォーマットで、数値1で並び変えたいということですかね。

最近のWindowsエクスプローラーは、ファイル名を辞書順ではなく数字部分を数値とみなして並び替えしようとします。
質問文のソートの下側の並びが辞書順です。

Excelで上側と同じように並び替えしたかったら、数字の部分を分割して別セルに入れて、その列でソートをかけるか（下表のような表を作り、数値１、数値２でソートをかける）

オリジナル	数値1	数値2
佐藤1-10	1	10
田中11-35	11	35
松本36-48	36	48
竹岡49-59	49	59
田中60-100	60	100
隅田101-103	101	103

またはファイル名の形式変更が許容できるなら、数値にゼロ埋めをするのも手では。

佐藤001-010
田中011-035
松本036-048
竹岡049-059
田中060-100
隅田101-103

私が実業務でやるとしたら、まず後者を提案し、却下されたら前者の案で進めるかな。

Comments

[Tetsuya3456]

回答ありがとうございます。

それがファイル名は変更不可で実際のファイル名は、数字が前にあったり後ろにあったりと様々です。

ファイル名を分割せずにフォルダ名の順番にするのは結構大変でしょうか。

[hope_mucci]

えー、実際のファイル名はもっと複雑ですか。
URLとか埋めたかったので別の回答を起こしました。そちらもご覧ください。