BTW:
putting this code in a BeforeSave macro will cause
the file to save itself TWICE.
(I'm surprised it doesn't get stuck in a loop)
Because:
BeforeSave means: "Do this before saving the file".
Then: in your macro, you're saving the file!
so it does it once WITHIN the macro, and once after the macro exits.
Not a problem with a small file, bit if it's large, it will take a while.
put a breakpoint at the end and watch the file date if you don't believe me.
To keep this from happening, anywhere within the macro, put:
cancel = true
Paul
----- Original Message ----
> From: Johnnyboy5 <intermediatec...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Sun, October 3, 2010 4:39:40 PM
> Subject: Re: $$Excel-Macros$$ save as cell A1 to to a specific drive - BUT
>excel then closes - why ?
>
> Thanks - nice one.
>
> Johnny
>
> On 3 Oct, 04:27, siti Vi <villager.g...@gmail.com> wrote:
> > Dim myPath As String, ThisFile As String
> > myPath = "H:\Temp\"
> > ThisFile = myPath & Range("A1").Value
> > Application.DisplayAlerts = False
> > ActiveWorkbook.SaveAs Filename:=ThisFile
> > Application.DisplayAlerts = True
> >
> > On Sat, Oct 2, 2010 at 6:34 PM, Johnnyboy5 <intermediatec...@gmail.com>
> > wrote:
> >
> >
> >
> > > Hi
> > > When I use the macro below it does save the file as per cell A1 and
> > > it does save it to the chosen drive. But it then closes Excel.
> >
> > > On checking the file location it is saved there.
> > > Any ideas what’s wrong.
> > > Thanks
> > > John
> >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
> > As Boolean)
> > > Dim FileSaveName
> > > ChDrive "H"
> > > ChDir "H:\Temp"
> > > ThisFile = Range("A1").Value
> > > ActiveWorkbook.SaveAs Filename:=ThisFile
> > > End Sub
>
> --
>----------------------------------------------------------------------------------
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
