I have a file that I open daily in Excel. It’s actually a data file with comma separated variables that is used by other database management programs. I use the following instruction to open the file:
Workbooks.OpenText FileName:="C:\Daily\Complete.all", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
Array(10, 2), Array(11, 2))
After the macro makes changes it saves the file with the following instruction:
ActiveWorkbook.SaveAs FileName:="C:\Daily\Complete.all", FileFormat:=xlCSV, _
CreateBackup:=False
Then additional changes (like column headings and such) are made and the file is saved again as an Excel file.
ActiveWorkbook.SaveAs FileName:="C:\WINDOWS\DESKTOP\Complete.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
My problem is that the macro stops on each save and displays “File Already Exists” box and asked if I want to overwrite it.
Is there a way to save the file using the above instructions and have it automatically overwrite the file. The macro is quite long and stops in the middle for this prompt. I would like to start the macro and walk away and have it finish.
Sorry for the length of the post but I thought I had better explain the whole problem. Any help would be greatly appreciated.
Workbooks.OpenText FileName:="C:\Daily\Complete.all", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
Array(10, 2), Array(11, 2))
After the macro makes changes it saves the file with the following instruction:
ActiveWorkbook.SaveAs FileName:="C:\Daily\Complete.all", FileFormat:=xlCSV, _
CreateBackup:=False
Then additional changes (like column headings and such) are made and the file is saved again as an Excel file.
ActiveWorkbook.SaveAs FileName:="C:\WINDOWS\DESKTOP\Complete.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
My problem is that the macro stops on each save and displays “File Already Exists” box and asked if I want to overwrite it.
Is there a way to save the file using the above instructions and have it automatically overwrite the file. The macro is quite long and stops in the middle for this prompt. I would like to start the macro and walk away and have it finish.
Sorry for the length of the post but I thought I had better explain the whole problem. Any help would be greatly appreciated.
