PDA

View Full Version : VBA - Open each file from directory in ascending Order



malleshg24
08-07-2019, 12:04 PM
Hi Team,


Is there a way to open each files from a folder in ascending order.I am consolidating the data
But files are getting opened randomely and to correct it I am sorting the data in Master File.


Thanks in advance for your help.


Sub LoopThroughFiles()
Dim StrFile As String
StrFile = Dir("c:\testfolder\*test*")
Do While Len(StrFile) > 0
Debug.Print StrFile
StrFile = Dir
Loop
End Sub




Thanks
mg

Kenneth Hobs
08-07-2019, 12:41 PM
Modify to suit:

Sub myBatch()
Dim s As String, a() As String, myDir As String, v As Variant
myDir = "C:\ken\_Excel\Batch\"
s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & """" & myDir & "*at*" & """" & _
" /od/b").StdOut.ReadAll '/s to iterate subfolders. Full paths are then shown.
a() = Split(s, vbCrLf)
On Error GoTo endSub
ReDim Preserve a(0 To UBound(a) - 1) As String 'Trim trailing vblfcr


MsgBox s
For Each v In a()
'Replace Debug.Print line with your batching code to act on each found file.
Debug.Print myDir & v
Next v

endSub:
End Sub

malleshg24
08-08-2019, 10:31 PM
Hi Kenneth,

Thanks for the help:thumb, there is a solution for this task as well !!

Regards,
mg