How to "Save As" a worksheet as a text 2

[pradipto]

This is probably a really dumb question. Suppose I have a worksheet with a sheet say "Data" and I want to save only this particular sheet as a textfile, say "D:\Data.txt" by running a macro, what would be the VBA code ...?

Thanks in advance.

 

[RobBroekhuis]

This works:

activesheet.saveas "c:\myfile.txt",xltextmsdos

Rob

 

[RobBroekhuis]

But come to think of it, I'd never do it that way, because then the activeworkbook.name is set to the name you just gave your sheet. Better to do it in two steps:

activesheet.copy
activeworkbook.saveas "myfile.text",xltextmsdos
activeworkbook.close
Rob

 

[pradipto]

thanks a lot. actually i was just about to write regarding the issue in your second post.

just two more questions:

(a) if there is already a file existing, then Excel ask "A file name 'myfile.txt' already exists in this location. Do you want to replace it?'. How do i supress this pop-up, so that it automatically overwrites?
(b) After writing it in that location, it gives another pop-up (due to activeworkbook.close line) : "Do you want to save the changes you made to 'myfile.txt'? ". how do i supress this second pop-up ?

thanks

 

[andrew299]

PUT IN

Application.DisplayAlerts = False

ActiveSheet.Copy
ActiveWorkbook.saveas "myfile.text", xlTextMSDOS
ActiveWorkbook.Close
Application.DisplayAlerts = True

andrew299

 

[RobBroekhuis]

You can suppress the messages using

application.displayalerts=false
...
application.displayalerts=true

always remember to turn this switch back on; you won't like the lack of warnings if you forget. Also, you can specify in your close command that you don't want to save changes:

activeworkbook.close savechanges:=false

I'm surprised you'd get the prompt, since you just saved in the line above, but sometimes Excel is a little paranoid...

Rob