Excel VBA Save As Button

[darbymsu1982]

I'm trying to create a macro button in my Excel spreadsheet. I can create the button, I just need the code.

I need the 'Save As' dialog box to open. FileName to be the text located in "J15" , the file path to be linked to "K61", and the file type to be ".xlsm"

Can anyone help that knows VBA?

Thanks!

 

[bobsan42]

try this:

    Dim FilenameSaveAs As String
    
    ChDir (ThisWorkbook.Worksheets("Sheet1").Range("K61"))
    FilenameSaveAs = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Worksheets("Sheet1").Range("J15"), fileFilter:="Microsoft Excel Macro-Workbook (*.xlsm), *.xlsm")
    If FilenameSaveAs <> "False" Then
        ActiveWorkbook.SaveAs Filename:=FilenameSaveAs, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        Else
        MsgBox "File not saved", vbInformation, "Saving Cancelled"
    End If

P.S.: for ChDir to work you may need to use ChDrive as well.

 

[darbymsu1982]

Almost there, but doesn't actually Save...



Sub FileName_Cellvalue()
'DialogBox Opens
'J15 is added to Save line w/ "Quote"
'File Path location works correctly
'Doesn't Actually Save
Dim fPth As Object
Dim Path As String
Dim FileExtStr As String
Path = Sheets(1).Range("K61").Text
FileName = Sheets(1).Range("J15").Text + " - Quote"
FileExtStr = ".xlsm": FileFormat = xlOpenXMLWorkbookMacroEnabled
Set fPth = Application.FileDialog(msoFileDialogSaveAs)


With fPth
.InitialFileName = Path & FileName & FileExtStr
.Title = "Save As"
.InitialView = msoFileDialogViewList
.Show
End With


End Sub

I'm trying to create a macro button in my Excel spreadsheet. I can create the button, I just need the code.

I need the 'Save As' dialog box to open. FileName to be the text located in "J15" , the file path to be linked to "K61", and the file type to be ".xlsm"

Can anyone help that knows VBA?

Thanks!

 

[darbymsu1982]

Thanks so much for your response. I've been trying to get yours to work all day..

It gives me an Error "Run-Time Error (Error 9) Subscript out of range"

Points to your ChDir... line...

I tried adding the ChDrive line as directed to no avail.

The source directory will be different locations on the "S:"

 

[bobsan42]

I would guess the error is to do with the part after ChDir :

ChDir (ThisWorkbook.Worksheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("K61"))

maybe you don't have a sheet named "Sheet1" - change the red to your sheet's name.
or use the thing that works for you:

Sheets(1).Range("K61").Text

I guess the same error will appear on the next line as well.

 

[darbymsu1982]

I got it! You were correct. I changed the "Sheet1" and it worked perfectly.

Thanks so much!