Trying to Create Folder and Save to Folder based on cell values

[DeeMan]

Hello,

I am trying to create a folder in the existing (same as current workbook) directory. I want this folder to be named according to a cell reference. I am then trying to save the workbook to the directory. When saving, I want the filename to be based off cell references as well. Currently, this code will simply save the file to the current directory and also create a folder. But it won't save the file to the folder.

Any help is appreciated. I know this should be fairly simple in VBA, but I'm a total newbie. I believe the issue to be related to the .SaveAs portion in the bottom.

Sub save()

Dim Path As String
Dim filename As String
Dim filename2 As String
Dim filename3 As String
Dim filename4 As String
Dim fullname As String
Dim fldr As String

fldr = Range("C5")
MkDir ("U:\Personal Folders\Current\DMee\! Costs Sheets\fldr")

Path = "U:\Personal Folders\Current\DMee\! Costs Sheets\fldr"
filename = Range("C9") & "__"
filename2 = Range("C4") & "__"
filename3 = Range("C6") & "ft release" & "__"
filename4 = Range("C5")
fullname = filename + filename2 + filename3 + filename4
ActiveWorkbook.SaveAs filename:=Path & fullname, FileFormat:=xlNormal

End Sub

 

[Candyman8019]

try changing this line:

ActiveWorkbook.SaveAs filename:=Path & fullname, FileFormat:=xlNormal

to

ActiveWorkbook.SaveAs filename:=Path & "\" & fullname, FileFormat:=xlNormal

 

[DeeMan]

Solved. Thanks Candyman. I actually think I had that earlier from watching a video, but forgot the additional & after the "\" . Now, is that needed because before it didn't have the slash to indicate a folder (or something..?)

Thanks! -Deeman

 

[Candyman8019]

Yeah, your path doesn't end with a slash so you need to manually put it in there. for example, if you added this line before you save, you'll see that the filename and path are not separated.

msgbox filename

alternatively, you could add the \ to your path value above...

 

[DeeMan]

Thanks again. I did notice that this is creating a folder named fldr. I assume it is the way I have done the mkdir command. But not sure how to modify. Any suggestions?

 

[Candyman8019]

Yes...simply remove the fldr from your mkdir command... You'll also then need to remove it from your path.

 

[DeeMan]

Yes...simply remove the fldr from your mkdir command... You'll also then need to remove it from your path.

If instead I'd like to reference a cell, do I need to add range("Cell#") into the mkdir command? Or would I first need to define that as string, and call the variable?

 

[Candyman8019]

It would be something like:

MkDir ("U:\Personal Folders\Current\DMee\! Costs Sheets\" & sheet1.range("A19").value)

 

[DeeMan]

OK - that worked. You rock!