Hi Everyone,
I am after some help please!
I am trying to nest a do loop within a do loop so that I can generate multiple iterations. ie the second do loop executes for every loop of the first loop.
I want to do this becasue i am trying to write a macro that scans a file system for a folder with a time stamp and need to loop backwards through all the possible combinations of hours, days, months and years until the latest folder is found.
Code below
Please help!
[VBA]
Sub FolderExists()
Dim day As Integer
day = 30
Dim year As Integer
year = 2010
Dim hour As Integer
hour = 18
Dim month2 As Integer
month2 = 12
Dim month3 As String
Do
If month2 = 12 Then month3 = "DEC"
If month2 = 11 Then month3 = "NOV"
If month2 = 10 Then month3 = "OCT"
If month2 = 9 Then month3 = "SEP"
If month2 = 8 Then month3 = "AUG"
If month2 = 7 Then month3 = "JUL"
If month2 = 6 Then month3 = "JUN"
If month2 = 5 Then month3 = "MAY"
If month2 = 4 Then month3 = "APR"
If month2 = 3 Then month3 = "MAR"
If month2 = 2 Then month3 = "FEB"
If month2 = 1 Then month3 = "JAN"
Do
partial = "\\cefileserver\groups\EMO\Forecasting\Elec\ShortTerm\Forecasts\Half Hourly/"
folder = partial & day & month3 & year & "_" & hour
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(folder) = True Then
MsgBox folder & " is a valid folder/path.", vbInformation, "Path Exists"
Else
End If
hour = hour - 1
Loop Until fso.FolderExists(folder) Or hour < 9
If fso.FolderExists(folder) = True Then
MsgBox folder & " is a valid folder/path.", vbInformation, "Path Exists"
Else
End If
month2 = month2 - 1
Loop Until fso.FolderExists(folder) Or month2 < 0
End Sub
[/VBA]