Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

File SaveAs, replacing existing one if it exists

Posted on 2014-01-02
2
Medium Priority
?
300 Views
Last Modified: 2014-01-03
This code kicks back asking if I want to overwrite the existing file with the same name.  How do I tell it yes.  and do it every time?  Please advise and thanks. -R-

myDirECTorY = "C:\_SAP\ShortageRpt\DefaultExtractFiles\"        'Wherever you want
'myFileNameO = myDirECTorY & Format(Now(), "yyyymmdd_hhmm") & " ZSHORTV2DataCurrent" & ".xlsx"    'File name as desired.
myFileNameO = myDirECTorY & " ZSHORTV2DataCurrent" & ".txt"    'File name as desired.

Workbooks.Add
ActiveSheet.Paste

'Add text to column here

ActiveWorkbook.SaveAs Filename:=myFileNameO, FileFormat:= _
      xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
 
MsgBox (" Done file saved ")

Open in new window

0
Comment
Question by:RWayneH
2 Comments
 
LVL 20

Accepted Solution

by:
dsacker earned 2000 total points
ID: 39752947
Before your SaveAs, add this line of code:
Application.DisplayAlerts = False
After your SaveAs, add this line of code:
Application.DisplayAlerts = True
0
 

Author Closing Comment

by:RWayneH
ID: 39753751
Thanks, worked nicely. -R-
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question