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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.