+ Reply to Thread
Results 1 to 2 of 2

Set drive/path when opening xlDialogSaveAs

Hybrid View

  1. #1
    Registered User
    Join Date
    12-18-2017
    Location
    Martinsburg, WV
    MS-Off Ver
    10
    Posts
    14

    Set drive/path when opening xlDialogSaveAs

    I have not been able to figure out how to get VBA to set the Drive and Path where I want to save the file.
    Here is a sample of my attempts.

    - - - - - - - - My Current Code - - - - - - - - - - - - -
    Sub Auto_Open()
    '========================================================
    ' SaveAs to protect master document
    '--------------------------------------------------------
    On Error GoTo ENDTHIS
        vCheck = ActiveWorkbook.Name
     If Not vCheck Like "*Master*" Then Call mSetQrtr: Exit Sub
        vAppName = "Urine Contamination Rates"
        vDate = Date
        vYear = InputBox("! ! ! Welcome to the " & vAppName & " Workbook" & vbCrLf + vbCrLf & " Please enter the Fiscal Year for which to Create this workbook.", "Create New Fiscal Year for this new Workbook", Year(vDate) + 1)
        If StrPtr(vYear) = 0 Then Exit Sub
        Sheets(1).Range("D1").Value = vYear
        vFilePath = "L:\Micro\Urine Contamination Files\"      '\\r04mwvnas21.v05.med.va.gov\mwv_groups\P&LMS\Micro\Urine Contamination Files\
        vFileNm = vYear & "-FY " & vAppName & ".xlsm"
        If vFileNm = vbNullString Then MsgBox "You have ended this Macro. Nothing has been done."
        If vFileNm = vbNullString Then GoTo ENDTHIS
        ChDrive "L"
        ChDir vFilePath
        Application.Dialogs(xlDialogSaveAs).Show vFileNm
    ENDTHIS:
        Range("A1").Select
        Range("B5").Select
        Call mSetQrtr
    End Sub
    - - - - - - - - - - - End of Sample Code - - - - - - -
    Last edited by alansidman; 07-16-2021 at 01:17 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    23,983

    Re: Set drive/path when opening xlDialogSaveAs

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] path to pen drive
    By tusharsinghal in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-16-2014, 05:32 AM
  2. [SOLVED] Find the Empty Folders -Particular path/Drive
    By laxmanann in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 06-06-2014, 10:57 PM
  3. Links Error: Remote Path vs T:Drive Path
    By bbarrene in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-11-2010, 11:18 AM
  4. Find File Path to USB Drive
    By andyrads in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-15-2009, 05:32 PM
  5. Network Drive Path
    By smarta in forum Excel General
    Replies: 3
    Last Post: 04-19-2007, 11:14 AM
  6. [SOLVED] Path to a network drive
    By CWillis in forum Excel General
    Replies: 2
    Last Post: 06-22-2006, 10:10 PM
  7. Drive and full path in name at top of screen
    By Mervyn Thomas in forum Excel General
    Replies: 14
    Last Post: 08-03-2005, 11:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1