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
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