Overwriting Exisiting Excel File w/SaveAs 
Author Message
 Overwriting Exisiting Excel File w/SaveAs

Is it possible to overwrite an existing Excel file without being
prompted to do so? Here's my code:

xlsWorkbook.SaveAs "\\gmidoc01\finops\Summary.htm", xlHtml

Here's the message:

A file named "summary.htm" already exists in this location. Do you want
to replace it?

I answer yes, but I would prefer if it would not prompt me each time.

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 Overwriting Exisiting Excel File w/SaveAs
Hi Greg,

As I recall, (perhaps incorrectly), in VBA for Excel there is an option
for Save As with No Prompt.

Go into Excel, and record a macro of what you want to to. when you stop
recording go to "Edit" in the Macro edit and see what code excel
producted. I think there will be a line for ":= WDNoPrompt" or
"NoPrompt:= True/False" etc.

Alternatively in VB (not VBA) you could just test for the existence of
the file and delete it (the old one) first if it exists AS in

filename = "C:\Whatever\Something.XLS"  
if dir(filename) <> "" then
  kill filename
endif

Well come to think of it, this would work in VBA as well.

--
-Dick Christoph

http://www1.minn.net/~dchristo

Quote:

> Is it possible to overwrite an existing Excel file without being
> prompted to do so? Here's my code:

> xlsWorkbook.SaveAs "\\gmidoc01\finops\Summary.htm", xlHtml

> Here's the message:

> A file named "summary.htm" already exists in this location. Do you want
> to replace it?

> I answer yes, but I would prefer if it would not prompt me each time.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 Overwriting Exisiting Excel File w/SaveAs
I cannot profess to being au fait with Excel's VBA so there may well be a
solution.

However, if there is not a possible workaround could be to use the SendKeys
statement or alternatively
you could check that the file exists & then delete it.

The latter, while not ideal, would certainly stop the prompt from appearing.

Hope this is of some help.

Regards

Colin Gourlay
CG Systems

http://www.cgsystems.org.uk


Quote:
> Is it possible to overwrite an existing Excel file without being
> prompted to do so? Here's my code:

> xlsWorkbook.SaveAs "\\gmidoc01\finops\Summary.htm", xlHtml

> Here's the message:

> A file named "summary.htm" already exists in this location. Do you want
> to replace it?

> I answer yes, but I would prefer if it would not prompt me each time.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 Overwriting Exisiting Excel File w/SaveAs
I've not seen Greg's original message on my server but the answer to his
problem is to set Excel's

Application.DisplayAlerts = False

before doing the SaveAs and then set it back to True afterwards.

regards

Ian

** invalid email address, change dk to denmark

homepage http://www.kingsoft-denmark.com/
Tips & Tricks page http://computers.kingsoft-denmark.com/


Quote:
> I cannot profess to being au fait with Excel's VBA so there may well be a
> solution.

> However, if there is not a possible workaround could be to use the
SendKeys
> statement or alternatively
> you could check that the file exists & then delete it.

> The latter, while not ideal, would certainly stop the prompt from
appearing.

> Hope this is of some help.

> Regards

> Colin Gourlay
> CG Systems

> http://www.cgsystems.org.uk



> > Is it possible to overwrite an existing Excel file without being
> > prompted to do so? Here's my code:

> > xlsWorkbook.SaveAs "\\gmidoc01\finops\Summary.htm", xlHtml

> > Here's the message:

> > A file named "summary.htm" already exists in this location. Do you want
> > to replace it?

> > I answer yes, but I would prefer if it would not prompt me each time.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Word 2000 VBA - SaveAs Method Overwrites Without Warning

2. overwrite excel file

3. Write and SaveAs Excel files

4. VB script in Excel to SaveAs and not prompt user to replace

5. Excel Using SaveAs Dialog Box in Macro Code

6. Dialog box saveas in Excel,

7. Excel save/overwrite "fails"

8. Suppress msgbox under Excel's SaveAs

9. Excel 8.0 and Visual studio 97 overwrite

10. VBscript / Excel SaveAs question

11. Saving Excel workbook w/o SaveAs dialog?

12. Saving Excel file to Excel file

 

 
Powered by phpBB® Forum Software