Save<\/B> method rather than the SaveAs method. If you open an existing spreadsheet and make some changes to it, just call the Save method; that should save your document without displaying the confirmation dialog box. For example, here\u2019s a sample script that opens the file C:\\Scripts\\Test.xls, writes the current date and time into cell A1, saves the file, and then quits:<\/P>Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\nobjWorksheet.Cells(1, 1).Value = Now\nobjWorkbook.Save()\nobjExcel.Quit\n<\/PRE>\n
In most cases, that should do the trick. However, it\u2019s also true that the SaveAs method offers more options than the Save method. If you want to add password protection to the spreadsheet, create a backup version of the file, or save in another format, you\u2019ll need to use SaveAs. Likewise, suppose that every morning you run a script that grabs some data and populates cells in a brand-new spreadsheet. Every morning you want to save that file as C:\\Scripts\\Daily_report.xls. Because this is a new spreadsheet (remember, you didn\u2019t open the existing Daily_report.xls), you need to use the SaveAs method. And, when you do, a dialog box will pop up telling you that a file by that name already exists and asking you if you want to replace it.<\/P>\n
So how do you get around these dialog boxes? The secret is to set the DisplayAlerts<\/B> property (part of the Excel Application object) to FALSE<\/B>. The DisplayAlerts property suppresses the display of dialog boxes and alert messages; instead of allowing you to choose whether to do X, Y, or Z, Excel automatically selects the default action for you. The default action for overwriting existing files is Yes<\/B>. So when DisplayAlerts is set to False, the revised worksheet will be saved and you won\u2019t be nagged and bothered by dialog boxes. Here\u2019s a sample script that sets DisplayAlerts to FALSE and then uses the SaveAs method to save the changes:<\/P>Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nobjExcel.DisplayAlerts = FALSE<\/p>\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1, 1).Value = Now\nobjWorkbook.SaveAs(“C:\\Scripts\\Test.xls”)\nobjExcel.Quit\n<\/PRE>\n
And just for the heck of it, here\u2019s a script that creates a new worksheet and then overwrites an existing worksheet, again by setting DisplayAlerts to FALSE and by using SaveAs:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nobjExcel.DisplayAlerts = FALSE<\/p>\nSet objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1, 1).Value = Now\nobjWorkbook.SaveAs(“C:\\Scripts\\Test.xls”)\nobjExcel.Quit\n<\/PRE>\n
If you\u2019re having problems saving files, setting DisplayAlerts to FALSE will more than likely take care of things for you.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I open an existing Excel spreadsheet, add some additional information to that spreadsheet, and then save my changes? Every time I call the SaveAs method a dialog box pops up asking me if I want to save my changes.— RW Hey, RW. Generally speaking, you can avoid that dialog box […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5],"class_list":["post-70543","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I open an existing Excel spreadsheet, add some additional information to that spreadsheet, and then save my changes? Every time I call the SaveAs method a dialog box pops up asking me if I want to save my changes.— RW Hey, RW. Generally speaking, you can avoid that dialog box […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70543","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=70543"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/70543\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=70543"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=70543"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=70543"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n