Save as CSV

[broogle]

I am trying to save a sheet to CSV file (users are free to choose the
location to save this file). I am able to do it, but when I open the
file, only the extension is CSV but the format still XLS (all formula,
, color, format still there).
Any guru can help? Thanks.

Private Sub CommandButton1_Click()

On Error GoTo errHandler:

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim mysave, filesavename, myLocation, myFileName
Dim Cancel As Boolean

myFileName = Sheet1.Range("B4") & Sheet1.Range("B1")
mysave = MsgBox("Please chose location to save CSV file!", vbOKCancel)

If mysave = vbCancel Then
GoTo errHandler
End If

Sheets("Section").Select
ActiveSheet.Copy

SavingFile:

filesavename = Application.GetSaveAsFilename(myFileName, _
fileFilter:="CSV (Comma Delimited) (*.csv), *.csv")

If filesavename <> False Then
Application.DisplayAlerts = False
Dim resp As Long
resp = vbYes
If Dir(filesavename) <> "" Then
resp = MsgBox(Prompt:=filesavename & " already exist,
overwrite?", Buttons:=vbYesNo)
End If

If resp = vbYes Then
ActiveWorkbook.SaveAs filesavename
myLocation = ActiveWorkbook.Path
ActiveWorkbook.Close
MsgBox (myFileName & ".csv was saved in " & myLocation)
Sheets("Department").Select
Application.EnableEvents = True
Application.DisplayAlerts = True
Else
GoTo SavingFile
End If

Else
Cancel = True
Application.DisplayAlerts = False
ActiveWorkbook.Close
Sheets("Department").Select
GoTo errHandler
End If

errHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = False
End Sub

 

[JE McGimpsey]

Take a look at the SaveAs method in XL/VBA Help.

You need to use the fileformat argument...