Save As Dialogue Box Error

[dirtychinchilla]

Morning all,

I've pieced the code below to create a Save As dialogue box, with the goal being that the name and file type are already completed for the user. The file name is generated from various other parts of information in the spreadsheet, and in order to eliminate what I thought were illegal characters, I've used the following:

="Service Quote"&CHAR(95)&SUM(Input!$E$11:$E$26)&"xAM SVU"&CHAR(95)&ProjName&CHAR(95)&TEXT(Date,"ddmmyyyy")

Using the above in conjunction with the below, the Save As dialogue box opens, with the file name and type filled in as planned, and then when I press save I get an error to say "The text: is not a valid file name."

It looks like it's not recognising that a file name has even been entered, but obviously it has.

Sub SaveAs()

Dim varResult As Variant
Dim strName As String


'displays the save file dialog


    varResult = Application.GetSaveAsFilename(InitialFileName:=Range("FileName"), FileFilter:="Excel Files (*.xls), *.xls")
    
'checks to make sure the user hasn't canceled the dialog


    If varResult <> False Then
        Cells(2, 1) = varResult
    End If


InvalidName:


    MsgBox "The text: " & strName & " is not a valid file name.", vbCritical


End Sub

This is the last piece of this program so I'd really appreciate anyone's input!

Thanks in advance,

Jonathon

 

[Norie]

Jonathon

Even if a valid filename was entered then the message box telling the user the filename isn't valid will be executed.

You need to exit the Sub once you've checked the user hasn't clicked Cancel.

Sub SaveAs()

Dim varResult As Variant
Dim strName As String


'displays the save file dialog


    varResult = Application.GetSaveAsFilename(InitialFileName:=Range("FileName"), FileFilter:="Excel Files (*.xls), *.xls")
    
'checks to make sure the user hasn't canceled the dialog


    If varResult <> False Then
        Cells(2, 1) = varResult
        Exit Sub
    End If


InvalidName:


    MsgBox "The text: " & strName & " is not a valid file name.", vbCritical


End Sub

PS I would suggest another name for the sub - SaveAs could get confused with the Workbook SaveAs method.

 

[dirtychinchilla]

Norie to the rescue, once again! Thanks for correcting the text box part, but now it's not saving and has no box to let you know that the filename is invalid...

 

[dirtychinchilla]

Actually, having looked at it again, it the error message now only​ comes up if you press cancel in the dialogue box!

 

[Norie]

The code doesn't save anything, all it does is prompt the user to get the name to save the file with.

To actually save the file you'll need further code.

 

[dirtychinchilla]

The code doesn't save anything, all it does is prompt the user to get the name to save the file with.

To actually save the file you'll need further code.

Oh...! I want the user to be able to select where to save it, so I had thought that by opening up the dialogue box, they'd actually be able to save it.

 

[dirtychinchilla]

Finished it! Thanks for your help, Norie. Completed code is below:

Sub SaveDocument()

Dim varResult As Variant
Dim strName As String


'displays the save file dialog


    varResult = Application.GetSaveAsFilename(InitialFileName:=Range("FileName"), FileFilter:="Excel Files (*.xlsx), *.xlsx")
    
'checks to make sure the user hasn't canceled the dialog


    If varResult <> False Then
        Cells(2, 1) = varResult
        ActiveWorkbook.SaveAs Filename:=varResult
        Exit Sub
    End If


InvalidName:


    MsgBox "The text: " & strName & " is not a valid file name.", vbCritical


End Sub

 

[dirtychinchilla]

I ended up making a minor change to control the file format:

Sub SaveDocument()

Dim varResult As Variant
Dim strName As String


'displays the save file dialog


    varResult = Application.GetSaveAsFilename(InitialFileName:=Range("FileName"), FileFilter:="Excel Files (*.xls), *.xls")
    
'checks to make sure the user hasn't canceled the dialog


    If varResult <> False Then
        Cells(2, 1) = varResult
        ActiveWorkbook.SaveAs Filename:=varResult, FileFormat:=xlExcel8
        Exit Sub
    End If


InvalidName:


    MsgBox "The text: " & strName & " is not a valid file name.", vbCritical


End Sub