Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


2892 / 13644 ツリー ←次へ | 前へ→

【65307】エクスプローラの昇順とエクセルの昇順 pon 10/5/7(金) 16:42 質問[未読]
【65308】Re:エクスプローラの昇順とエクセルの昇順 kanabun 10/5/7(金) 17:23 発言[未読]
【65309】Re:エクスプローラの昇順とエクセルの昇順 pon 10/5/7(金) 17:48 発言[未読]
【65310】Re:エクスプローラの昇順とエクセルの昇順 kanabun 10/5/7(金) 19:10 発言[未読]
【65410】Re:エクスプローラの昇順とエクセルの昇順 pon 10/5/19(水) 10:02 お礼[未読]

【65307】エクスプローラの昇順とエクセルの昇順
質問  pon  - 10/5/7(金) 16:42 -

引用なし
パスワード
   こんにちは よろしくお願いいたします

エクスプローラのファイル一覧ウインドウでファイル名で並べ替えると
A11.pdf
A12.pdf
A13.pdf
A110.pdf
のように並んでいます

上記のファイル情報をエクセルに取り込んで昇順に並べ替えると
A11.pdf
A110.pdf
A12.pdf
A13.pdf
のように並んでしまいます

エクセルで
エクスプローラのような並びにするにはどうすればよいでしょうか

【65308】Re:エクスプローラの昇順とエクセルの昇順
発言  kanabun  - 10/5/7(金) 17:23 -

引用なし
パスワード
   ▼pon さん:

>エクセルで
>エクスプローラのような並びにするにはどうすればよいでしょうか

エクスプローラの並びというのは 数字の部分を数値として並び替えて
いるということですかね?

泥臭い方法ですが、こんなのとか?
([A2]以降に ファイルリストがあるとします。拡張子は同じとします)

Sub Try1()
 Dim r As Range
 Dim i As Long, j As Long
 Dim arry, S As String
 
 Set r = Range("A2", Cells(Rows.Count, 1).End(xlUp)).Resize(, 2)
 r.Columns(2).ClearContents
 arry = r.Columns(1).Value
 For i = 1 To UBound(arry)
   S = arry(i, 1)
   For j = 1 To Len(S)
     If Mid$(S, j, 1) Like "#" Then
       arry(i, 1) = Left$(S, j - 1) & Format$(Val(Mid$(S, j)), String$(20 - j, "0"))
       Exit For
     End If
   Next j
 Next i
 r.Columns(2).Value = arry
 r.Sort Key1:=r.Columns(2), Header:=xlNo
 'r.Columns(2).ClearContents
 
End Sub

【65309】Re:エクスプローラの昇順とエクセルの昇順
発言  pon  - 10/5/7(金) 17:48 -

引用なし
パスワード
   kanabun さん こんにちは

お世話になります

>エクスプローラの並びというのは 数字の部分を数値として並び替えて
>いるということですかね?
解説いただき納得みたいなです

>泥臭い方法ですが、こんなのとか?
ん〜 なるほどです

もしかしたら、エクスプローラで使用している
並べ替え関数?とか
オプションみたいなものがあるのか
とか思っていました

>([A2]以降に ファイルリストがあるとします。拡張子は同じとします)
実際は
フルパスになっていて
そのためかと思いますがうまく動作できませんでした

ご提示いただいたコードもう少し勉強してみます

ご報告には、ちょっと時間がかかりそうですが
よろしくお願いいたします

【65310】Re:エクスプローラの昇順とエクセルの昇順
発言  kanabun  - 10/5/7(金) 19:10 -

引用なし
パスワード
   ▼pon さん:

>>([A2]以降に ファイルリストがあるとします。拡張子は同じとします)
>実際は
>フルパスになっていて
>そのためかと思いますがうまく動作できませんでした

そのコードの処理法は、
>   S = arry(i, 1)
    'セルの文字列を先頭から一つづつ見ていって
>   For j = 1 To Len(S)
      ' 文字が数字なら
>     If Mid$(S, j, 1) Like "#" Then
        '数字の前の文字列 と
        ' それ以降の文字列を数値にして書式"00000000"
        ' でFormatした文字列を結合したものとする
>       arry(i, 1) = Left$(S, j - 1) _
>       & Format$(Val(Mid$(S, j)), String$(20 - j, "0"))

ということをしていますから、
パス名に数字が入っていたりすれば、
パス名の数値部分を数値にして並び替えようとします。

