Save As Macro

I have created a macro that I use weekly.  The macro names and saves the 
spreadsheet.  

Currently, when saving the spreadsheet I am asked "A file name 
‘c:\filename.xls’ already exists in this location.  Do you want to replace 
it?"

Is there a way that I can amend the macro to automatically answer YES. 

 ActiveWorkbook.SaveAs Filename:="c:\filename.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False 

Thanks
0
Utf
4/9/2010 12:01:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1360 Views

Similar Articles

[PageSpeed] 14

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



Max2073 wrote:
> 
> I have created a macro that I use weekly.  The macro names and saves the
> spreadsheet.
> 
> Currently, when saving the spreadsheet I am asked "A file name
> ‘c:\filename.xls’ already exists in this location.  Do you want to replace
> it?"
> 
> Is there a way that I can amend the macro to automatically answer YES.
> 
>  ActiveWorkbook.SaveAs Filename:="c:\filename.xls", _
>         FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
>         ReadOnlyRecommended:=False, CreateBackup:=False
> 
> Thanks

-- 

Dave Peterson
0
Dave
4/9/2010 12:15:50 PM
Hi,

Try this

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="c:\filename.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Max2073" wrote:

> I have created a macro that I use weekly.  The macro names and saves the 
> spreadsheet.  
> 
> Currently, when saving the spreadsheet I am asked "A file name 
> ‘c:\filename.xls’ already exists in this location.  Do you want to replace 
> it?"
> 
> Is there a way that I can amend the macro to automatically answer YES. 
> 
>  ActiveWorkbook.SaveAs Filename:="c:\filename.xls", _
>         FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
>         ReadOnlyRecommended:=False, CreateBackup:=False 
> 
> Thanks
0
Utf
4/9/2010 12:17:02 PM
Reply:

Similar Artilces:

How to change a Macro based on content of a cell
Hi, I need to import a text file into a spreadsheet. I can do this manually by doing File > Open > then select a text file. Then Excel goes through the Text Import Wizard I can automate this by recording a Macro in Excel. The macro looks like this (Excell wrote that for me, I did not): Sub Macro1() Workbooks.OpenText Filename:="C:\DrillData\" + "MOR701.pck", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(12 _ , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True End Sub How can I change ...

Macro help #8
Here is what I have: Sheet3(pcdata) data is in rows A3:R19 I need to copy and paste(Macro) this data to Sheet4(Total) The first available row is A3 Sheet4(Total) The Sheet3(pcdata) will be updated daily the macro needs to paste the data on Sheet4(Total) below the existing data,, (next available row). As I am quite a novice at excel. any help.. is very appreciated. Thanks Mike Hi Mike Try this http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Mike" <mbrune@insightbb.com> wrote in message news:rBKle.11597$Is4.5585@attbi_s21... &g...

Saving dataset to XML and reading back gives different data
This would better be described by 'serialization' than 'interop', but I didn't find a newsgroup that seems closer on topic. The problem in a few words: I save data with DataSet.WriteXML, but I get different data back when I read it later with DataSet.ReadXml. More detail: I'm saving a dataset containing a single datatable to an XML file (DataSet.WriteXml), and its schema to an xsd file (DataSet.WriteXmlSchema). Then in another app, I load them back into a new empty dataset using DataSet.readXmlSchema and DataSet.ReadXml. The schema is read first. What's &qu...

Macro to run without any popups
I am a newbie but thought someone in this group could assist Trying to accomplish the following: Search for all ^p and replace them with ^| (soft return) and then find the following text =93please call with questions=94 and put that in bold anywhere in the sentence. However I am able to do the soft return with a macro but I don=92t want any popups to happen when I run the macro. Is that possible? Sub ScratchMaco() Dim oRng As Word.Range Set oRng = ActiveDocument.Content With oRng.Find .Text = "^p" .Replacement.Text = "^l" .Execute Replace:=wdRep...

Could not complete the operation. One or more parameter values are not valid. The note will close and your changes will not be saved.
When I close Outlook 2000 after having opened a Note for edit or read-only, I get the following error messages: "Could not complete the operation. One or more parameter values are not valid." -Followed By- "The note will close and your changes will not be saved." When I researched this message, Microsoft has a KB note (KB842942) regarding Outlook 2003 and a Live Meeting Add-in. The only thing similar is that I recently ran NetMeeting for the first time on this PC - after all of my Palm stuff has bee...

