PDA

View Full Version : file exists or not



naveencn023
04-10-2014, 09:51 PM
Hi,

I am looking at building a macro for the below condition.. please do the needful.

I have the folder keywords in column A, folder paths in column B and i want to know if the folder with the keyword is present in that folder path and if present it should throws message as PASS or FAIL in column C and also i wanted to know the last modified date of that folder in column D.

Thanks in advance for your help...

pike
04-14-2014, 04:15 AM
this will get you started
Sub My_Newest_Files()
Dim objFSO As Object
Dim objFile As Object
Dim mfile As String
Dim mpath As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
mpath = "C:\Documents and Settings\"
mfile = Dir(mpath & "*.*")
Do While mfile <> ""
mfile = Dir
If objFSO.FileExists(mpath & mfile) Then
Set objFile = objFSO.GetFile(mpath & mfile)
If objFile.DateLastModified > Cells(1, 1).Value Then
Cells(1, 1).Value = objFile.DateLastModified
Cells(1, 2).Value = mfile
End If
End If
Loop
End Sub

naveencn023
04-14-2014, 09:40 PM
Thanks a lot for your response.. but i am new to macro and i am not getting what you explained.

So kindly provide us the code for the example mentioned below, so that it will be helpful for us..

Path File keyword file present/ file nor present last modified date
G:\Park Infra IND
F:\SEEMA Data Dec

Thanks a lot in advance..

pike
04-15-2014, 12:48 AM
I think i understand

Option Explicit
Sub My_Newest_Files()
Dim objFSO As Object
Dim objFile As Object
Dim mfile As String
Dim rngFolder As Range

Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each rngFolder In Range("A1", Cells(Rows.Count, "A").End(xlUp))
If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
If objFSO.FolderExists(rngFolder.Value) Then
mfile = Dir(rngFolder & "*" & rngFolder.Offset(, 1).Value & "*.*")
Do While mfile <> ""
mfile = Dir
If objFSO.FileExists(rngFolder.Value & mfile) Then
Set objFile = objFSO.GetFile(rngFolder & mfile)
If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
rngFolder.Offset(0, 2).Value = objFile.DateLastModified
rngFolder.Offset(0, 3).Value = mfile
End If
End If
Loop
End If
Next
Set objFSO = Nothing
Set objFile = Nothing

End Sub

naveencn023
04-15-2014, 01:27 AM
Hi,

I am unable to run the below mentioned macro.. I copied and pasted in macro and clicked run but no result...

pike
04-15-2014, 02:14 AM
hi naveencn023,

do the folders and files exist?
can you attach a small sample workbook of you layout?

pike
04-15-2014, 02:24 AM
try this it will indicate the results if folder or files dont exist

Option Explicit
Sub My_Newest_Files()
Dim objFSO As Object
Dim objFile As Object
Dim mfile As String
Dim rngFolder As Range

Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each rngFolder In Range("A1", Cells(Rows.Count, "A").End(xlUp))
If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
If objFSO.FolderExists(rngFolder.Value) Then
mfile = Dir(rngFolder & "*" & rngFolder.Offset(, 1).Value & "*.*")
Do While mfile <> ""
If mfile = "" Then
rngFolder.Offset(0, 3).Value = "No File Found"
End If
mfile = Dir
If objFSO.FileExists(rngFolder.Value & mfile) Then
Set objFile = objFSO.GetFile(rngFolder & mfile)
If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
rngFolder.Offset(0, 2).Value = objFile.DateLastModified
rngFolder.Offset(0, 3).Value = mfile
End If
End If
Loop
Else: rngFolder.Offset(0, 2).Value = "Folder does not exist"
End If

Next
Set objFSO = Nothing
Set objFile = Nothing

End Sub

naveencn023
04-15-2014, 02:49 AM
Hi,

Please find attached workbook for your kind reference.. please provide me the macro..

pike
04-15-2014, 03:25 AM
try

naveencn023
04-15-2014, 03:43 AM
Hi,

I run the macro, but if file present it is showing as blank and its not showing the last modified date of the file..

Please have a look at the attached file and advise the correct macro.

Thanks in advance..

pike
04-15-2014, 03:56 AM
try..

pike
04-15-2014, 04:19 AM
no need to down load a workbook just copy the code and paste over the existing code in the workbook module

Option Explicit
Sub My_Newest_Files()
Dim objFSO As Object
Dim objFile As Object
Dim mfile As String
Dim rngFolder As Range
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each rngFolder In Range("A2", Cells(Rows.Count, "A").End(xlUp))
If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
If objFSO.FolderExists(rngFolder.Value) Then
mfile = Dir(rngFolder & "*" & rngFolder.Offset(, 1).Value & "*.*")
If mfile = "" Then
rngFolder.Offset(0, 2).Value = "No File Found"
End If
Do While mfile <> ""
mfile = Dir
If objFSO.FileExists(rngFolder.Value & mfile) Then
Set objFile = objFSO.GetFile(rngFolder & mfile)
If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
rngFolder.Offset(0, 3).Value = objFile.DateLastModified
rngFolder.Offset(0, 2).Value = mfile
End If
End If
Loop
Else: rngFolder.Offset(0, 2).Value = "Folder does not exist"
End If
Next
Set objFSO = Nothing
Set objFile = Nothing
End Sub

naveencn023
04-15-2014, 04:34 AM
I tried again but no use.. file present is not showing and modified date also not reflecting..

pike
04-15-2014, 04:43 AM
after you run the routine/sub what results do you get?
I test an my sample data and it work for me population if the file path did not exist or if the file name could not be found

check the full paths and file names

pike
04-15-2014, 04:45 AM
Is folder key name a file? or is it a sub folder?
are you just look for folder details?
I dont follow

pike
04-15-2014, 05:00 AM
think this is it
any file in the column a path and sub folder in column B look for any file latest saved data
Option Explicit
Sub My_Newest_Files()
Dim objFSO As Object
Dim objFile As Object
Dim mfile As String
Dim rngFolder As Range
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each rngFolder In Range("A2", Cells(Rows.Count, "A").End(xlUp))
If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
If Right(rngFolder.Offset(0, 1).Value, 1) <> "\" Then rngFolder.Offset(0, 1).Value = rngFolder.Value & "\"
If objFSO.FolderExists(rngFolder.Value & rngFolder.Offset(0, 1).Value) Then
mfile = Dir(rngFolder & rngFolder.Offset(, 1).Value & "*.*")
If mfile = "" Then
rngFolder.Offset(0, 2).Value = "No File Found"
End If
Do While mfile <> ""
mfile = Dir
If objFSO.FileExists(rngFolder & rngFolder.Offset(, 1).Value & mfile) Then
Set objFile = objFSO.GetFile(rngFolder & rngFolder.Offset(, 1).Value & mfile)
If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
rngFolder.Offset(0, 3).Value = objFile.DateLastModified
rngFolder.Offset(0, 2).Value = mfile
End If
End If
Loop
Else: rngFolder.Offset(0, 2).Value = "Folder does not exist"
End If
Next
Set objFSO = Nothing
Set objFile = Nothing
End Sub