こちらでは、ponさんが提示されたようなサンプルデータを
想定して検証してみているわけですから、

'---------------------------------------
(こちらが検証に使ったデータは 以下のようなものです
  これは Excelでソートしたあとの並び順です)
▼Filename
A0003.txt
A010.txt
A033.txt
A11.txt
A110.txt
A1100.txt
A12.txt
A13.txt
A3.txt

  (Try1マクロで並び替えると、以下のような並びになります。
    この並び順は エクスプローラと同じ並び順です)
▼Filename
A0003.txt    A000000000000000003
A3.txt    A000000000000000003
A010.txt    A000000000000000010
A11.txt    A000000000000000011
A12.txt    A000000000000000012
A13.txt    A000000000000000013
A033.txt    A000000000000000033
A110.txt    A000000000000000110
A1100.txt    A000000000000001100
'-------------------------------------------------

pon さんのほうでも、最初にテストするとき、
ファイルリストはパス名の付いていない裸のファイル名で
検証していただきたかったです。

パス名付きのファイル名をファイル名の部分で並び替えるなら、
For〜Next部分を 以下のように加筆して試してみてください。

 For i = 1 To UBound(arry)
   S = arry(i, 1)
   j = InStrRev(S, "\")
   If j Then
     S = Mid$(S, j + 1)
     For j = 1 To Len(S)
       If Mid$(S, j, 1) Like "#" Then
         arry(i, 1) = Left$(S, j - 1) & Format$(Val(Mid$(S, j)), String$(20 - j, "0"))
         Exit For
       End If
     Next j
   End If
 Next i

【65410】Re:エクスプローラの昇順とエクセルの昇順
お礼  pon  - 10/5/19(水) 10:02 -

引用なし
パスワード
   ▼kanabun さん:
大変遅くなりました


>>実際は
>>フルパスになっていて
>>そのためかと思いますがうまく動作できませんでした

>>ご提示いただいたコードもう少し勉強してみます


>ということをしていますから、
>パス名に数字が入っていたりすれば、
>パス名の数値部分を数値にして並び替えようとします。

>こちらでは、ponさんが提示されたようなサンプルデータを
>想定して検証してみているわけですから、

申し訳ありませんでした

実際のところ
ファイル名の命名規則がいまいちでパターンをつかむ必要がありました

後、取得DTでのテストでは
エクセルシート上でのフルパスでの並べ替えで影響が出るのは
ファイル名の数値部分だけのようでした

レコード的には
\\fuga\20091あ\0401\A08.PDF    
\\fuga\20091あ\0401\A11.PDF    
\\fuga\20091あ\0401\A110.PDF    ←
\\fuga\20091あ\0401\A12.PDF    
\\fuga\20091あ\0401\AB02.PDF    
\\fuga\20091あ\0401\AB03.PDF    
\\fuga\20091あ\0401\AB103.PDF    ←
\\fuga\20091あ\0401\AB12.PDF    
こんなかんじでした
(ファイル名は実際こんな感じです、パスで内容表示みたいな感じ・・)

やり方としては
kanabun さん のアドバイスをもとに、
英字部を4文字用意し A→Aaaa,ABaa
数字部を4文字用意し 08→0008,103→0103
みたいな処理をして、
補助列にフルパスのABaa0103等をいれそこで昇順処理で希望の操作がかないました

ファイル名 AB03.PDF から 数値 03を 取得するに当たり
生まれて初めて正規表現をつかってみました  (*^-^*)
といってもネットで公開のYukiさん関数を利用させて頂いただけですが・・・
使いこなすには難しそうですがとっても強力みたいな感じです


?TESTX("AB103.PDF")
103


Function TESTX(strD)
  'Dim strD  As String
  Dim objSC As Object
  Dim objS  As Object
  Dim objSR  As Object
  Dim strF  As String
  Dim varF  As Variant

  'strD = "ABC 12345678 DEF"

  Set objSC = CreateObject("VBScript.RegExp")

  With objSC
    .Pattern = "\d+"
    .Global = True
    Set objSR = .Execute(strD)
  End With

  For Each objS In objSR
    'Debug.Print objS.Value
    TESTX = objS.Value
    
  Next
End Function


その他、いくつかフルパス加工の関数を用意して
補助列には 上記組み合わせた
if文がネストした長ったらしい数式になってしまいましたが
おかげさまで希望の操作かないました
ありがとうございました

今後もよろしくお願いいたします

2892 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free