VBA Function: check last modified date of files with wildcards in name and path

jd00005

New Member
Joined
Feb 19, 2009
Messages
3
VBA Newbie here. Please forgive any protocall mistakes:

I currently use the following to test for files to exist after running a batch to copy them from various locations on a network. Works well allows wildcards in path, file name and extension.

Private Function IsFileTrue(fDriveFolder, fName, fExtension) As Boolean
'Standard module code function, like: Module1.

'Syntax: as strings or cell locations
'=IsFileTrue("C:\cp\",A2,".txt")
'Note: If using values in cells, do not use quotes in cells!

'If file is found: Returns TRUE, else FALSE.
Dim strFolderFile$, strTestWhat$

strTestWhat = fDriveFolder & fName & fExtension
strFolderFile = Dir(strTestWhat)

'To activate help input: FileExists(,"Help",)
If UCase(fName) = "HELP" Then
MsgBox " Syntax: =IsFileTrue(DriveFolder,FileName,Extention)" & vbCr & vbCr & _
"Like ==> =IsFileTrue(""C:\cp\"",A2,"".txt"")"
End If

If strFolderFile <> "" Then
IsFileTrue = True
Else

IsFileTrue = False
End If
End Function


I would really like to retrieve the last modified date of each file I test (if it exists). Most are excel files but some are .txt I tried using the following:

Function lastsaved(FileName As String)
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(FileName)
lastsaved = f.DateLastModified
End Function


This works well but does not allow wildcards in path and filename. Unfortunately I don't always know the exact path and complete filename (my batch file that does the copying uses lots of variables and wildcards).

Q. Does anyone have a bit of code I could use either as a function (prefered) or in a macro form. There are about a hundred different files for each workbook and many tens of workbooks.

Thanks any help or direction would be greatly appreciated. -JD
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top