0

I have the following code that grabs docx files out of a specific directory. Then another section of codes parses the tables out of them into an excel file. My problem is that the files are not being processed in order. They are being randomly grabbed. In order for my final file to have the correct data I need this be processed in order (all files start with 1,2,3..etc)

Dim MyFile As Variant
Dim Counter As Long

'Create a dynamic array variable, and then declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)

'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$("c:\test\output\*.*")
Do While MyFile <> ""
DirectoryListArray(Counter) = MyFile
MyFile = Dir$
Counter = Counter + 1
Loop

'Reset the size of the array without losing its values by using Redim Preserve
ReDim Preserve DirectoryListArray(Counter - 1)


Dim Loc As String
Loc = "C:\test\output\"
Community
  • 1
  • 1
Nolemonkey
  • 159
  • 2
  • 12
  • 1
    One way would be to sort your array of filenames http://stackoverflow.com/questions/152319/vba-array-sort-function. – Ambie Sep 09 '16 at 14:49
  • I see to call the array I would use this "Call QuickSort(myArray, 0, UBound(myArray))" how would I input that into my code? Replace the Dir$? – Nolemonkey Sep 09 '16 at 15:09
  • `Dir$` *defaults to* filename sort order. Do your filenames have leading zeros if the number of digits don't match? I.e. "1_foo.docx, 10_foo.docx, 2_foo.docx". – Comintern Sep 09 '16 at 15:09
  • I added numbers to them to keep them separate, there a few ABC files but they end differently. The names are all different enough that the order will stay numerical so 10 will never come before 2. They all go 1ABC_name_version, 2DEF_difname_version – Nolemonkey Sep 09 '16 at 19:56

2 Answers2

0

I found another thread that pointed me an answer. I had to use a few other pieces. This Quicksort Algorithm and this function which gets files from a folder. With the help of these I made the following changes. 

Dim allFiles As Variant
Dim Mydir As String
Mydir = "c:\builds\combine\"
allFiles = GetFileList(Mydir & "*.docx")
If IsArray(allFiles) Then
Call QuickSort(allFiles, LBound(allFiles), UBound(allFiles))
End If

Then in the code below this to process the tables, I just pointed it at the allFiles array and it loaded it in order.

Community
  • 1
  • 1
Nolemonkey
  • 159
  • 2
  • 12
-1

NOTE: I did say I hadn't test that. Been a while since I worked inside of VBA. Sorry:

OK - there doesn't seem to be a 'clean' way to readily sort using the Dir$ so I might roll with shell.

Shell ("cmd.exe /C dir c:\test*.* /a:-d /o:n /b > c:\test\output\myfiles.txt")

Then read the myfiles.txt using the fso and it will be nicely sorted by name ascending. The /a:-d is there so the list doesn't include directories and the /b gives you bare filename only (no size and date info).

One problem with this might be - you have to watch out for numbers when using an alpha sort - because 1 to 199999 will sort before 2.

user3023271
  • 1
  • 1
  • if I add that the part of the code that processes the word doc it tells me my document has no tables for some reason? – Nolemonkey Sep 09 '16 at 15:08
  • `Dir$` doesn't support the shell command line arguments. This is a Run-time error 52 if you execute it (and alpha is the default sort anyway). – Comintern Sep 09 '16 at 15:33