Nest do loops

[rossmiddleto]

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!






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

[Simon Lloyd]

You could always check for the folder modified date using FileDateTime ( file_path ) like this Dim MyDate As Date
MyDate= FileDateTime("C:\MyFile")
MsgBox MyDateWhere MyFile would be the file you are looking at.

[rossmiddleto]

Thanks for the suggestion Simon but the I cannot use the modified date to search for the file as people go and change files all the time. I need to search for the latest file using the file name. Any idea on what is wrong with my do loops?

[Bob Phillips]

This seems to work for me



Sub FolderExists()
Dim fso As Object
Dim day As Integer, month2 As Integer, year As Integer
Dim month3 As String
Dim hour As Integer
Dim partial As String
Dim folder As String

day = 30
year = 2010
hour = 18
month2 = 12

Do
month3 = Format(DateSerial(year, month2, 1), "mmm")
Set fso = CreateObject("Scripting.FileSystemObject")
Do
partial = "\\cefileserver\groups\EMO\Forecasting\Elec\ShortTerm\Forecasts\Half Hourly/"
folder = partial & day & month3 & year & "_" & hour
If fso.FolderExists(folder) 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) 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

[rossmiddleto]

Thanks for your help, but the macro doesn’t display any results when I execute it.

When I comment out each do loop in turn so that there is only one loop executing the message "...is a valid folder/path." is displayed but the folder that the message specifies does not exist when I check in the actual folder system.

Kind Regards.

Ross

[Bob Phillips]

Then I am at a loss I am afraid. I tested it, with my folders, and it seemed to work.

[rossmiddleto]

Ok, thanks anyway!

[Simon Lloyd]

Can you right click the folder/file and choose properties and then copy the target address and supply the filename so we can look at the construction?

[rossmiddleto]

I have been fiddling around all morning think I have just about managed to get the code working.

Like you guys have said, I think the problem might have been due to excel dropping out a lead zero from the day integer as in the file folder file name the day needs to be "08" not "8". I changed the type from integer to string and put a few if statements in the code to work around this and it seems to work ok.

If you can spot any way that I can cut out unnecessary lines of code then please feel free to make suggestions.

Thank you all again for your time.




Sub FolderExists()
Dim fso As Object
Dim day As String, day1 As Integer, day2 As String, month2 As Integer, year As String, year1 As Integer, year3 As String
Dim month3 As String
Dim hour As Integer
Dim partial As String
Dim folder As String
Dim date1 As Date

hour = 18
month2 = 12

day = Format(Date, "dd")

If day = "31" Then day1 = 31
If day = "30" Then day1 = 30
If day = "29" Then day1 = 29
If day = "28" Then day1 = 28
If day = "27" Then day1 = 27
If day = "26" Then day1 = 26
If day = "25" Then day1 = 25
If day = "24" Then day1 = 24
If day = "23" Then day1 = 23
If day = "22" Then day1 = 22
If day = "21" Then day1 = 21
If day = "20" Then day1 = 20
If day = "19" Then day1 = 19
If day = "18" Then day1 = 18
If day = "17" Then day1 = 17
If day = "16" Then day1 = 16
If day = "15" Then day1 = 15
If day = "14" Then day1 = 14
If day = "13" Then day1 = 13
If day = "12" Then day1 = 12
If day = "11" Then day1 = 11
If day = "10" Then day1 = 10
If day = "09" Then day1 = 9
If day = "08" Then day1 = 8
If day = "07" Then day1 = 7
If day = "06" Then day1 = 6
If day = "05" Then day1 = 5
If day = "04" Then day1 = 4
If day = "03" Then day1 = 3
If day = "02" Then day1 = 2
If day = "01" Then day1 = 1



If day1 = 31 Then day2 = "31"
If day1 = 30 Then day2 = "30"
If day1 = 29 Then day2 = "29"
If day1 = 28 Then day2 = "28"
If day1 = 27 Then day2 = "27"
If day1 = 26 Then day2 = "26"
If day1 = 25 Then day2 = "25"
If day1 = 24 Then day2 = "24"
If day1 = 23 Then day2 = "23"
If day1 = 22 Then day2 = "22"
If day1 = 21 Then day2 = "21"
If day1 = 20 Then day2 = "20"
If day1 = 19 Then day2 = "19"
If day1 = 18 Then day2 = "18"
If day1 = 17 Then day2 = "17"
If day1 = 16 Then day2 = "16"
If day1 = 15 Then day2 = "15"
If day1 = 14 Then day2 = "14"
If day1 = 13 Then day2 = "13"
If day1 = 12 Then day2 = "12"
If day1 = 11 Then day2 = "11"
If day1 = 10 Then day2 = "10"
If day1 = 9 Then day2 = "09"
If day1 = 8 Then day2 = "08"
If day1 = 7 Then day2 = "07"
If day1 = 6 Then day2 = "06"
If day1 = 5 Then day2 = "05"
If day1 = 4 Then day2 = "04"
If day1 = 3 Then day2 = "03"
If day1 = 2 Then day2 = "02"
If day1 = 1 Then day2 = "01"



year = Format(Date, "YYYY")

If year = "2011" Then year1 = 11
If year = "2010" Then year1 = 10
If year = "2009" Then year1 = 9

If year1 = 11 Then year3 = "2011"
If year1 = 10 Then year3 = "2010"
If year1 = 9 Then year3 = "2009"


