Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save to specific folder (Excel 2002)

    Is there a way to force a document created from a template to save to a specific folder. This is a shared template, which automatically tries to save to a person's own My Documents folder. How can I force the document to automatically navigate to a different folder so that everyone who creates this document will save it to the same folder. I've looked at some of the SaveAs methods, but can't find anything that seems appropriate.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save to specific folder (Excel 2002)

    Use this line in your code change file and path as appropriate

    activeworkBook.SaveAs Filename:="dathfilename.xls"

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to specific folder (Excel 2002)

    You most likely can use the Workbook SaveAs event routine, depending on exactly what you want to do. Before being specific, you need to answer a couple of questions:

    1- Do you want to suggest a folder but allow the user to change to another, or only allow the save to go to the particular folder?

    2- Do you want to use a specific file name, or let the user choose?
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to specific folder (Excel 2002)

    Thanks for your reply.

    I want the file to save to a specific folder, but I want the user to be able to determine the specific file name.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Save to specific folder (Excel 2002)

    The following should work in Excel 2002

    ChDir Path
    Application.Dialogs(xlDialogSaveAs).Show

    where Path his the folder you want to save to. The Save As Dialog opens and allows the user to enter a file Name.

    Andrew C

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to specific folder (Excel 2002)

    The code below, placed in the Workbook Before Save event routine should do what you want. It saves into the directory C:Work which can be changed to whatever directory you want.

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim vFileName As Variant
    If Not SaveAsUI Then Exit Sub
    Cancel = True
    ChDir ("C:Work")
    vFileName = Application.GetSaveAsFilename(Title:="SaveAs Filename")
    If vFileName = False Then Exit Sub
    vFileName = Right(vFileName, Len(vFileName) - InStrRev(vFileName, ""))
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename:="C:Work" & vFileName
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save to specific folder (Excel 2002)

    Unfortunately, that is not going to prevent the user from changing to another directory in the SaveAs dialog.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •