Boy, that's disappointing, huh?
It came back with that invalid name for me, but the saveas failed.
Maybe you could check after the attempt to save???
Option Explicit
Sub testme()
Dim SaveOk As Boolean
Dim TargetName As Variant
TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SaveOk = False
Do
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")
If TargetName = False Then
Exit Do 'but it wasn't saved!
End If
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=TargetName, FileFormat:=xlWorkbookNormal
If Err.Number <> 0 Then
MsgBox "Workbook wasn't saved!"
TargetName = ""
Err.Clear
Else
SaveOk = True
End If
On Error GoTo 0
If SaveOk = True Then
Exit Do
End If
Loop
If SaveOk = False Then
'error messages here?
End If
End Sub
Amazingly, if (in the getsaveasfilename dialog) I just added a spacebar at the
end of the string, then deleted that space character, the dialog issued a
warning....
So I tried this...
Option Explicit
Sub testme()
Dim TargetName As Variant
TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SendKeys "{End} {backspace}"
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")
If TargetName = False Then
Exit Sub
Else
MsgBox TargetName
End If
End Sub
And son of a gun (or !@#%!!), it seemed to work ok.
Robert said:
This is very odd, I got the same result as you for "blah:blah.xls" but
please try this which contains a potential file name from my system. Maybe it
has some thing to do with the space after the colon?
Option Explicit
Sub testme()
Dim TargetName As String
TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")
MsgBox TargetName
End Sub