File Exists in C:\Folder1 or not...

This partial code downloads email attachements and saves using:

att.SaveAsFile "C:\Folder1\" & att.FileName

If there were more than one identical file names like:

image001.gif
image001.gif
image001.gif
image001.gif

I want it to save like

image001.gif
image001_1.gif                  
image001_2.gif
image001_3.gif
etc.

Question: How can I find whether a file exist or not bfore saving it in the directory. If it exists, endings like _1, _2, ect. has to be added to them.

Thank you
For Each att In mailitem.Attachments
   att.SaveAsFile fnFolderFileName("C:\Folder1\", att.FileName) 
Next
'-----------------------------
Function fnFolderFileName(strFolder as string, strFile as string)As String
  Dim strTemp as string
  If <wo to discover to see if file strFile exists in strFolder> = true Then
        strTemp ="how to add _1, _2, etc. to the end of existing strFile s?" 
  Else
        strTemp = strFolder & "\" & strFile 
  End IF
  fnFolderFileName = strTemp 
End Function

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Mike,

Check out the Dir() function...
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
there should be some looping to discover all. I was think about Dir() but wasn't sure how to apply it.
0
Rey Obrero (Capricorn1)Commented:


if Dir("C:\Folder1\" & att.FileName)<>"" then
        ' save with _1
        else
       'save normal
end if
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbizupCommented:
Mike,
 so if the file exists, you want to check for the next _n extension?

The loop would look something like this:

Dim isThere as boolean
isthere = (isNull(dir("C:\x.txt")) = False)
Do Until isThere = False
     n= n+ 1
     isthere = (isNull(dir("C:\x_" & n & ".txt")) = False)
Loop


After the loop is done,    C:\x_" & n & ".txt"  will be the file with the Nth number (that doesn't yet exist)


   
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
image001.gif
image001.gif
image001.gif
image001.gif

I think, following format maybe necessary.

image001Copy_01.gif                  
image001Copy_02.gif
image001Copy_03.gif
image001Copy_04.gif

Reading your posts now.

Thanks,

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
This is so far I have dvanced it:
Function fnFileName(strFolder As String, strFile As String) As String
 
  Dim strTemp As String
  Dim intMax As Integer
  Dim bolFileExists As Boolean
  
  'using capricorn1 code, chek if the file alrady exists
  If Dir(strFolder & "\" & strFile) <> "" Then bolFileExists = True
  
  If bolFileExists Then
  'now we have to handle it
  
    If InStr(strFile, "Copy") > 0 Then
    ' there is Copy_01 or Copy_02, etc. already present
  
     ' I need to incremet the numbers here <<<<<<*****
    Else
    
        'remove file extention (.pdf, .doc, etc.)
        strTemp = Replace(strFile, Right(strFile, 4), "")
       
        'add first Copy_01 to the file name
        strTemp = strTemp & "Copy_01" & Right(strFile, 4)
    End If
    
  Else
  'return the file name as it was, it dosn't
  'exist yet in the directory
    strTemp = strFile
  End If
  
  fnFileName = strTemp
  
End Function

Open in new window

0
mbizupCommented:
Dim isThere as boolean
isthere = (isNull(dir("image001.gif")) = False)
Do Until isThere = False
     n= n+ 1
     isthere = (isNull(dir("image001_copy" & format(n,"00")  & ".gif")) = False)
Loop
msgbox "Your next filename is: " & image001_copy" & format(n,"00")  & ".gif"
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
mbizup,

Your intial comment: Check out the Dir() function... got me started. My final work is attached below. I am testing it right now,

brb
Function fnFileName(strFolder As String, strFile As String) As String
 
  Dim strTemp As String
  Dim str_Temp As String
  Dim intMax As Integer
  Dim bolFileExists As Boolean
  Dim i As Integer
  
  'using capricorn1 code, chek if the file alrady exists
  If Dir(strFolder & "\" & strFile) <> "" Then bolFileExists = True
  
  If bolFileExists Then
  'now we have to handle it
  
    If InStr(strFile, "Copy") > 0 Then
    ' there is Copy_01 or Copy_02, etc. already present
        
        'remove file extention .pdf, .doc, etc.
        strTemp = Replace(strFile, Right(strFile, 4), "")
        
        'build prefix like "image0001Copy_"
        str_Temp = Left(strTemp, InStr(strTemp, "Copy") + 4)
        
        For i = 2 To 99 'max 99 identical name
            
            If Dir(strFolder & "\" & str_Temp & Format(i, "00") & Right(strFile, 4)) <> "" Then
                intMax = i
            Else
                GoTo 10
            End If
        Next i
     ' I need to incremet the numbers here
    Else
    
        'remove file extention (.pdf, .doc, etc.)
        strTemp = Replace(strFile, Right(strFile, 4), "")
       
        'add first Copy_01 to the file name
        strTemp = strTemp & "Copy_01" & Right(strFile, 4)
    End If
    
  Else
  'return the file name as it was, it dosn't
  'exist yet in the directory
    strTemp = strFile
  End If
  
10:
  
  If intMax > 1 Then
    intMax = intMax + 1
    strTemp = str_Temp & Format(intMax, "00") & Right(strFile, 4)
  End If
  
  fnFileName = strTemp
  
End Function

Open in new window

0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Final verion works:
Function fnFileName(strFolder As String, strFile As String) As String
 
  Dim strTemp As String
  Dim str_Temp As String
  Dim str_File As String
  Dim intMax As Integer
  Dim bolFileExists As Boolean
  Dim i As Integer
  
  intMax = 1
  'using capricorn1 code, chek if the file alrady exists
  If Dir(strFolder & strFile) <> "" Then bolFileExists = True
  
  If bolFileExists Then
  'now we have to handle it
  strTemp = Replace(strFile, Right(strFile, 4), "")
    'if...Copy_01.pdf version exists
    str_File = Dir(strFolder & strTemp & "Copy_01" & Right(strFile, 4))
    If str_File <> "" Then
    ' build versions Copy_02 or Copy_03, etc.
        
        'remove file extention .pdf, .doc, etc.
        'strTemp = Replace(strFile, Right(strFile, 4), "")
        
        'build prefix like "image0001Copy_"
        str_Temp = Left(str_File, InStr(str_File, "Copy") + 4)
        
        For i = 1 To 99 'max 99 identical name
            
            If Dir(strFolder & "\" & str_Temp & Format(i, "00") & Right(strFile, 4)) <> "" Then
                intMax = i
            Else
                GoTo 10
            End If
        Next i
     ' I need to incremet the numbers here
    Else
    
        'remove file extention (.pdf, .doc, etc.)
        strTemp = Replace(strFile, Right(strFile, 4), "")
       
        'add first Copy_01 to the file name
        strTemp = strTemp & "Copy_01" & Right(strFile, 4)
    End If
    
  Else
  'return the file name as it was, it dosn't
  'exist yet in the directory
    strTemp = strFile
  End If
  
10:
  
  If intMax > 0 Then
    intMax = intMax + 1
    strTemp = str_Temp & Format(intMax, "00") & Right(strFile, 4)
  End If
  
  fnFileName = strTemp
  
End Function

Open in new window

0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I will close this question later tonight.

Thanks,

Mike
0
Rey Obrero (Capricorn1)Commented:
try this

Function fnFolderFileName(strFolder As String, strFile As String) As String
Dim tmpFile As String, j As Integer, xFile, ext
ext = Right(strFile, 4)
If Dir(strFolder & strFile) <> "" Then
    j = 1
    tmpFile = Left(strFile, InStr(strFile, ".") - 1) & "Copy" & j & ext
    xFile = Dir(strFolder & "*copy*" & ext)
    While xFile = tmpFile
        j = j + 1
        tmpFile = Left(strFile, InStr(strFile, ".") - 1) & "Copy" & j & ext
        xFile = Dir
    Wend
    fnFolderFileName = tmpFile
    Else
    fnFolderFileName = strFile
End If
End Function


0
Rey Obrero (Capricorn1)Commented:

better to use this

Function fnFolderFileName(strFolder As String, strFile As String) As String
Dim tmpFile As String, j As Integer, xFile, ext, fName
fName = Left(strFile, InStr(strFile, ".") - 1): ext = Right(strFile, 4)
If Dir(strFolder & strFile) <> "" Then
    j = 1
    tmpFile = fName & "Copy" & j & ext
    xFile = Dir(strFolder & fName & "copy*" & ext)
    While xFile = tmpFile
        j = j + 1
        tmpFile = fName & "Copy" & j & ext
        xFile = Dir
    Wend
    fnFolderFileName = strFolder & tmpFile
    Else
    fnFolderFileName = strFolder & strFile
End If
End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I will try it tomarrow,

Thanks,

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
capricorn1,

I am beginig to think you are a better coder than I am (LOL).

Few notes before discussing slight problem I am having with your lates code:

1) Function fnFolderFileName(...) has been renamed to fnFileName(..) because now it is returning file name only, as you see below.

2) The Word "Copy" has been replaced with an acronym "_APOS" becuase the word "Copy" maybe included with the file name.

There is attachement with few emails called "rErrors.rtf" and the code below delivers them as:

rErrors_APOS7_APOS1.rtf
rErrors_APOS6.rtf
rErrors_APOS5.rtf
rErrors_APOS4.rtf
rErrors_APOS3.rtf
rErrors_APOS2.rtf
rErrors_APOS1.rtf
rErrors.rtf

Function fnFileName(strFolder As String, strFile As String) As String
Dim tmpFile As String, j As Integer, xFile, ext, fName
fName = Left(strFile, InStr(strFile, ".") - 1): ext = Right(strFile, 4)
If Dir(strFolder & strFile) <> "" Then
    j = 1
    tmpFile = fName & "_APOS" & j & ext
    xFile = Dir(strFolder & fName & "_APOS*" & ext)
    While xFile = tmpFile
        j = j + 1
        tmpFile = fName & "_APOS" & j & ext
        xFile = Dir
    Wend
    fnFileName = tmpFile
    Else
    fnFileName = strFile
End If
End Function

If I could, I am trying to debug it to deliver like:

rErrors_APOS8.rtf
rErrors_APOS7.rtf
rErrors_APOS6.rtf
rErrors_APOS5.rtf
rErrors_APOS4.rtf
rErrors_APOS3.rtf
rErrors_APOS2.rtf
rErrors_APOS1.rtf
rErrors.rtf

Do you see what the proplem is?

Thanks,

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I did step through:

In the case of "image001.gif", it returns:

image001.gif
image001_APOS1.gif
.
.
image001_APOS13.gif

without any problem. But in the case of "rErrors.rtf", I am getting:

rErrors.rtf
rErrors_APOS1.rtf
.
.
rErrors_APOS7.rtf
rErrors_APOS7_APOS1.rtf

I will count the number of attachements for both of these files to QC them further. But despit all these, as long as thos number match, I will be fine.

brb.

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I have 23 copy of "image001.gif", but it reports back only:

image001.gif
image001_APOS1.gif
.
.
image001_APOS13.gif

image001_APOS14.gif    \
.                                       |   <-- 8 missing.
.                                       |
image001_APOS22.gif  /

Also I have 16 copy of "rErrors.rtf", but it reports back only:

rErrors.rtf
rErrors_APOS1.rtf
.
.
rErrors_APOS7.rtf
rErrors_APOS7_APOS1.rtf

rErrors_APOS9.rtf     \
.                                 |  <-- 7 missing
.                                 |
rErrors_APOS15.rtf    /

Another part of the code calling fnFileName() records these file names in a table using:

                    For Each att In mailitem.Attachments
                        ' after resetting public intGif =0 and intRtf =0 at the begining: 
                        If att.FileName = "image001.gif" Then intGif = intGif + 1
                        If att.FileName = "rErrors.rtf" Then intRtf = intRtf + 1
                        strAttName = att.FileName
                        FileExt = LCase(Mid(strAttName, InStrRev(strAttName, ".") + 1))
                        strAttName = fnFileName("C:\TestFolder\", strAttName)
                        att.SaveAsFile "C:\TestFolder\" & strAttName
                        CurrentDb.Execute "Insert Into tAttachFile(Email_ID, FileName, AttchType, FileExt) Values(" & lngEmailID & ", '" & _
                                           strAttName & "', '" & att.Type & "', '" & FileExt & "')"
                    Next
 
And the result in table for these two files "tAttachFile" is the same as what your code produces and this is a good news.
 
The reason why the numbers do not match, I think, it is because a forwarded email (with attachement) may show it has attachement but it doesn't hence the confusion. I hope this made sence.
 
As it stands we are okay with what we have. but it would be nice to have:
 
.
.
rErrors_APOS7.rtf
rErrors_APOS8.rtf
 
not
 
.
.
rErrors_APOS7.rtf
rErrors_APOS7_APOS1.rtf
 
 
Thanks,
 
Mike

Open in new window

0
mbizupCommented:
Mike,

Did you try my suggested air-code at all?   This contains a correction for handling the results of the Dir function:

Function foo2()
Dim isThere As Boolean
Dim n As Integer
isThere = (Nz(Dir("c:\x.txt"), "") <> "")
Do Until isThere = False
     n = n + 1
     isThere = (Nz(Dir("c:\x_" & Format(n, "000") & ".txt"), "") <> "")
Loop
MsgBox "Your next filename is: c:\x_" & Format(n, "000") & ".txt"
End Function

I may be missing the point of your question, but the numbering in the above code works consecutively (nothing skipped).  Apart from skipping that suggestion in the point split, you have not given me any feedback about the code, so I'm not sure what your reasons were for dismissing it.
 
This was a start as a test... you can work in generic filenames and extensions as needed.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi mbizup,

re:>Did you try my suggested air-code at all?

I tried your earlier post stating: "Check out the Dir() function..."

After that I was heavily into my own solution. The solution from Ray looked simple and it works.

Next, I am going to try your good solution because it is even more simple compared to the others.

Thank you for the input.

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi mbizup,

I am not sure capricorn1 is a better coder than me; but for sure you are better then both of us(LOL). Your code improved a bit and was very simple. The result I got was:


rErrors.rtf
rErrors_Copy001.rtf
.
.
rErrors_Copy007.rtf
rErrors_Copy007_Copy001.rtf

I don't know why is is not able to produce:

rErrors_Copy008.rtf instead.   '<-- I had go back to thw word "Copy" again.

Here is your code a bit revised:
Function fnFileName(strFolder As String, strFile As String) As String
 
    Dim isThere As Boolean
    Dim ext, fName
    fName = Left(strFile, InStr(strFile, ".") - 1): ext = Right(strFile, 4)
    
    Dim n As Integer
    isThere = (Nz(Dir(strFolder & strFile), "") <> "")
    Do Until isThere = False
         n = n + 1
         isThere = (Nz(Dir(strFolder & fName & "_Copy" & Format(n, "000") & ext), "") <> "")
    Loop
    
    If n > 0 Then
        fnFileName = fName & "_Copy" & Format(n, "000") & ext
    Else
        fnFileName = strFile
    End If
 
End Function

Open in new window

MuliCopyFiles.bmp
0
mbizupCommented:
Mike,

How are you calling the function?
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
like:

                   For Each att In mailitem.Attachments
                        ' after resetting public intGif =0 and intRtf =0 at the begining:
                        If att.FileName = "image001.gif" Then intGif = intGif + 1
                        If att.FileName = "rErrors.rtf" Then intRtf = intRtf + 1
                        strAttName = att.FileName
                        FileExt = LCase(Mid(strAttName, InStrRev(strAttName, ".") + 1))
                        strAttName = fnFileName("C:\TestFolder\", strAttName)
                        att.SaveAsFile "C:\TestFolder\" & strAttName
                        CurrentDb.Execute "Insert Into tAttachFile(Email_ID, FileName, AttchType, FileExt) Values(" & lngEmailID & ", '" & _
                                           strAttName & "', '" & att.Type & "', '" & FileExt & "')"
                    Next
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
in short:

strAttName = fnFileName("C:\TestFolder\", strAttName)
0
mbizupCommented:
Mike,

You need to pass the "root" of the filename (without the copy number) to the dir statement.  This assumes that the first file is saved as image001 and subsequent files are saved as image001_copy001 , etc.

Try revising your code like this:
> fName = Left(strFile, InStr(strFile, ".") - 1): ext = Right(strFile, 4)

Substitute these lines to get fName  (check this in the debugger to make sure you are getting the root of the filename without the _copy001:

dim intCount as integer
ext = Right(strFile, 4)
intCount = InStr(strFile, "_copy")
If intCount > 0 then
  fname = Left(strFile, intCount - 1)
Else
  fName = Left(strFile, InStr(strFile, ".") - 1)      
End If


0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thanks mbizup; will do it tomarrow.

Thank you for the follow-up.

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi mbizup,

The incoming strFile (file names directly read from the emails) will not have _Copy ending ever). So I will not try the latest based on this fact.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.