MS-Excel macro help!

[yesthattom]

I’m writing a macro that, among other things, saves the MS-Excel spreadsheet:

ActiveWorkbook.SaveAs Filename:= “EventList.txt”, FileFormat:= xlText

However, every time it runs I get a dialog box asking for permission to replace the old file.

Anyone know how to disable that box?

Comments

[cos]

I know nothing about Excel macros, but if there isn't a way to disable that box, how about:
- rename the old file
- save the new file
- if the save was successful, delete the renamed file

[likethewatch.livejournal.com]

Instead of trying to disable the box, I would build into the macro the action of pressing the OK button to continue.

[chhotii.livejournal.com]

How is that possible? I don't think the SaveAs function returns until after the box has been dismissed, does it?

[yesthattom.livejournal.com]

When I record the macro, then view the macro source it has nothing like that. Can you give me some pointers?

The ORA books that I have all delete the file right before they do the save. LAAAAME.

[likethewatch.livejournal.com]

Problem is when *I* write a macro, it's by turning on the Record button. I don't know how to program at *all* and rely entirely on the GUI.

I just recorded a macro that saved the file with the same filename as an existing one, so I'd be prompted to overwrite the existing file. This is what it recorded:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Administrator\My Documents\foo.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Does that help you at all?

[chhotii.livejournal.com]

What version of Excel are you using? I have here Excel X for Macintosh Service Release 1. It won't even run your code snippit, it gives an error "Run-time error '424': Object Required", which mystifies me.

[yesthattom.livejournal.com]

I'm using Mac Office 2004.

Tom