Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Determine whether the user has saved the file?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to add user input to determine a variable in a macro Needles Excel Programming 0 September 6th 06 07:42 PM
how to determine if user has Outlook or Lotus Notes jseven Excel Programming 1 July 1st 06 01:23 AM
Determine if XL file opened from Outlook or a saved file quartz[_2_] Excel Programming 1 September 16th 05 09:50 PM
Excel file opened as read-only, if saved by another user GeEf Excel Discussion (Misc queries) 2 August 28th 05 04:29 PM
determine what sheet user is viewing Elias Excel Programming 1 September 27th 03 12:40 AM


All times are GMT +1. The time now is 01:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement