Results 1 to 4 of 4

VBA to prompt user to select location to save file

Threaded View

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2007
    Posts
    51

    VBA to prompt user to select location to save file

    Hi,

    I'm trying to figure out how to have VBA check the path before it saves and it does not match, then a pop up box opens and the user can select the drive and folder where they want the file saved.

    Right now I have the location hard coded with the path to my C drive. Now several people need to use this VBA code and I don't want them to go into the VBA to change the path.

    Any help would be greatly appreciated.

    Here is my code
    Sub MakeNewFile()
     
        Dim FName           As String
        Dim ShtName         As String
        Dim FPath           As String
        Dim NewBook         As Workbook
        Dim TDate           As String
    
        Application.ScreenUpdating = False
        FPath = "C:\Users\myuserID\Desktop"
     
        Range("A2:O100").Copy
        Set NewBook = Workbooks.Add
        Sheets.Add.Name = Format(Date, "mm-dd-yy")
    
        With NewBook.Sheets(1).Range("A2:O100")
        ActiveSheet.Paste
        Cells.Select
        Range("A2").Activate
        Cells.EntireColumn.AutoFit
        Cells.Select
        ActiveWindow.Zoom = 75
        ActiveWorkbook.SaveAs Filename:=FPath & "\" & "MS_Vetting_Request_" & Format(CStr(Now()), "mm.dd.yy") & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=True
        Application.ScreenUpdating = True
    
        MsgBox "Exported to Desktop"
        
    
        End With
    
      
    End Sub
    Last edited by Bevg; 04-04-2019 at 04:25 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA - Save File Location Prompt
    By moni_tm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2017, 12:36 PM
  2. Replies: 1
    Last Post: 06-20-2017, 10:28 AM
  3. [SOLVED] Macro to prompt user to select a file and the selected file should saved in location
    By logesh excel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-26-2015, 03:43 PM
  4. [SOLVED] Macro to prompt user to select date and save file
    By TheLittlePrince in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-04-2013, 08:34 AM
  5. [SOLVED] Macro to prompt user for folder location and save acitve workbook.
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2013, 06:20 PM
  6. Macro to prompt user for file name and save in location
    By Fungijus in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-21-2011, 02:16 PM
  7. How to prompt user for location of file to reference
    By SymphonyTomorrow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-22-2010, 01:23 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