Opening a File with a Variable as a Filepath and Filename

[Pete81]

I have some code which will create and save a new file in to a folder named after the value in cell A1, and also name the file after the value in cell A1 (e.g. C:\Folders\test\test.xlsm, where "test" is the value in A1). This is if the file does not already exist, so I have set this code up as an IF function. If it file already exists, then I want the code simply to open it. My code for that part is here:

Else
Workbooks.Open Filename:="C:\Folders\" & Range("A1").Text & "\" & Range("A1").Text

When I run this macro for a new file, one is created exactly where I need it and in the correct format. However, when I run the macro again, I receive this error:

"Run-time error '1004':
Sorry, we couldn't find C:\Folders\. Is it possible it was moved, renamed or deleted?"

The way in which I have structured the filename above in the code for opening the file is the same structure I have used for creating a new one. What am I doing wrong here?

 

[WarPigl3t]

I see you have never tested code before. The test code doesn't "DO" anything. When you run through the code line by line, you will eventually reach the lines where the test code is. After it runs the test code, you simply put your cursor over the variable named myTestString. Originally myTestString was equal to "", but after the code ran it should be equal to your variable name. myTestDir should be equal to "" before it runs that line, and after it runs that line it should still be equal to "". If it equals something else which I'm pretty sure it does, we will know what the problem is. You need to find out what myTestDir equals. The problem is that for some reason, the code is never entering the "Else" part of the if statement. I believe it has something to do with whatever the results of myTestDir will give you.

 

[Pete81]

Hi again,

I've reworked the Else part of the argument and it now seems to be working, provided the file is not already open:

Sub CreateOrOpen()

If Len(Dir("C:\Folders\" & Range("A1").Text & "\" & Range("A1").Text & ".xlsm", vbDirectory)) = 0 Then
Workbooks.Add.SaveAs Filename:="C:\Folders\" & Range("A1").Text & "\" & Range("A1").Text, FileFormat:=52
Else
Workbooks.Open Filename:="C:\Folders\" & Range("A1").Text & "\" & Range("A1").Text
End If

End Sub

Thanks again for the help WarPigl3t.