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!
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!