GetSaveAsFilename almost works... need a little Help!

[noxidnai]

I have a .xltm template that is a form used by several users. After the user enters some information (Co, MASitem, MASdesc) the form is ready to be saved. There is a button that launches the code below. Right now it successfully opens the saveas dialog and fills in the file name and .xlsm extension correctly. When save is clicked the window closes, but nothing is saved! Obviously I'm missing something... help please!

- Ian

Sub SaveAs()

Dim Var As Variant
Dim BuildFileName As String
Dim FilePath As String
Dim MASitem As String
Dim NEWitem As String
Dim MASdesc As String
Dim NEWdesc As String
Dim Co As String

If Cells(2, 20).Value <> "" Then
    Co = "AB, "
    ElseIf Cells(2, 23).Value <> "" Then
    Co = "CD, "
    ElseIf Cells(2, 26).Value <> "" Then
    Co = "EF, "
    Else: MsgBox "Select a Company Name first."
    End
End If
    
MASitem = Cells(4, 14).Value
If MASitem = "" Then
    MsgBox "The Item Code field is blank."
    End
Else
    NEWitem = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(MASitem, "|", "-"), ">", "-"), "<", "-"), Chr(34), "in"), "?", ""), "*", ""), ":", "-"), "/", "_"), "\", "_"), ", ", " "), ",", " ")
End If

MASdesc = Cells(6, 14).Value
If MASdesc = "" Then
    MsgBox "The Description field is blank. Check Item Code."
Else
    NEWdesc = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(MASdesc, "|", "-"), ">", "-"), "<", "-"), Chr(34), "in"), "?", ""), "*", ""), ":", "-"), "/", "_"), "\", "_"), ", ", " "), ",", " ")
End If

FilePath = "C:\folder\"
BuildFileName = FilePath & Co & NEWitem & ", MAP-ACE " & NEWdesc
Var = Application.GetSaveAsFilename(BuildFileName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
Application.DisplayAlerts = True

End Sub

[TMS]

It does what it says on the tin. It gets the Save As Filename ... and stores it in a variable, var. you then need to use that to save the file.


Regards, TMS

[noxidnai]

Trevor,

I'm not sure what to do with var at this point. I've only got as far as I have using examples on excelforum.com and other helpful sites. I assumed that clicking okay on the SaveAs window was the end of the road.

I re-read the Excel Help and understand what you are saying a little better. Basically when okay is clicked it passed var back, so I need to write some code to actually save the file, right?

- Ian

[berlan]

Perhaps you add the following:

Dim wb as workbook

Set wb= Workbooks.Add 
Do 
    Var = Application.GetSaveAsFilename(BuildFileName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
Loop Until Var <> False 
wb.SaveAs Filename:=Var

[berlan]

Hi nexidnai,

try use the SaveAs-function with Var (that stores the SaveAs name as Trevor outlined) at the end of your procedure, as I tried to show in my previous post.

[noxidnai]

I added the code below and it seems to work. Don't know if there should be anything to handle errors.

- Ian

ActiveWorkbook.SaveAs Filename:=Var, FileFormat:=52

[TMS]

You're welcome. Thanks for the rep.

There shouldn't really be any errors to handle, I don't think, because you've used the dialogue box to navigate to a folder, etc.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[berlan]

Great! Sorry if I may have only confused you noxidnai, meant to set wb = Thisworkbook.

All best