How to open a file with date at the end of file name in vba

I have created a macro in which the file name shows "Recon_file_24112017.xls".

The file name will change on a daily basis like

Recon_file_25112017.xls
Recon_file_26112017.xls

Can anyone help me to define a code in the macro to avoid the error that the file name is not found.

The code defined in the macro is
Workbooks.Open Filename:="F:\Transfer\Recon_file_24112017.xls"
Rajesh ChandrasekarAsked:
Who is Participating?
 
Fabrice LambertFabrice LambertCommented:
@Aleksandr M.
The dir() function hold its criteria in a global scope, so it is not reliable to check for the existance of files (or directories).
Better use FSO:
Function FileExists(ByVal strPath As String) As Boolean
On Error goto Error
    Dim fso As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    FileExists = fso.FileExists(strPath)
    Set fso = Nothing
Exit Function
Error:
    If Not(fso Is Nothing) Then
        Set fso = Nothing
    End If
    err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
End Function

Open in new window

And better never ignore exception errors.
0
 
Fabrice LambertFabrice LambertCommented:
Hi,

use the format  functions to format the date, and concatenate in a string:
Public function getFileName() As String
    getFileName = "Recon_file_" & format(now, "ddmmyyyy") & ".xls"
End Function

Open in new window

0
 
AlanConsultantCommented:
Hi,

Try this:

Workbooks.Open Filename:="F:\Transfer\Recon_File_" & Format(Now, "DDMMYYYY") & ".xls"

Open in new window



Alan.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aleksandr M.EconomistCommented:
Hello,
help me to define a code in the macro to avoid the error that the file name is not found
After you define the file name you want to check for existance, you can run the following function (see below). It takes Full path to a file and returns True if file exists, or False in the opposite case.
Function FileExists(ByVal strPath As String) As Boolean
'********************************
'Purpose:       Function checks if a spesified string is a valid file path
'Arguments:     strPath - string variable assuming to be a path
'Result:        True (if file path is valid)/False (if file path is not valid).
'********************************
FileExists = False
On Error GoTo FuncExitPoint
If Len(Dir(strPath)) > 0 Then
    FileExists = True
End If
FuncExitPoint:
On Error GoTo 0
End Function

Open in new window

So, the Sub should look like something below:
Sub test()
Dim file_path as string
file_path="F:\Transfer\Recon_file_24112017.xls"
if FileExists(file_path) then
          Workbooks.Open Filename:=file_path
else:  Msgbox "File doesn't exist!"
end if
end sub

Open in new window

0
 
Aleksandr M.EconomistCommented:
@Fabrice Lambert, thanks for making some improvements to my code.
Concerning your recommendations:
1) Fortunately, I haven't encountered any problems with dir() function while checking files existance so far. Would be greatful if you point to some articles/forums discussions on this topic or give some examples to replicate the error conditions.
2) I do agree with you that a great attention is to be paid to error exceptions. Though there are some cases when to use errors catching is more convenient and don't lead to any problems while runtime etc. For example, it's easier to check for worksheet existance trying to set a worksheet object to workbook's sheet with a specific name and catch an error if it raises, rather then to loop through the entire collection of worksheets in a Workbook. At that if we ignore errors in a separate function/subroutine, errors would be visible only inside of them, but not inside a calling procedure.
0
 
Fabrice LambertFabrice LambertCommented:
Well, I don't have any articles, but calling the dir function mutliple times (without arguments) was enough to convince me of a potential problem.
What if I call a function that change Dir() criteria ?? I'll be screwed.

So, I prefer relying on reliable Tools. Better be safe than sorry.
0
 
AlanConsultantCommented:
Solutions and options provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.