FORCE a file to Save ??
Posted by Pete Wilshaw on April 24, 2001 6:24 AM
I need to create a macro that opens a csv file, formats it, and then closes it. Simple, eh ? - Well I'm having some problems with the 'Save' part....
When I try and save the file it keeps producing dialog boxes such as "<filename> is not in MS Excel format, are you sure you want to save", and the "Save As" dialog box appears as well.
Anyone know how to force Excel to save my .csv file from within a macro with no questions asked ?
Regards,
Pete.
Posted by JAF on April 24, 2001 6:52 AM
Pete
The following should do what you need...
Sub Open_CSV_Format_Save_Close()
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\My Documents\SourceFile.csv"
''
'formatting actions
Range("A1:C1").Font.Bold = True
''
ActiveWorkbook.SaveAs Filename:="C:\My Documents\CreatedFile.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
MsgBox "File has been opened, formatted and saved."
End Sub
Posted by Pete Wilshaw on April 24, 2001 7:15 AM
Thx for your help JAF, unfortunately this saves the file back to XLS format, I need to be able to format the file and then save it as a CSV file once again....The problem occurs when the macro saves the file as FileFormat:=xlCSV.
Any Ideas ??
Posted by JAF on April 24, 2001 8:55 AM
This should do the trick...
Pete
Try this. The line Application.DisplayAlerts = False will disable any alerts that Excel normally throws up. It's VERY important to put in the line at the end of the macro to set Alerts back to True!!
JAF
Sub Open_CSV_Format_Save_Close()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\My Documents\SourceFile.csv"
''
'formatting actions
Range("A1:C1").Font.Bold = True
''
ActiveWorkbook.SaveAs Filename:="C:\My Documents\CreatedFile.csv", _
FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "File has been opened, formatted and saved."
End Sub
Posted by Pete Wilshaw on April 24, 2001 9:39 AM
Re: This should do the trick...
Many Thanks for your help - that works fine :)
Pete.
