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?

 

[mole999]

difficult to say without the rest of the macro, all you are showing here is what happens if something else isn't viable

 

[WarPigl3t]

mole is right. but I'm going to take a wild guess and say you already have the file open and you are trying to run the macro to open it while it is already open. That may just open up two instances of it, or it may bring an error. who knows. tough to say without the rest of your macro.

 

[Pete81]

Hi both,

I've been working a little more since then and now I have a different error, which I've decided to call progress.

Here is the entire argument

Sub CreateOrOpen()


If Len(Dir("C:\Folders\" & Range("A1").Text & "\" & Range("A1").Text)) = 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

The code still creates a new file in the correct format (.xlsm) without opening it, which is what I want.

But if the file already exists, I want it to be opened.

What's happening instead is that, if the file already exists, Excel is opening a new book and asking me if I want to save over the existing file, which is not what I want.

Any ideas?

 

[WarPigl3t]

I'm not sure to be honest. I'm just going to throw a wild guess and say it has something to do with the open file code where you wrote .Text instead of .Value

I also noticed you did the .Text in other places of your code. Pretty sure it should be .Value

 

[Pete81]

I'm not sure to be honest. I'm just going to throw a wild guess and say it has something to do with the open file code where you wrote .Text instead of .Value

I also noticed you did the .Text in other places of your code. Pretty sure it should be .Value

Done. No change I'm afraid. Same issue.

 

[WarPigl3t]

Also try changing you If statement to this...
IF Dir("C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value) = "" Then

 

[Pete81]

Also try changing you If statement to this...
IF Dir("C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value) = ""Then

Again, no change, although thanks for pointing out I don't need LEN() in there.

I'm not crazy though, right? This should work, shouldn't it?

 

[WarPigl3t]

I don't know what is wrong and you are not crazy. As for as I can tell, it should work. Put this test line of code on top of the if statement like this.

myTestString = "C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value
myTestDir = Dir("C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value)

IF Dir("C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value) = "" Then

Do you know how to run the code line by line? The command to run the code line by line is F10 I believe. Run the code line by line and see what values those tests come up with and verify they are what they should be. That's all the help I'll be able to be.

 

[Pete81]

I don't know what is wrong and you are not crazy. As for as I can tell, it should work. Put this test line of code on top of the if statement like this.

myTestString = "C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value
myTestDir = Dir("C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value)

IF Dir("C:\Folders\" & Range("A1").Value & "\" & Range("A1").Value) = "" Then

Do you know how to run the code line by line? The command to run the code line by line is F10 I believe. Run the code line by line and see what values those tests come up with and verify they are what they should be. That's all the help I'll be able to be.

If it's what I think you mean, then it's F8. I ran through it from scratch. Created and renamed the new file no problem. On running through again in order to open the existing file, got the same error. The debug highlighted the Save As line in the first part of the argument. Does this mean that the part before is the issue? I don't understand how that can be the case.

Also, I'm not entirely sure what the myTest code you gave me does. I added it in, but did not see any change in the macro.

That being said, thank you for your help and time on this.