Checking for the Existence of a File

thank a lot, but there are problem>>
assume i assigned cell A1 for the file location.
when i create a formula it works and reflect the status properly. but,
as a test i removed the file from its location to check if the excel will know the file still exist or not but it did not updated.
i should go inside A1 cell or formula cell and press enter to make the cell of formula know that the file has been removed.

Rich:

See my resources page, which is here:

http://excelribbon.tips.net/resources.html

-Allen

Besides offering your books - do you provide training. I'm looking for Advanced VBA training/mentoring?

If you do not whom would you recommend?

I see NewHorizon and www.onlc.com offer training?

Thanks
Rich S.

#NAME Problem: You have to insert this into Excel as a Function (you can't simply enter a formula as =FileExists1).

After pasting the code in VBA:
In the Ribbon, select Formulas > Insert Function > Select User Defined in the Drop down > Click OK > Select the cell you want to check (A1 for example) > Click OK

this is a great post. thanks!
-b

Hi All, I have problem in Macro created for "Copy and Rename Folder and Files Using File System Objetc (FSO)"

Below is the VBA script.. from the below script I need to change the file name. Kindly help me.

Sub Copy_and_Rename_To_New_Folder()

Dim objFSO As FileSystemObject, objFolder As Folder, PathExists As Boolean
Dim objFile As File, strSourceFolder, Mnth, Dmnth, Yr, Dyr As String, strDestFolder As String
Dim x, Counter As Integer, Overwrite As String, strNewFileName As String
Dim strName As String, strMid As String, strExt As String

Application.ScreenUpdating = False 'turn screenupdating off
Application.EnableEvents = False 'turn events off

Mnth = DatePart("m", (DateAdd("m", -1, Date))) 'Source Month
Yr = DatePart("yyyy", (DateAdd("m", -1, Date))) 'Source Year
Dyr = Year(Date) 'Desination Month
Dmnth = Month(Date) 'Destination Year

If Len(Mnth) = 1 Then Mnth = "0" & Mnth
If Len(Dmnth) = 1 Then Dmnth = "0" & Dmnth

strSourceFolder = "E:WorkWork AHEntry" & Mnth & "-" & Yr & " Entries" 'Source path
strDestFolder = "E:WorkWork AHEntry" & Dmnth & "-" & Dyr & " Entries" 'destination path

'below will verify that the specified destination path exists, or it will create it:
On Error Resume Next
x = GetAttr(strDestFolder) And 0
If Err = 0 Then 'if there is no error, continue below
PathExists = True 'if there is no error, set flag to TRUE
Overwrite = MsgBox("The folder may contain duplicate files," & vbNewLine & _
"Do you wish to overwrite existing files with same name?", vbYesNo, "Alert!")
'message to alert that you may overwrite files of the same name since folder exists
If Overwrite <> vbYes Then Exit Sub 'if the user clicks YES, then exit the routine..
Else: 'if path does NOT exist, do the next steps
PathExists = False 'set flag at false
If PathExists = False Then MkDir (strDestFolder) 'If path does not exist, make a new one
End If 'end the conditional testing

On Error GoTo ErrHandler
Set objFSO = New FileSystemObject 'creates a new File System Object reference
Set objFolder = objFSO.GetFolder(strSourceFolder) 'get the folder
Counter = 0 'set the counter at zero for counting files copied

If Not objFolder.Files.count > 0 Then GoTo NoFiles 'if no files exist in source folder "Go To" the NoFiles section

For Each objFile In objFolder.Files 'for every file in the folder...

strName = Left(objFile.Name, 5) 'Fist
strMid = Format(Now(), "mm") ' Middle
strExt = Mid(objFile.Name, 8, 50) ' Last

strNewFileName = strName & strMid & strExt 'build the string file name (can be done below as well)

objFile.Copy strDestFolder & "" & strNewFileName, False 'False = do not overwrite/ True = Overwrite if exist

'End If 'where conditional check, if applicable would be placed.

Counter = Counter + 1
Next objFile 'go to the next file

MsgBox "All " & Counter & " Files from " & vbCrLf & vbCrLf & strSourceFolder & vbNewLine & vbNewLine & _
" copied to: " & vbCrLf & vbCrLf & strDestFolder, , "Completed Transfer/Copy!"

Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects

Exit Sub

NoFiles:
'Message to alert if Source folder has no files in it to copy
MsgBox "There Are no files or documents in : " & vbNewLine & vbNewLine & _
strSourceFolder & vbNewLine & vbNewLine & "Please verify the path!", , "Alert: No Files Found!"
Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects

Application.ScreenUpdating = True 'turn screenupdating back on
Application.EnableEvents = True 'turn events back on

Exit Sub

ErrHandler:
'A general error message
MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
"Please verify that all files in the folder are not currently open," & _
"and the source directory is available"

Err.Clear 'clear the error
Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
Application.ScreenUpdating = True 'turn screenupdating back on
Application.EnableEvents = True 'turn events back on
End Sub

Typo, Thank you!

Anna,

Same deal, make sure in the Function FileExists1 that the following line:

FileExists = Dir(sPath) <> ""

says:

FileExists1 = Dir(sPath) <> ""

Also, ensure you've put this into a Module, not just the worksheet code.

Mathijs,
In the Function FileExists2, make sure the line:

FileExists = Dir(sPath) <> ""

actually says:

FileExists2 = Dir(sPath) <> ""

Just a typo in the example above.

I cannot get this to work either.

I have pasted the function in a macro-enabled workbook (tried both the data sheet and ThisWorkbook). In one column of data I have the full path, filename, and extension concatenated. In another column, I have the formula =FileExists1(E2). I get a #NAME? error.

Old thread, but if there are any good Samaritans looking to help, it would be appreciated.

I used this, thankyou, but i only get a 0 if i enter the formula. I used the user-defined function without the path. In the cell i put the formula with path + A1 etc

Can you tell me what i did wrong?