I noticed that the email was off-line instead of to the group.. so I
included it here.
----------------------------------------------------------------------------------------------
My solution seems to work if the file hasn't been named, so I got more
elaborate:
--------------------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim FileSaveName
Cancel = True
ChDrive "H"
ChDir "H:\data"
FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls*), *.xls")
If FileSaveName <> False Then
MsgBox "Save as " & FileSaveName
Application.EnableEvents = False
ActiveWorkbook.SaveAs FileSaveName
End If
Application.EnableEvents = True
End Sub
-------------------------------------------------------
Hi Paul
Thanks - it works - but only if its on "C" drive or sub folders etc.
It doesn't like it if I use and "S" drive or a memory stick drive "F"
etc
any suggestions
John
On 30 September 2010 18:43, Paul Schreiner <schreiner_p...@att.net>
wrote:
> right-click on the sheet "tab" name.
> select "View Code"
>
> This will open the VBA editor
> the panel on the right side SHOULD list the sheet in the workbook
> as well as a "sheet" called "ThisWorkBook"
> (if you don't see this panel, hit Ctrl-R)
>
> double-click "ThisWorkbook"
>
> At the top of the large "editor" window, you'll see two pull-down lists.
>
> The left one says: (General)
> change it to "Workbook"
>
> By default it creates a Workbook_Open() event.
>
> you can delete this one.
>
> in the right-hand pull-down, select BeforeSave
>
> it will create:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> End Sub
>
> add the Chdir line with the appropriate folder name so it looks like:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> Chdir "C:\temp"
> End Sub
>
>
> save the template and you should be on your way!
>
> Paul
> ----- Original Message ----
>> From: Johnnyboy5 <intermediatec...@gmail.com>
>> To: Paul Schreiner <schreiner_p...@att.net>
>> Sent: Thu, September 30, 2010 1:18:09 PM
>> Subject: Re: $$Excel-Macros$$ macro to save it to a particular drive / file
>>location.
>>
>> Thanks,
>>
>> I understand the idea but dont know how to write the macro to do it.
>>
>> many thanks
>>
>> John
>>
>> On 30 Sep, 13:19, Paul Schreiner <schreiner_p...@att.net> wrote:
>> > If you're allowing the users to define the name of the file they're saving
>> > (instead of having the program do it)
>> > then, in someplace like the Workbook_BeforeSave event,
>> > use:
>> >
>> > Chdir "C:\temp"
>> >
>> > (or whatever you want the "default" folder to be)
>> >
>> > Paul
>> >
>> >
>> >
>> >
>> >
--
----------------------------------------------------------------------------------
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
