Please HELP! I'm using the “GetSaveAsFilname” function to save my file using the value of a specific cell as title, this is created by a formula. I’m not the only one using the file, so the saving path most be selected by the user. Most of the times it works, but sometimes the title is blank, and the user need to write it down from zero. Below my code:

Sub SaveTool()

Dim Name As String
Dim sFileSaveName As Variant

Name = ActiveWorkbook.Sheets("Analisis").Range("G1")
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=Name, FileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
    ActiveWorkbook.SaveAs sFileSaveName
End If

End Sub

CodePudding user response：

If the suggested name is blank, the issue is with InitialFileName which is set to Name. So the issue is that Name is sometimes blank. Name gets it's value from ActiveWorkbook.Sheets("Analisis").Range("G1").

Two unreliable parts of this are:

1. ActiveWorkbook instead of ThisWorkbook or Application.Workbooks(index). If the user clicks on other workbooks before executing the macro, then ActiveWorkbook may be pointing to an unrelated workbook.
2. Sheets("Analisis").Range("G1") is open to user-editing. If the sheet has not been protected, the user may have unintentionally deleted the text contained in that cell.

I would suggest a line that checks if Name is blank and provides a default value to Name in the case that it is indeed blank.

Sub SaveTool()

Dim Name As String
Dim sFileSaveName As Variant

Name = ActiveWorkbook.Sheets("Analisis").Range("G1")

If Trim(Name) = "" Then Name = "DefaultFileName"

sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=Name, FileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
    ActiveWorkbook.SaveAs sFileSaveName
End If

End Sub

I have added a test to maybe help you in debugging why this does not seem to work for you and your files:

Open a blank workbook and try this code - press Save on the pop-up windows without typing anything:

Sub test()
    With ThisWorkbook.Sheets.Add
        .Name = "Analisis"
        .Range("G1").Value = "Test_File_Name"
    End With
    
    Dim Name As String
    Dim sFileSaveName As Variant
    
    Name = "Test_File_Name"
    Debug.Print "1a - " & Name
    sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=Name, FileFilter:="Excel Files (*.xlsm), *.xlsm")
    Debug.Print "1b - " & sFileSaveName
    
    Name = ThisWorkbook.Sheets("Analisis").Range("G1").Value
    Debug.Print "2a - " & Name
    sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=Name, FileFilter:="Excel Files (*.xlsm), *.xlsm")
    Debug.Print "2b - " & sFileSaveName
End Sub

The output should be:

'1a - Test_File_Name
'1b - C:\Users\Username\Documents\Test_File_Name.xlsm
'2a - Test_File_Name
'2b - C:\Users\Username\Documents\Test_File_Name.xlsm

Now remove the With block from the test and put the code into your project file. The differences in output should help you narrow down the cause of your problems.

CodePudding user response：

The problems where the special characters on the name to be assigned! I added a function to remove them with blank spaces and now it works! This is the final code:

Function ValidFileName(text As String) As String
    text = Replace(text, "\", "")
    text = Replace(text, "/", "")
    text = Replace(text, "[", "")
    text = Replace(text, "]", "")
    text = Replace(text, ":", "")
    text = Replace(text, "?", "")
    text = Replace(text, ".", "")
    text = Replace(text, ",", "")
        ValidFileName = text
End Function
Sub SaveTool()

Dim ToolName As String
Dim sFileSaveName As Variant


ToolName = ValidFileName(ThisWorkbook.Sheets("Analisis").Range("G1").Value)

sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=ToolName, FileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
    ActiveWorkbook.SaveAs sFileSaveName
End If

End Sub

Thank you @Toddleson for your help!