Determine whether the user has saved the file?

[Tim879]

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

 

[PCLIVE]

Maybe something like this:

If ActiveWorkbook.Saved = True _
Then
MsgBox ("Workbook has been saved.")
Else
MsgBox ("Workbook has NOT been saved.")
End If

HTH,
Paul

 

[Joel]

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]

Who new it was that easy??

thanks man.

Have a great day!
Tim

 

[Dave Peterson]

People can have some strange template names that would include the string "xls".

I'd look at the path.

A workbook that's never been saved won't have a path:

if activeworkbook.path = "" then
'never saved
else
'it's been saved at least once
end if