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

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

--

"Tim879" <(E-Mail Removed)> wrote in message
news:ed51f089-0680-4213-8674-(E-Mail Removed)...
> 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

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
>

Who new it was that easy??

thanks man.

Have a great day!
Tim
On Mar 5, 10:14 am, "PCLIVE" <pclive(RemoveThis)@cox.net> wrote:
> 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
>
> --
>
> "Tim879" <tim...@gmail.com> wrote in message
>
> news:ed51f089-0680-4213-8674-(E-Mail Removed)...
>
> > 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

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

Joel wrote:
>
> 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
> >

--

Dave Peterson