PDA

View Full Version : How to Save File & Overwrite Original??



RonMcK
06-30-2010, 07:51 PM
Hi, All,

I want to open a tab-delimited text file (info.txt), do some manipulations using VBA, and then save the file back as tab-delimited text using the same name.

Using Macro Recorder to get a jumping off point, I ended up with this for opening and saving:
Sub Macro1()
'
' Macro1 Macro
'
Workbooks.OpenText Filename:="C:\Users\Ron\Desktop\EV Check6\INFO.TXT", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1)), TrailingMinusNumbers:=True

ActiveWorkbook.SaveAs Filename:="C:\Users\Ron\Desktop\EV Check6\INFO.TXT", _
FileFormat:=xlText, CreateBackup:=False

End Sub

The only problem: when I run the macro, Excel pops a dialog telling me the file exists and asking if I want to replace it.

How do I get Excel/VBA to just save it, ever so quietly? I answered Yes when I originally saved the file while recording the macro; why was that action not recorded in the macro?

Thanks!

HaHoBe
06-30-2010, 08:50 PM
Hi, Ron,

With Application
.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="C:\Users\Ron\Desktop\EV Check6\INFO.TXT", _
FileFormat:=xlText, CreateBackup:=False
.DisplayAlerts = True
End With
Ciao,
Holger

RonMcK3
07-01-2010, 06:48 AM
Holger,

Thank you very much. I've been away from VBA for 4 or 5 months and my little gray cells have forgotten much.

Thanks,