Do


Do
month3 = Format(DateSerial(year, month2, 1), "mmm")
Set fso = CreateObject("Scripting.FileSystemObject")

Do

Do
partial = "\\cefileserver\groups\EMO\Forecasting\Elec\ShortTerm\Forecasts\Half Hourly/"
folder = partial & day2 & month3 & year3 & "_" & hour
If fso.FolderExists(folder) 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) Then
'MsgBox folder & " is a valid folder/path.", vbInformation, "Path Exists"
Else
End If
day1 = day1 - 1
Loop Until fso.FolderExists(folder) Or day1 < 1


If fso.FolderExists(folder) Then
'MsgBox folder & " is a valid folder/path.", vbInformation, "Path Exists"
Else
End If
month2 = month2 - 1
Loop Until fso.FolderExists(folder) Or month2 < 1

If fso.FolderExists(folder) Then
MsgBox folder & " is a valid folder/path.", vbInformation, "Path Exists"
Else
End If
year1 = year1 - 1
Loop Until fso.FolderExists(folder) Or year1 < 9
End Sub

[Bob Phillips]

This code seems ridiculous to me



day = Format(Date, "dd")

If day = "31" Then day1 = 31
If day = "30" Then day1 = 30
If day = "29" Then day1 = 29
If day = "28" Then day1 = 28
If day = "27" Then day1 = 27
If day = "26" Then day1 = 26
If day = "25" Then day1 = 25
If day = "24" Then day1 = 24
If day = "23" Then day1 = 23
If day = "22" Then day1 = 22
If day = "21" Then day1 = 21
If day = "20" Then day1 = 20
If day = "19" Then day1 = 19
If day = "18" Then day1 = 18
If day = "17" Then day1 = 17
If day = "16" Then day1 = 16
If day = "15" Then day1 = 15
If day = "14" Then day1 = 14
If day = "13" Then day1 = 13
If day = "12" Then day1 = 12
If day = "11" Then day1 = 11
If day = "10" Then day1 = 10
If day = "09" Then day1 = 9
If day = "08" Then day1 = 8
If day = "07" Then day1 = 7
If day = "06" Then day1 = 6
If day = "05" Then day1 = 5
If day = "04" Then day1 = 4
If day = "03" Then day1 = 3
If day = "02" Then day1 = 2
If day = "01" Then day1 = 1



If day1 = 31 Then day2 = "31"
If day1 = 30 Then day2 = "30"
If day1 = 29 Then day2 = "29"
If day1 = 28 Then day2 = "28"
If day1 = 27 Then day2 = "27"
If day1 = 26 Then day2 = "26"
If day1 = 25 Then day2 = "25"
If day1 = 24 Then day2 = "24"
If day1 = 23 Then day2 = "23"
If day1 = 22 Then day2 = "22"
If day1 = 21 Then day2 = "21"
If day1 = 20 Then day2 = "20"
If day1 = 19 Then day2 = "19"
If day1 = 18 Then day2 = "18"
If day1 = 17 Then day2 = "17"
If day1 = 16 Then day2 = "16"
If day1 = 15 Then day2 = "15"
If day1 = 14 Then day2 = "14"
If day1 = 13 Then day2 = "13"
If day1 = 12 Then day2 = "12"
If day1 = 11 Then day2 = "11"
If day1 = 10 Then day2 = "10"
If day1 = 9 Then day2 = "09"
If day1 = 8 Then day2 = "08"
If day1 = 7 Then day2 = "07"
If day1 = 6 Then day2 = "06"
If day1 = 5 Then day2 = "05"
If day1 = 4 Then day2 = "04"
If day1 = 3 Then day2 = "03"
If day1 = 2 Then day2 = "02"
If day1 = 1 Then day2 = "01"


You get a 2 character string day number, which you convert to a numeric day using a lot of IFs, and then convert that numeric day number back to a 2 charatcer string day number using a lot more IFs. Why not just use the first value of day?

BTW, using variables called Day, month, year is not a good idea.

[Bob Phillips]

Ditto, the code for year.

[rossmiddleto]

Yeah I though that seemes a bit stupid but as I havent touched VBA until two days ago I dont know any other way.

The point of all those if statements is becasue I needed to use an integer to control the do loop but cannot use this integer as the variable that goes into the folder name as excel drops the leading zero from day numebrs like 08 when I need them to be 08 as the folder name will be in the following format

08DDDYYYY_HH

where 08 is the day (between 01 and 31)

Any ideas??

[Simon Lloyd]

Can you right click the folder/file and choose properties and then copy the target address and supply the filename so we can look at the construction?Firstly please use the VBA button to wrap your code just as we have done as it makes the code easier to read, secondly can you perform the above and post the results?

[Bob Phillips]

Yeah I though that seemes a bit stupid but as I havent touched VBA until two days ago I dont know any other way.

The point of all those if statements is becasue I needed to use an integer to control the do loop but cannot use this integer as the variable that goes into the folder name as excel drops the leading zero from day numebrs like 08 when I need them to be 08 as the folder name will be in the following format

08DDDYYYY_HH

where 08 is the day (between 01 and 31)

Any ideas??

Just format the loop indexes with in the loops to get the string value, like



Format(day1,"00") & Month3 & Format(year,"0000") & "_" & Format(hour,"00")