Disable worksheet macros when copying a worksheet in VBA
I am using the worksheet.copy method to copy a worksheet intact. The source worksheet has a few event macros that I would like to disable in the destination worksheet. I considered just copying the contents of the source to a new worksheet. However, the source worksheet contains some controls I would like to keep in the destination sheet. These controls call to module subroutines outside the worksheet. I can see two possible solutions, but I do not know how to execute any of them: 1) Disable all worksheet VBA code for the destination worksheet 2) Copy the contents of the source work...

Macros and the Toolbar
I have some worksheets that we have built for quote and invoicing. I would like to create some generic macros to reposition me to certain cells in the spreadsheet, but would like these macros available in several different files. We have different files with different worksheet names, but the layout of the data is the same for all. When I click a custom button assigned to a macro, I would like to jump to a specific cell on the sheet. I have been able to create the macros, assign them to some buttons, but they only seem to work on a specific page, and not generically for any worksheet...

Can't save Excel file. Msg "File" is in use. Try again later.
Recently I've been getting the message "<Filename> is currently in use. Try again later." when I try to save certain Excel files. (Most of these are saved in compatibility mode.) Is there something I can do besides constantly saving under a new file name? My OS is XP Professional. ...

Visio macros for ALL Visio files??
Hi all, No matter what I do, when I create a macro in Visio, that macro only works in the file I created it in, and not in any files other that I have already created. That is useless too me, because I want my macro to format a footer for me, and I have 600 files it must do this to (one at a time -- but I wish it could do this globaly, but that's too much to ask)! Is there any way at all to make the new macro available to any and all open Visio files? (I have Visio 2003 SP2) Thanks! -Bill Hi Bill, Yes, you're right, there's no "Normal.dot" to hold your "glo...

US Savings Bonds #4
Does anyone know how to track US Savings Bonds in Microsoft Money and have the current value calculated? I add a new investment and enter the purchase value of the bonds but Microsoft Money doesn't figure out what the current value is. The advice that I've seen for older version suggests figuring it out from the table on my own and then entering that value. I'd much prefer if the value was updated automatically my Money. Money does not know how to value US Savings Bonds. Use Savings Bond Wizard (from the US Treasury web site) for that and than enter an Interest transaction in Mo...

Macros associated with pictures
I am working with Excel 2000. I have a worksheet that has several pictures on it. Each picture has an associated macro. The macros are in an extenal workbook. Currently each picture, when it tries to access the macro, it cannot find the file, because the file containing the macros has been moved. I have a number of files that are setup this way and need a macro that will go in and automatically remove the folder/path refference from the file/macro reference. Example: Currently referenced macro: F:\Dir1\SubDir2\SubSubDir3\OperatorMacros.xls!Picture6_Click I want it to read: OperatorMacr...

Saving an Excel file #2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I cannot save an Excel file to my hard drive. I get a message that reads "...Cannot access the file "Macintosh HD" with various reasons. Have you tried repairing disk permissions? Have you udated to Office 12.0.1? Are you running OS X 10.5 or 10.5.2? It would help to know specifically *what* reasons are given in the error messages - "various" tells us absolutely nothing. In fact, the content you're omitting is the most important part. -- Regards |:>) Bob Jones [MVP] Office:Mac <fr...

Removing the Macro message when opening a worksheet
If a worksheet has a macro embedded into it, is it possible to remove the macro so that when you open the spreadsheet, it does not prompt yoe whether you want to enable macros etc Debra Dalgleish has some pictures that may help: http://www.contextures.com/xlfaqMac.html#NoMacros Matt wrote: > > If a worksheet has a macro embedded into it, is it > possible to remove the macro so that when you open the > spreadsheet, it does not prompt yoe whether you want to > enable macros etc -- Dave Peterson ec35720@msn.com ...

Recording Macros using Office 97 with Windows XP ( user level not.
Unable to record macros in excell, office97 Using windowsXP as an User but can as an administrator? Hi maybe the location/folder for personal.xls is restricted for your user account -- Regards Frank Kabel Frankfurt, Germany sp2 wrote: > Unable to record macros in excell, office97 Using windowsXP as an > User but can as an administrator? ...

looping excel macros
I have Excel 2002 and routinely use the macro recorder. I can read the macros and make simple edits, but never learned to program in VB and my fortran programming experience is from 30 years ago. After recording a macro, I want it to loop 15 times before exiting. What lines to I need to add to the code to start and end the loop? I already have the macro. It basically inserts a line, totals certain columns and formats one cell. Brad You could use a "for/next" loop Dim dCtr as long for dctr = 1 to 15 'do something next dctr But if you're inser...

save as pipe delimited #2
hi, just an appendage: i found another link, which provides a file by Earl which does this. http://www.excelforum.com/showthread.php?t=265998&highlight=pipe+delimited i am not able to figure out where to input the filename for the data t be picked up from. any information regarding this would be helpful. thanks, mac -- icestationzbr ----------------------------------------------------------------------- icestationzbra's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=458 View this thread: http://www.excelforum.com/showthread.php?threadid=26877 ...

How do I save worksheet form data into a table in Excel 2003?
In Excel 2000 I used the Template Wizard to copy form data to a spreadsheet table in another workbook. I don't see this function in Excel 2003. ...

PUB 03
Win XP, Pub 03 No other Apps open I'm having trouble with 'save as picture'. It can take 5 min or more to complete the task. The result of the saved jpeg is average appx 2-3 megs. Even without any pics. I'm selling a few things through craigslist. I post a link to my Yahoo Photos for descriptions and pics. I use Pub to create a kind of information sheet with pics when needed. I do create them larger than the 'page', it gives a bit more freedom. When finished, I save as a jpeg in order to upload. This really really slows my entire computer. Sometimes i...

auto saving documents Office 2003
I can not find where to set up auto save in excell I know in office 2000 there was an "addin" for auto save, where I could save just the workbook I was working on or all workbooks. Can someone help? Laurie, 2003 has auto recovery, tools options, save, it saves the file at set times and if excel crashes, or you lose power excel will try and recover the file when you open it back up, if you have 2000 or 97 on another computer, or a disk you can use the Auto Save addin from them and it will work in 2003. -- Paul B Always backup your data before trying something new Please post...

Make combobox blank in macro
I use a wizard to create a combo box to find the record in my form. It creates macro automatcially. Works fine. After finding the record, I want to set the combo box to null or "" but need help add that line in macro. Thanks. Sorry but not many people use macros (me too) so can’t really be help there. But try this. I assume your macro is search on a name ? You have the ID field in the combo and the name and the ID field is the bound column of the combo ??? If so. 1st MAKE A BACKUP OF YOUR DATABASE Then – open you form in design view Right click the combo an...

macro help #21
please help iwant to make a macro that consists of- in Cell a1 my stock number 1541 in cell b1 my part name crisps when i enter my stock number i want my part name to appear. is this possible?? Hi Mantha, Look at vLookup in Excel help. Even better, see debra Dalgleish's tutorial at: http://www.contextures.com/xlFunctions02.html --- Regards, Norman "mantha09" <mantha09@discussions.microsoft.com> wrote in message news:9301063A-2473-4A6D-B8C6-1B4E4A0A35B6@microsoft.com... > please help > iwant to make a macro that consists of- > in Cell a1 my stock...

Removing a custom Toolbar Button & macro
I've put macro in module, then make a custom button on the toolbar to invoke a macro.(according to excel help) -------------------------------------- Create a toolbar button that runs your macro 1. On the Tools menu, click Customize, and then click the Commands tab. 2. Under Categories, click Macros. 3. Drag the custom button to the toolbar where you want it. 4. On the Customize dialog box, click Modify Selection, and then click Assign Macro. 5. In the Assign Macro dialog box, click the nam...

Save record immediately?
I have a simple database with a data entry form for adding records to the main table [with a Date field, default value Date() ] On that form is a command button which runs a query to show the records added that day. However, if the user has completed the fields on the form but not navigated to the next new record, that record does not show in the query. How can I get a record still showing in the form (just entered) to be included in a query of records added that day? Any help gratefully received. Cheers......Jason hi Jay, Jay wrote: > How can I get a record still showing in ...

Re: Saving websites when shutting down
My mistake. I should have said Shft button, not Ctrl button. Frank "FrankV" <vze32975@verizon.net> wrote in message news:... > Try pushing Ctrl button at shutdown>standby with the web pages left open. > > Frank > > "Robert" <readydougREMOVEERMVOE@yahoo.com> wrote in message > news:Xns9CD87BA0A22Fcpq1bcle@209.197.15.222... >>I have a laptop computer with Windows XP Professional. Sometimes I have >> Ineternet Explorer open to several sights when I need to turn off the >> computer and take it with me. I have ...

Saving Calendar As Web Page
I'm having a problem saving a calendar as a web page to my web server. The first time I do try to save a calendar as HTML into a folder on my web server, it works fine. If I go back and make changes and try to save it again into the same folder, I get an "operation failed". If I delete all of the items in the folder and then try to save as, it works fine. Is there a way around this so that I don't have to erase the files each time? Thanks, Glenn ...