ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Determine whether the user has saved the file? (https://www.excelbanter.com/excel-programming/407160-determine-whether-user-has-saved-file.html)

Tim879
Determine whether the user has saved the file?
 
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
Determine whether the user has saved the file?
 
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" wrote in message
...
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



joel
Determine whether the user has saved the file?
 
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

Tim879
Determine whether the user has saved the file?
 
Who new it was that easy??

thanks man.

Have a great day!
Tim
On Mar 5, 10:14 am, "PCLIVE" 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" wrote in message

...

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
Determine whether the user has saved the file?
 
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


All times are GMT +1. The time now is 11:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com