Activeworkbook.saveas And Error Handling...

mjschukas · Apr 26th 2007

i'm using the following code to force saving a file into a predetermined path and folder...

sometimes, the user accidentally uses a "special character" in one of the cells and the file won't save...

i would like to have an error message, alerting the user to this problem (and ask them to rename and save again...)...

???

Code

customerName = Range("h6").Value
customerjobwork = Range("h7").Value
mysalesyear = Range("h4").Text
Dim myfilename As String
Dim newfile As Variant
customerjobwork & "-" & mysalesyear
myfilename = "c:\temp\" & customerName & " - " & customerjobwork & " - " & mysalesyear
newfile = Application.GetSaveAsFilename(myfilename)
If Not newfile Then Exit Sub
ActiveWorkbook.SaveAs filename:= _
        newfile, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
        Application.WindowState = xlMaximized

Display More

thank you...

Mavyak · Apr 26th 2007

Re: Activeworkbook.saveas And Error Handling...

Try some nested Replace functions. Insert the following line of code after you populate the myfilename variable but before you populate the newfile variable:

Code

myfilename = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(myfilename, "\", ""), "/", ""), "?", ""), "<", ""), ">", ""), "*", ""), "|", ""), ":", ""), Chr(34), "")

mjschukas · May 1st 2007

Re: Activeworkbook.saveas And Error Handling...

thank you...

and if i would like to test for these initially (rather than just replace) and send a message to the user...how would i do this...?

(nested ifs...?)

???

thank you...

Activeworkbook.saveas And Error Handling...

Participate now!

Share