Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / June 2006

Tip: Looking for answers? Try searching our database.

Dir command not consistent

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Robert_L_Ross - 30 Jun 2006 18:00 GMT
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 - 30 Jun 2006 22:17 GMT
To detect directories with Dir() you have to use the
optional second parameter "attributes":

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

> Here's my problem:
>
[quoted text clipped - 92 lines]
>
> Thanks in advance!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.