A newworkbook won't have xls in the file name try this instead

if instr(CurrentWBName,"xls") 0 then
NewFileName = WorksheetFunction.Substitute(CurrentWBName,
".xls", "") & ".csv"
else
NewFileName = CurrentWBName & ".csv"
end if

"Tim879" wrote:

Hey

Does anyone know how to determine if the user has saved the excel file
they are working in?

I have a macro that saves the current tab as a CSV file for upload,
but if the user hasn't saved the file the code doesn't work.

the code is posted below.
Thanks
Tim
Sub Save_As_CSV()
'saves the current tab as a CSV tab.
Dim CurrentDay, CurrentMonth, CurrentSheet, CurrentWBName, CurrentDate
As String
Dim continue As Integer
Dim NewFileName As String


continue = MsgBox("Do you want to save this tab as a CSV file?",
vbYesNo)

If continue = vbYes Then

Dim wsht As Worksheet


Application.DisplayAlerts = False
Application.ScreenUpdating = False

CurrentWBName = ActiveWorkbook.FullName
CurrentSheet = ActiveSheet.Name

Set wsht = Sheets.Add
wsht.Name = "CSV-Temp"


wsht.Select
Cells.Select
Selection.NumberFormat = "@"

Sheets(CurrentSheet).Select

Cells.Select
Selection.Copy

wsht.Select

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' paste values


NewFileName = WorksheetFunction.Substitute(CurrentWBName,
".xls", "") & ".csv"
' NewFileName = Left(CurrentWBName, Len(CurrentWBName) - 4) &
".csv"

MsgBox NewFileName
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
NewFileName, FileFormat:=xlCSV, CreateBackup:=False


Sheets(CurrentSheet).Select

wsht.Delete

ActiveWorkbook.SaveAs Filename:= _
CurrentWBName, FileFormat:=xlNormal, CreateBackup:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Range("a1").Select

MsgBox ("CSV saved under: " & NewFileName)

Else
MsgBox ("Cancelled")
End If
End Sub