I have a good Save VBA code i use to save stuff and build folders, But it has Input Required(help)

[zone709]

This is a very good vba code to build folders and save stuff. I use it all the time, but I want to use this code on a click of a button to save it to a folder I prefer without using --> SaveName = Trim(InputBox("Enter the file name you want to save. (blank to skip)", "Input required."))

What do I need to remove or add to this code below for that to work thanks.

So basically I need to remove that and have an area in code to name sheet and just save it to a folder I name. While using this code without the Input required box. Thanks.

Sub SaveFileButton()
Dim SaveName As String
Const MyPath As String = "C:\Test\" 'This is the path it's using for saves before the dates
ReName:
On Error GoTo ErrorHandle


SaveName = Trim(InputBox("Enter the file name you want to save. (blank to skip)", "Input required."))
If Len(SaveName) > 0 Then
SaveName = SaveName & ".xlsx"
If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm"), vbDirectory)) = 0 Then
MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\"
End If
If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy"), vbDirectory)) = 0 Then
MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\"
End If
ActiveWorkbook.SaveAs Filename:=MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & _
"-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\" & SaveName, FileFormat:=xlOpenXMLWorkbook
End If

Exit Sub

ErrorHandle:
If Err.Number = 75 Then
Resume Next
ElseIf Err.Number = 1004 Then
MsgBox ("That name is already used for this day. Please try again!")
GoTo ReName
Else: MsgBox ("There is an unknown error")
End If
End Sub

 

[zone709]

I am posting my code again the right way. If you can look at it. I want to use this code adding a Filename of my choice in code. Instead of using the input required box that pops up. So I can just click a button and it saves to folder I prefer with name I prefer in code. Thanks.

Sub SaveFileButton()
Dim SaveName As String
Const MyPath As String = "C:\Submittals\"   'This is the path it's using for saves before the dates
ReName:
On Error GoTo ErrorHandle


    SaveName = Trim(InputBox("Enter the file name you want to save. (blank to skip)", "Input required."))
    If Len(SaveName) > 0 Then
        SaveName = SaveName & ".xlsx"
        If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm"), vbDirectory)) = 0 Then
            MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\"
        End If
        If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy"), vbDirectory)) = 0 Then
            MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\"
        End If
        ActiveWorkbook.SaveAs Filename:=MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & _
            "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\" & SaveName, FileFormat:=xlOpenXMLWorkbook
    End If

Exit Sub

ErrorHandle:
    If Err.Number = 75 Then
        Resume Next
    ElseIf Err.Number = 1004 Then
        MsgBox ("That name is already used for this day.  Please try again!")
        GoTo ReName
    Else: MsgBox ("There is an unknown error")
    End If
End Sub

 

[Joe4]

There are a bunch of old threads which show you how you can bring up the file browser, and select the path and filename you want to use.
Here are a few, but there are a bunch others:
http://www.mrexcel.com/forum/excel-...ave-dialog-box-visual-basic-applications.html
http://www.mrexcel.com/forum/excel-...-prompt-save-user-input-name-destination.html

See if you can incorporate that into your code, and write back if you run into difficulties.

 

[zone709]

Ok I looked, but no luck. I don't want any pop ups on this I want to remove the input required in the code and somewhere in the code. I want to add Filename and destination folder. So when I run macro the rest of the code will do the work and put file in folder.

 

[Special-K99]

What do you want it to do when you decide to save the file in a different folder?

 

[Joe4]

If you can look at it. I want to use this code adding a Filename of my choice in code. Instead of using the input required box that pops up. So I can just click a button and it saves to folder I prefer with name I prefer in code.

Sorry, I think I misread it and had it backwards.

So where exactly are these "preferences" coming from?

If it is hard-coded or calculated in the code (let's say stored in a field name "myFilePathAndName", you can remove all the input box code and simply use:

ActiveWorkbook.SaveAs Filename:=myFilePathAndName, FileFormat:=xlOpenXMLWorkbook

 

[zone709]

I just want to click macro and it saves the file without the pop up input required now.

If I have to change folder or filename. I will go into code and change it. So say if its written like C:\test\folder & "FIlename .xslm or whatever. Ill Just change it on my own when things change or use it multiple times. I tried to picker at this code to get it to work, but im getting debug. I cant seem what to remove and to add inside of code for it to work. Im not new to excel or vba. I'm more new to writing things to make it work other then recording stuff thanks.

 

[zone709]

So there must be more to remove then just the input required sentence. Anyway you can copy my original code and add in what you think will work? I still want this in there cause it builds folders and dates etc.

    If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm"), vbDirectory)) = 0 Then
            MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\"
        End If
        If Len(Dir(MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy"), vbDirectory)) = 0 Then
            MkDir MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\"
        End If
        ActiveWorkbook.SaveAs Filename:=MyPath & Format(Now, "yy") & "-" & Format$(Now, "mmm") & "\" & Format(Now, "mm") & _
            "-" & Format(Now, "dd") & "-" & Format(Now, "yyyy") & "\" & SaveName, FileFormat:=xlOpenXMLWorkbook
    End If

 

[Joe4]

Sometimes it works better to go the other way. Start with a simple example, and build upon that instead of taking a complex code and trying to whittle it down.

So if you use the advice I gave you in the last reply, you could create a really simple example, like this:

Sub MySaveFileButton()


    Dim myFilePathAndName As String
    
    myFilePathAndName = "C:\Temp\MyTest.xlsm"
    
    ActiveWorkbook.SaveAs Filename:=myFilePathAndName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
End Sub

So that simply gives you code that will save the file to a path and name you have pre-determined.

Then, you can go in and add back-in any error handling or calculations you like.

 

[zone709]

Joe this small code is great will definitely use it. So I tried to get it to work like my other code I cant. I just don't know what im doing wrong. I want it like you gave me, but I want to use the code to build folder mar-16 then it builds folder date 03-03-2016 then file goes into that dated folder. I added removed etc. Im definitely putting code in the wrong spots then or missing something. IF you can help and give me something that works. I appreciate it. I just cant get it.