vba 使用 Dir 按文件系统顺序从文件夹中返回文件

Use Dir to return files from a folder in file system order

提问by Roy

PixPath is the full path to a folder of jpg's, and I'm using code like the following to process each jpg.

PixPath 是 jpg 文件夹的完整路径，我使用如下代码来处理每个 jpg。

fileName = Dir(PixPath)
Do Until fileName = ""
   If Right$(fileName, 4) = ".jpg" Then   
      fileName = Dir()

      <process this jpg>

   End If
Loop

This works fine except the files are returned in alpha-numeric order, and not the order that the files are shown listed in the folder. Is there any way around this?

这工作正常，除了文件按字母数字顺序返回，而不是文件在文件夹中显示的顺序。有没有办法解决？

采纳答案by brettdj

You can't do this with Dir.

你不能用Dir.

An alternative is to:

另一种方法是：

• Use FileSystemObjectto access all the files in your directory
• Read in all the .jpgfiles into an array X
• Use Valto compare the .jpgs by value to sort in a numeric ascending order

• The final array Xcontains the sorted files

  Sub Test()
  Dim objFSO As Object
  Dim objFolder As Object
  Dim objFiles As Object
  Dim objFile As Object
  Dim X
  Dim lngFileCnt As Long
  Dim lngCnt As Long
  Dim i As Long
  Dim j As Long
  Dim strBuffer1 As String
  Dim strFolder As String
  
  Set objFSO = CreateObject("Scripting.fileSystemObject")
  strFolder = "C:\temp"
  Set objFolder = objFSO.getFolder(strFolder)
  Set objFiles = objFolder.Files
  lngFileCnt = objFiles.Count
  ReDim X(1 To lngFileCnt)
  
  'grab all jpg files        
  For Each objFile In objFiles
      If Right$(objFile.Name, 3) = "jpg" Then
          lngCnt = lngCnt + 1
          X(lngCnt) = objFile.Name
      End If
  Next
  
  'resize array to number of jpg files
  ReDim Preserve X(1 To lngCnt)
  
  'sort array by numeric value
  For i = 1 To lngCnt
      For j = (i + 1) To lngCnt
          If Val(X(i)) > Val(X(j)) Then
              strBuffer1 = X(j)
              X(j) = X(i)
              X(i) = strBuffer1
          End If
      Next
  Next
  MsgBox Join(X, ";")
  End Sub
  

  See herefor more info on using FileSystemObject.

• 使用FileSystemObject访问的所有目录中的文件
• 将所有.jpg文件读入数组X
• 用于按值Val比较.jpgs 以按数字升序排序

• 最终的数组X包含排序后的文件

  Sub Test()
  Dim objFSO As Object
  Dim objFolder As Object
  Dim objFiles As Object
  Dim objFile As Object
  Dim X
  Dim lngFileCnt As Long
  Dim lngCnt As Long
  Dim i As Long
  Dim j As Long
  Dim strBuffer1 As String
  Dim strFolder As String
  
  Set objFSO = CreateObject("Scripting.fileSystemObject")
  strFolder = "C:\temp"
  Set objFolder = objFSO.getFolder(strFolder)
  Set objFiles = objFolder.Files
  lngFileCnt = objFiles.Count
  ReDim X(1 To lngFileCnt)
  
  'grab all jpg files        
  For Each objFile In objFiles
      If Right$(objFile.Name, 3) = "jpg" Then
          lngCnt = lngCnt + 1
          X(lngCnt) = objFile.Name
      End If
  Next
  
  'resize array to number of jpg files
  ReDim Preserve X(1 To lngCnt)
  
  'sort array by numeric value
  For i = 1 To lngCnt
      For j = (i + 1) To lngCnt
          If Val(X(i)) > Val(X(j)) Then
              strBuffer1 = X(j)
              X(j) = X(i)
              X(i) = strBuffer1
          End If
      Next
  Next
  MsgBox Join(X, ";")
  End Sub
  

  请参见这里了解如何使用的详细信息FileSystemObject。

回答by Roy

The answer from brettdj (thank-you brettdj) works well and is probably the method I'll use, but I've found something else that also works and that might offer an advantage in other situations. For one thing, it retains the extreme simplicity of using Dir to loop through folders.

来自 brettdj（谢谢 brettdj）的答案效果很好，可能是我将使用的方法，但我发现了其他一些也有效的方法，并且可能在其他情况下提供优势。一方面，它保留了使用 Dir 循环文件夹的极端简单性。

In Excel 11, I use Dir (as described in the question) to create a list of jpg files from each folder (one folder at a time), sorted alpha-numeric in Col A. Then I use a Custom List to sort Col A with a (fake) numeric sort so I can process my jpg's in serial order. Then clear Col A, and repeat with the next folder.

在 Excel 11 中，我使用 Dir（如问题中所述）从每个文件夹（一次一个文件夹）创建一个 jpg 文件列表，在 Col A 中对字母数字进行排序。然后我使用自定义列表对 Col A 进行排序使用（假）数字排序，以便我可以按顺序处理我的 jpg。然后清除 Col A，并重复下一个文件夹。

To generate the Custom List:

生成自定义列表：

in Row 1 of a work column enter

在工作列的第 1 行中输入

 =ROW() & ".jpg"

and Fill Down to whatever suits. In my case I used 1000 items on my Custom List because that's the maximum number of jpg's I expect in any folder.

并填写到任何适合。就我而言，我在自定义列表中使用了 1000 个项目，因为这是我期望在任何文件夹中的最大 jpg 数量。

Custom Lists take only text (or "simple text" according to MS Help) so the newly generated list of formulas has to be converted to text using Paste>Special>Values before importing as a Custom List. Each item on the list is one of the expected file names. The final Custom List looks like this:

自定义列表仅采用文本（或根据 MS 帮助的“简单文本”），因此在作为自定义列表导入之前，必须使用 Paste>Special>Values 将新生成的公式列表转换为文本。列表中的每一项都是预期的文件名之一。最终的自定义列表如下所示：

 1.jpg
 2.jpg
 3.jpg
 …
 …
 1000.jpg

After I import my new Custom List (Tools>Options>Custom Lists>Import), it becomes an available selection in the dropdown menu at Data>Sort>Options>First Key Sort Order.

导入我的新自定义列表（工具>选项>自定义列表>导入）后，它成为数据>排序>选项>第一键排序顺序下拉菜单中的一个可用选项。

If you're doing this sort with VBA then here's what the Recorder provides:

如果您使用 VBA 进行这种排序，那么 Recorder 提供的内容如下：

Range("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

The first 5 Custom Lists are built into Excel, so OrderCustom:=6 is the new Custom List. Remember to change this back to OrderCustom:=False when doing normal Sorting. Custom Lists stay attached to the Wkb they're created in until deleted.

前 5 个自定义列表内置于 Excel 中，因此 OrderCustom:=6 是新的自定义列表。请记住在进行正常排序时将其更改回 OrderCustom:=False。自定义列表会一直附加到创建它们的 Wkb，直到被删除。