"Save as" without user intervention 2

[docjohn52]

I used to have some code that would save a sheet without the user box. I can't find it anymore. Didn't ask (SAVE Yes).
Can anybody clip that to me?
A star for the winner... all the winners...!

 

[Nikita6003]

docjohn,

not quite sure which of the following you need:

ActiveWorkbook.SaveAs <Path&FileNameHere>

will save the active workbook using [tt]Path&FileNameHere[/tt]

Alternatively, use

Application.DisplayAlerts = False

to switch off all Excel message boxes to the user. Then run your code, and use

Application.DisplayAlerts = True

to switch the messages back on (beware: if you don't switch them back on, you will NOT see any msgboxes until you close/restart Excel)

HTH

Cheers
Nikki
Look, mommy, I'm flying!

 

[docjohn52]

humm, let'see... tried the first one, and it wouldn't play.
(bumps the save me screen...) Maybe not always, seems i've been here...
Next two are completely new, can't wait to play... Stars to yah!
Who's next?

 

[TonyJollans]

Hi docjohn52,

I don't know what would cause you to get prompts from the [purple]SaveAs[/purple], but a quick note about Appliocation.DisplayAlerts - it should suppress prompts for you, but it only has effect in current running code. Although it's good practice to reset it, it will be reset at the end of your procedure anyway, and also does not have effect at a breakpoint.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Nikita6003]

Hiya docjohn, Tony,

This from Excel 97 Help function:

If you set [DisplayAlerts] to False, Microsoft Excel doesn't automatically set it back to True when your macro stops running. Your macro should always set the property back to True when it stops running.

Even if the Help function is incorrect here, you're right that it's best practice to switch it back on anyway. I know of at least one occasion where I closed a newly-debugged tool without getting the "Do you want to save changes ... " dialog box - cost me a day's work and a helluva lot of comments from my fellow-not-very-sympathetic-programmers




Cheers
Nikki
Look, mommy, I'm flying!

 

[stibbetts]

Maybe related, but I am having a problemwith saving as well, i create a new document and want to save it, if i use 'doc.save' it prompts me with the saveas window and asks for a name. but if i use 'doc.saveas "c:\filepath\filename"' I get an error message that saveas is not a valid method or operation or something to that effect. so if we're bribing with stars, theres a star for anyone who can help with this.

 

[docjohn52]

NewBook.SaveAs Filename:=fName is supposed to work, straight from help

 

[stibbetts]

saveas gives me an argument not optional error, save however works but makes saveas pop up becsue its a new document. and its in autodesk inventor just an fyi incase it helps. thanks though

 

[Dimandja]

Try this

Application.DisplayAlerts = False
ActiveSheet.Copy
ActiveWorkbook.SaveAs ActiveSheet.Name & ".xls", FileFormat:=xlWKS
ActiveWorkbook.Close
Application.DisplayAlerts = True

 

[PHV]

And the F1 key does nothing in the inventor IDE ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[docjohn52]

btw...

Application.DisplayAlerts = False
Sheet1.SaveAs "c:/test1.xls"
Application.DisplayAlerts = True

works like a champ, saves right over the old filename with intervention, perfect for saving a logfile.

Thanks to all who helped!

 

[stibbetts]

i gave up and attacked it from a different angle thanks to those who tried though

 

[TonyJollans]

Hi Nikki,

M$ seem to want to keep people guessing with this one .

As I understand it, whether or not it is automatically reset, DisplayAlerts only has effect in running code.

Also from Help ..

Set this property to False if you don’t want to be disturbed by prompts and alert messages [red]while a macro is running[/red];

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Nikita6003]

Thanks, Tony,

I guess M$ could stand to learn a lot about the use of proper English - shall we point the Making an Impression forum out to them?



Cheers
Nikki
Look, mommy, I'm flying!

 

[TonyJollans]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.