Does a specific file exist in a specific folder

Looking for some vba that will look for a specific file in a specific location and if it does not exist....  create the required folders and file.  It is a .xls file.   say we use,  C:\folder1\folder2\ThisIsTheFile.xls    If it does exist....  will PartyOn

It is a static file and will always need to exist on a users device in order for a process I am using in a procedure.  Plan is to put this in the WorkbookOpen so it checks each time the file is opened.  Thought that someone has probably done this before.  Please advise and thanks.
RWayneHAsked:
Who is Participating?
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.

byundtMechanical EngineerCommented:
Here is code that will test whether a workbook exists in a particular folder. It will create that workbook if necessary.

Note that the FileFormat specification must match the workbook file extension. You asked for a .xls file, so that corresponds to FileFormat 56.
Private Sub Workbook_Open()
Dim s As String, sFile As String, sPath As String
Dim wb As Workbook
sPath = "C:\folder1\folder2\"
sFile = "ThisIsTheFile.xls"
s = Dir(sPath & sFile)
If s = "" Then
    Application.ScreenUpdating = False
    Set wb = Workbooks.Add
    wb.SaveAs sPath & sFile, FileFormat:=56     'The 56 matches a .xls file extension
    wb.Close savechanges:=False
End If
End Sub

Open in new window

0
RWayneHAuthor Commented:
Thanks...  what is the format of a .xlsx  ???  in case we need to use one of those?
0
RWayneHAuthor Commented:
and it failed on the following line.

 wb.SaveAs sPath & sFile, FileFormat:=56     'The 56 matches a .xls file extension
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

byundtMechanical EngineerCommented:
You absolutely need to look up the FileFormat value when you change the file extension. For a .xlsx file extension, the FileFormat is 51.

/Rant
Microsoft could have made it easy for us by putting the file extensions in the on-line Help for the xlFileFormat enumeration https://msdn.microsoft.com/en-us/library/office/ff198017.aspx  Unfortunately, they did not. So you need to know that an "Open XML Workbook" is a .xlsx, and it needs a 51.
/EndRant

You will note that I did not use the xlOpenXMLWorkbook named constant in my suggested code. This is because an earlier version of Excel may not have that named constant, and you will get a Compilation error. By using 56, you avoid the error.
0
RWayneHAuthor Commented:
Not following this.. the 51 and the 56 are failing for me.  I used to use:

ActiveWorkbook.SaveAs Filename:=myFileNameO, FileFormat:= _

      xlOpenXMLWorkbook, CreateBackup:=False

But not sure about how that would fit into your code.  I do remember at times chg'ing the xlOpenXMLWorkbook to other formats.

Why would the 51 and the 56 be failing in Office 2013?
0
byundtMechanical EngineerCommented:
Does the folder C:\folder1\folder2\ exist?

Could you please post the entire macro that you are using? I tested the code before posting it, and I use Excel 2013.
0
byundtMechanical EngineerCommented:
I added a test for folder existence. If it doesn't exist, then it will be created.
Private Sub Workbook_Open()
Dim s As String, sFile As String, sPath As String
Dim wb As Workbook
sPath = "C:\folder1\folder2\"
sFile = "ThisIsTheFile.xlsx"
FolderExist sPath
s = Dir(sPath & sFile)
If s = "" Then
    Application.ScreenUpdating = False
    Set wb = Workbooks.Add
    wb.SaveAs sPath & sFile, FileFormat:=51     'The 51 matches a .xlsx file extension
    wb.Close savechanges:=False
End If
End Sub

Sub FolderExist(sFullPath As String)
Dim i As Long, n As Long
Dim s As String, sPath As String
Dim v As Variant
v = Split(sFullPath, Application.PathSeparator)
n = UBound(v)
For i = 0 To n
    sPath = sPath & v(i)
    s = Dir(sPath, vbDirectory)
    If s = "" Then MkDir sPath
    sPath = sPath & Application.PathSeparator
Next
End Sub

Open in new window

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
RWayneHAuthor Commented:
Excellent!!  Thanks for the help.
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
Microsoft Excel

From novice to tech pro — start learning today.

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.