Re: Beforesave - Save copy on my desktop

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Are you sure you want SaveAs rather than SaveCopyAs? Also, you should disable events before doing the save -- otherwise you'll loop.

Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, _
ByRef Cancel As Boolean)
Dim FName As String
On Error GoTo EXIT_SUB:
Application.EnableEvents = False
Application.DisplayAlerts = False
Set WshShell = CreateObject("WScript.Shell")
FName = WshShell.SpecialFolders("Desktop") & _
"\" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs Filename:=FName
EXIT_SUB:
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"J.W. Aldridge" <jeremy.w.aldridge@xxxxxxxxx> wrote in message news:1190840243.691957.161970@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi.

I have this code that i was running "beforeclose", however I adjusted
it to try to make it a "beforesave" code however, it doesnt work. No
error messages, but when i check my desktop, its not there.


Any advice?


Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef Cancel As
Boolean)
Application.DisplayAlerts = False
Set WshShell = CreateObject("WScript.Shell")
ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") &
"\" & ThisWorkbook.Name
Application.DisplayAlerts = True
End Sub


.

Relevant Pages

  • Re: After Save - 1 more question
    ... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As ...
    (microsoft.public.excel.programming)
  • Re: A little challenge for relativists.
    ... The Moon is 1.2 light seconds from Earth, ... > Dim redfirst As Boolean, bluefirst As Boolean, bluesecond As ... > Private Sub Command1_Click ...
    (sci.physics.relativity)
  • Re: Sorting a System.Collections.ObjectModel.Collection
    ... Public Sub Dispose() Implements System.IDisposable.Dispose ... Private m_SupportsSorting As Boolean = True ... Protected Overrides ReadOnly Property SupportsSortingCoreAs Boolean ... Dim m_SortList As New ArrayList ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Sorting a System.Collections.ObjectModel.Collection
    ... Public Sub Dispose() Implements System.IDisposable.Dispose ... Private m_SupportsSorting As Boolean = True ... Protected Overrides ReadOnly Property SupportsSortingCoreAs Boolean ... Dim m_SortList As New ArrayList ...
    (microsoft.public.dotnet.languages.vb)
  • Re: COM+, CRMs and Stuff
    ... Public Shared StartPath As String ... Public Sub New ... Dim _aRecord As New aRecord ... Function PrepareRecordAs Boolean ...
    (microsoft.public.dotnet.languages.vb)