Workbook SaveAs using Input Box to name file gives Error 1004

[sloshpuppy]

Hi,

This may sound a stupid question but I am struggling to make this piece of code to suit my requirement

The objective

I require a macro assigned to a command button on a userform that when pressed will save the active workbook to a directory (the directory is hardcode in) and the user uses an input box to name the file. Simple right ?

Maybe its that Friday feeling but as simple as this may seem I haven't been able to find a solution

This is what I have managed to muster so far

Sub Save_File_Name()
 Dim fname, fpath
     fname = InputBox("enter a name")
     fname = fname & ".xlsm"
     fpath = "K:\BP DATA\SALES PRODUCTS\SALES\2018\Data\"
     ActiveWorkbook.SaveAs fpath & fname
 End Sub

The code runs put fails on the:-

Run-time error '1004'
Method 'SaveAs' of object'_workbook' failed


Any ideas guys ?

Thanks


Slosh

[6StringJazzer]

This code works for me when I use a path that exists on my machine. I suspect that your path doesn't exist on your machine. Copy the path from your code and paste into the Windows Explorer folder box and see what you get.

[sloshpuppy]

Hi Jeff,

The path exists fine, I doubled checked that before handwriting it into the code.

Basically I have a master workbook which I don't want to overwrite. A user will input data into the relevant sheets using a VBA Userform then save the workbook as a new name file. Predominately using the current date as the file name format (hence the input box requirement). This is executed by using a command button on the Userform. I'm controlling all workbook functions via the Userform.

Alternatively, if there is somehow a way this could be hard written into the code to remove the need for an input box and save to the designated directory, then this will suffice as well.

Slosh

[6StringJazzer]

To use the current date as a file name:

Sub Save_File_Name()
 Dim fname, fpath
     fname = Format(Date, "yyyy-mm-dd")
     fname = fname & ".xlsm"
     fpath = "K:\BP DATA\SALES PRODUCTS\SALES\2018\Data\"
     ActiveWorkbook.SaveAs fpath & fname
 End Sub

However, that doesn't address the error. It didn't occur to me that you might be typing illegal characters into the input box. If you are typing "5/10/2018" then that will cause an error because "/" is not allowed as a file name character and will cause VBA to look for a folder under Data called 5\10 which of course does not exist.

[nigelog]

Id always use time as well as especially when setting up errors can occur if the filename exists

Format(Now, "dd-mmm-yy" & "-" & "h-mm-ss")

[nigelog]

Try

 Dim fname, fpath As String
 fname = ActiveSheet.Name & " " & Format(Now, "ddmmmyy" & " " & "hmmss")
     fname = fname & ".xlsm"
    fpath = Environ$("temp") & "\"
     With ActiveWorkbook
     .SaveAs fpath & fname
     End With