Look for a File and create one if it doesn't exist

Ive been working on a bit of VBA code for a while now but I cant seem to get it to work.
What i want tit to do is to look for a file and create one if it doesnt exist but at the same time for it to be able to be used in any file location.
this is what ive got so far:

Sub findfile()
Dim filepath As String
filepath = ThisWorkbook.Path & "\Registers\Register " & Range("C3")
If Dir(ThisWorkbook.Path & "\Registers\Register " & Range("C3")) <> "" Then
MsgBox "File exists"
Exit Sub
Else
Call AddWorkbook
End If
End Sub


Sub AddWorkbook()
Workbooks.Add
    ChDir ThisWorkbook.Path & "\Registers"
    ActiveWorkbook.SaveAs Filename:= _
        ThisWorkbook.Path & "\Registers\Register 2013.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub

Any help would be much appreciated
Thanks

Moderator's Note: You have to put code tags around your codes.Thank you.

Not entirely sure what you want to be able to do. What doesn't your routine do that you want it to do?


Regards, TMS

So, what's actually in cell C3?. You need to step through the code and see what file name is built and checked for. If it doesn't match the file name in the folder, that's your problem.

Bear in mind that only you know what data is in the cell(s).


Regards, TMS

Suggest you post a sample workbook with typical data and code.

Regards, TMS

I want the code to search and create a file called "register 2013" with 2013 coming from cell C3 and then to create an individual sheet for each other value in column C.
The problem is that I cant seem to get it to look for a file depending on "Summer Camp register editor.1.xlsm"'s current location; using ThisWorkbook.Path, so that the folder it is in can be moved
around but it will still be able to find the file "register 2013"

Thanks for your Patience Trevor

Stepping through your code ...

What you search for with Dir:

?filepath
C:\Users\......\Downloads\Registers\Register 2013

and what you then create when you find that doesn't exist:

?ThisWorkbook.Path & "\Registers\Register 2013.xlsx"
C:\Users\......\Downloads\Registers\Register 2013.xlsx

Spot the difference?


I'm guessing that you don't have Windows Explorer configured to show file extensions. If a file is shown with an Excel icon, it has been recognised as an Excel file and, for this to happen, it must have an Excel extension. So, just because a file name isn't listed with a file extension, it doesn't mean it isn't there.

When you are using Dir to check for the existence of the file, add: ' & ".xlsx"' (without the single quotes).


Regards, TMS

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.