Save As in Specific Directory and using Cell Range

[aldonin]

Hello all.

2 facts:

1) I've verry little VBA knowledge!
2) I've checked all threads in this forum as well as others and couldn't find a solution covering all requirements. So here it goes for the geniuses around!

I need a Macro that will do the following:

1) Save currently open Excel File As (i.e. create a new file).
2) Use the value of a specific Cell in the active sheet as FileName
3) Save the file to a specific path e.g. C:\Quotes
4) In case C:\Quotes (or whatever the path in number (3) is) does not exist, create it!

Thats it! Also I've read in some threads that I should insert this as new projects, others use a different insertion method. Which one should I use?

Thanks a lot in advance everyone!!

[Domski]

Try this:

Sub Save_File()
    Dim strPath As String
    strPath = "C:\Quotes"
    If FileFolderExists(strPath) = False Then MkDir strPath
    ActiveWorkbook.SaveAs strPath & "\" & Range("A1")
End Sub

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0
End Function

It picks the file name up from A1 on the active sheet.

Not quite sure what you mean by "I've read in some threads that I should insert this as new projects".

Dom

[aldonin]

Domski:

Right on! Thank you so much. This is like the fountain of knowledge here. Ask anything-excel and you get a fast right answer!

[aldonin]

Ok. so I ran beta testing. Works great. Have only found one error (and since it is related to that same code I'll keep using this thread).

When you run the code it saves the file correctly. When you run it again it warns that the specified filename already exists and asks to overwrite it or not.
If you click on Overwrite it does what it says. Great. If you however click on NO it gives you an error and asks to debug, etc.
I think a nice ending here would be that if you click NO it should prompt the Save As prompt and let you input an alternative filename.

Yet I'm very happy and grateful for the code. Just trying to tie up any loose endings!

Note: Same error takes place if you click on Cancel on the Existing Filename prmopt. I think if you click on Cancel it should just do nothing and make disappear all prompts.

[Domski]

Some error handling required:

Sub Save_File()
    Dim strPath As String
    strPath = "C:\Quotes"
    If FileFolderExists(strPath) = False Then MkDir strPath
    On Error GoTo ErrorHandler
    ActiveWorkbook.SaveAs strPath & "\" & Range("A1")
CleanExit:
    On Error GoTo 0
    Exit Sub
ErrorHandler:
    If Err.Number = 1004 Then
        Resume CleanExit
    Else
        MsgBox Err & " - " & Err.Description
        Resume CleanExit
    End If
End Sub

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0
End Function

Dom