"Save As Macro Problem"

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Hi All,

Just discovered a bug that I haven't been able to resolve.
Situation is this, sometimes there already exists the file
as called in the macro, so you get the option window to
replace the existing file. If you click "Yes" things work
wonderfully, however if you click "No" the macro hangs up.

Here's the line of code:

ActiveWorkbook.SaveAs Filename:= _
"C:\WirelessLedger\Activation Invoices\" & Format
(Date, "mm-dd-yy") & " Activation Inv" & ".xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

Any and all help will be greatly appreciated,

Don
 
What do you want to do if there's a file by that name?

If you just want to write over it:

application.displayalerts = false
'your code to save
application.displayalerts = true

===
Or maybe you could give some kind of warning:

dim myFileName as string
dim resp as long

myFilename= "C:\WirelessLedger\Activation Invoices\" & _
Format(Date, "mm-dd-yy") & " Activation Inv" & ".xls"

resp = vbyes
if dir(myfilename) <> "" then
resp = msgbox (prompt:="it's already there, overwrite?", _
buttons:=vbyesno)
end if

if resp = vbyes then
application.displayalerts = false
'your code to save
application.displayalerts = true
else
msgbox "not saved"
end if
 
Hi Dave,

Thanks for the quick response. I'd like to keep the
option of not saving like the second option you lay out).

You've given me something to work on this evening.

Thanks again and I'll be back and let you know how it
works out.

Don
 
Hi again Dave,

Works like a charm. I had some places in the workbook
that I wanted to save (overwrite) with no option and some
where I left the option, overwrite or not, to the
operator. The info you posted was right on target for
both cases.

Thanks again for the very quick response.

Don
 
Glad it worked.
Hi again Dave,

Works like a charm. I had some places in the workbook
that I wanted to save (overwrite) with no option and some
where I left the option, overwrite or not, to the
operator. The info you posted was right on target for
both cases.

Thanks again for the very quick response.

Don
Click to expand...
 
Back
Top