Dir command not consistent

[Robert_L_Ross]

Here's my problem:

I want to evaluate the contents of 3 directories while in a macro. If the
directory doesn't exist, I want to create the directory. If there are files
in the directory, I want to delete them.

I then want to save files to the directory. I'm going to have to verify
three directories and backup directories, and place 4 new files in each, so I
have two loops, one inside the other (UNITLOOP and FILELOOP).

I first get the date of the files the user wants to process by an inputbox.
It's a string "mmddyy" (i.e. 060206). Here's the code that 'cleans' out any
files already in the directory:
+---------------------------------
'BACKUP DIRECTORY OVERWRITE PROTECTION
Do Until UNITLOOP = 4
If UNITLOOP = 1 Then
UNITFOLDER = "CS"
ElseIf UNITLOOP = 2 Then
UNITFOLDER = "REC"
ElseIf UNITLOOP = 3 Then
UNITFOLDER = "SR"
End If

DESTINATIONPATH = "G:\A F S\MONTHLY REPORTS\HIPATH\"

'REMOVE FILES IF PRESENT

If Dir(DESTINATIONPATH & UNITFOLDER & "\" & RPTRUNDATE & "\*.XLS")
<> "" Then
msg = MsgBox(DESTINATIONPATH & UNITFOLDER & "\" & RPTRUNDATE &
"\" & Chr(13) & Chr(10) & _
"already contains files. Would you like to delete these
files now?", vbYesNo, "FILE PROCESSING ERROR")
If msg = 6 Then
Kill (DESTINATIONPATH & UNITFOLDER & "\" & RPTRUNDATE &
"\*.XLS")
ElseIf msg = 7 Then
Exit Sub
End If
End If

UNITLOOP = UNITLOOP + 1

Loop
+---------------------------------

In the code above, if DIR is not null ("") it gives the user the option to
delete existing files or quit the macro.

The next segment I have is to set up new folders if they don't exist.
+---------------------------------
Do Until UNITLOOP = 4
If UNITLOOP = 1 Then
UNITFOLDER = "G:\A F S\Monthly Reports\HiPath\CS\"
FILENAME = "MONTHLYC_"
NEWDIRECTORY = UNITFOLDER & RPTRUNDATE
If Dir(NEWDIRECTORY) = "" Then
MkDir NEWDIRECTORY
End If
ElseIf UNITLOOP = 2 Then
UNITFOLDER = "G:\A F S\Monthly Reports\HiPath\REC\"
FILENAME = "MONTHLYR_"
NEWDIRECTORY = UNITFOLDER & RPTRUNDATE
If Dir(NEWDIRECTORY) = "" Then
MkDir NEWDIRECTORY
End If
ElseIf UNITLOOP = 3 Then
UNITFOLDER = "G:\A F S\Monthly Reports\HiPath\SR\"
FILENAME = "MONTHLYS_"
NEWDIRECTORY = UNITFOLDER & RPTRUNDATE
If Dir(NEWDIRECTORY) = "" Then
MkDir NEWDIRECTORY
End If
End If
+---------------------------------

This code works fine on the 'first' pass, where the "G:\A F S\Monthly
Reports\HiPath\CS\060206" directory doesn't exist. When I run it a 2nd time,
it fails at the first MkDir command.

My question is why does the following code not work consistently?

If Dir(NEWDIRECTORY) = "" Then
MkDir NEWDIRECTORY
End If

On the 'first' pass for a date, it runs perfectly. If the user goes in to
re-run that days report, or if they accidentally run the macro twice with the
same RPTRUNDATE it errors the 2nd time. The DIR statement works the first
time to tell the code the directory doesn't exist, but won't work to tell the
code the directory is already there.

What's the deal?

Thanks in advance!

[Andrew Taylor]

To detect directories with Dir() you have to use the
optional second parameter "attributes":

If Dir(NEWDIRECTORY, vbDirectory) = "" Then
MkDir NEWDIRECTORY
End If



Robert_L_Ross wrote:
> Here's my problem:
>
> I want to evaluate the contents of 3 directories while in a macro. If the
> directory doesn't exist, I want to create the directory. If there are files
> in the directory, I want to delete them.
>
> I then want to save files to the directory. I'm going to have to verify
> three directories and backup directories, and place 4 new files in each, so I
> have two loops, one inside the other (UNITLOOP and FILELOOP).
>
> I first get the date of the files the user wants to process by an inputbox.
> It's a string "mmddyy" (i.e. 060206). Here's the code that 'cleans' out any
> files already in the directory:
> +---------------------------------
> 'BACKUP DIRECTORY OVERWRITE PROTECTION
> Do Until UNITLOOP = 4
> If UNITLOOP = 1 Then
> UNITFOLDER = "CS"
> ElseIf UNITLOOP = 2 Then
> UNITFOLDER = "REC"
> ElseIf UNITLOOP = 3 Then
> UNITFOLDER = "SR"
> End If
>
> DESTINATIONPATH = "G:\A F S\MONTHLY REPORTS\HIPATH\"
>
> 'REMOVE FILES IF PRESENT
>
> If Dir(DESTINATIONPATH & UNITFOLDER & "\" & RPTRUNDATE & "\*.XLS")
> <> "" Then
> msg = MsgBox(DESTINATIONPATH & UNITFOLDER & "\" & RPTRUNDATE &
> "\" & Chr(13) & Chr(10) & _
> "already contains files. Would you like to delete these
> files now?", vbYesNo, "FILE PROCESSING ERROR")
> If msg = 6 Then
> Kill (DESTINATIONPATH & UNITFOLDER & "\" & RPTRUNDATE &
> "\*.XLS")
> ElseIf msg = 7 Then
> Exit Sub
> End If
> End If
>
> UNITLOOP = UNITLOOP + 1
>
> Loop
> +---------------------------------
>
> In the code above, if DIR is not null ("") it gives the user the option to
> delete existing files or quit the macro.
>
> The next segment I have is to set up new folders if they don't exist.
> +---------------------------------
> Do Until UNITLOOP = 4
> If UNITLOOP = 1 Then
> UNITFOLDER = "G:\A F S\Monthly Reports\HiPath\CS\"
> FILENAME = "MONTHLYC_"
> NEWDIRECTORY = UNITFOLDER & RPTRUNDATE
> If Dir(NEWDIRECTORY) = "" Then
> MkDir NEWDIRECTORY
> End If
> ElseIf UNITLOOP = 2 Then
> UNITFOLDER = "G:\A F S\Monthly Reports\HiPath\REC\"
> FILENAME = "MONTHLYR_"
> NEWDIRECTORY = UNITFOLDER & RPTRUNDATE
> If Dir(NEWDIRECTORY) = "" Then
> MkDir NEWDIRECTORY
> End If
> ElseIf UNITLOOP = 3 Then
> UNITFOLDER = "G:\A F S\Monthly Reports\HiPath\SR\"
> FILENAME = "MONTHLYS_"
> NEWDIRECTORY = UNITFOLDER & RPTRUNDATE
> If Dir(NEWDIRECTORY) = "" Then
> MkDir NEWDIRECTORY
> End If
> End If
> +---------------------------------
>
> This code works fine on the 'first' pass, where the "G:\A F S\Monthly
> Reports\HiPath\CS\060206" directory doesn't exist. When I run it a 2nd time,
> it fails at the first MkDir command.
>
> My question is why does the following code not work consistently?
>
> If Dir(NEWDIRECTORY) = "" Then
> MkDir NEWDIRECTORY
> End If
>
> On the 'first' pass for a date, it runs perfectly. If the user goes in to
> re-run that days report, or if they accidentally run the macro twice with the
> same RPTRUNDATE it errors the 2nd time. The DIR statement works the first
> time to tell the code the directory doesn't exist, but won't work to tell the
> code the directory is already there.
>
> What's the deal?
>
> Thanks in advance!