Need vba code to open save as box and prefill file path only

mayoung

Active Member
Joined
Mar 26, 2014
Messages
259
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Is it possible to use VBA to open the save as dialog box with the file path already chosen so all the user has to do is name the file and pick file extension and save?

also if this can can done can the file extension be pre-chosen also?

Thanks
 
where do I put the default path to show in the dialog box? i see where to add the file naming schema.

The Original post worked for 1 and 2 but not 3.
1) File only saves in CSV
2) File name per my naming schema I determine
3) Folder path is pre-populated (harc coded)
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
As I understood, Yongle's original code worked fine for you but you were looking for an adjustment, and I quote ....
one variation i am looking for. Anyway to have the file name to pre-populate when I save as?
The code as in my post #20 provides exactly what you are looking for! Some explanation to give you more insight.
The "Example" procedure shows how the "FileSaveAs" function is used. In the sMyFile variable you have to put the full path and filename to be used (initially, since users can change that name due to the dialog). For example, when you prefer to save the workbook as a workbook without macros, you give the filename (in the variabele sMyFile) the .XLSX extension. The oWb variable has to have the reference to the workbook you wish to save, in the example the active workbook but you may change that to "ThisWorkbook" (ie the workbook in which your code is running) or whatever workbook you like. Those two variables are passed on to the "FileSaveAs" function as arguments.
The "FileSaveAs" function determines the type of file as of its extension given by you (xlsx, xlsm, csv) and passes this on to the Save As Dialog. The Dialog will open in the folder based on the path of the filename given by you, the File Name input field is populated with the filename given by you and the Save As Type input field is populated according to the file type given by you (xlsx, xlsm, csv).
As mentioned before, users may navigate to another folder on disk, users may change the filename and may change the file type, after all that's where a dialog is for. Anyway, at the moment the user has dicided to press the save button the "FileSaveAs" function saves the workbook according to the users input in the Dialog, returns to its caller (the "Example" procedure) and provides a string with the full filename of the saved file (drive:\folder\subfolder\filename.ext). At the moment the user on the other hand has dicided to close the Dialog or to press the Cancel button, the "FileSaveAs" function puts a message on screen and returns to its caller (the "Example" procedure) with a string without characters ("").
I can't explain it better than this.
 
Upvote 0
I fully understand the purpose and value of the save dialog box. I am maybe causing overkill where I wanted the beauty of a dialog box that automatically populates to a folder which will be the final destination. Maybe the better approach then is to have it just autosave in that location. My fear with that is if a file will be inadvertently overrode that is where I figured to use a dialogbox but pointing to the path to save. I wanted a level of risk control. I have a workbook with code that at the moment, exports a sheet into a new workbook renames the sheet and the final step is to rename the file, make it only save as .csv and to have it save in an entirely different folder than the original workbook with the code. Apologies if I am not grasping, because at the moment the code does only give me 2 of 3. And like you point out maybe the 3rd is useless seeing you give a user a dialogbox.
 
Upvote 0
I am maybe causing overkill where I wanted the beauty of a dialog box that automatically populates to a folder which will be the final destination.

Now I'm confused! The dialog does automatically populates to a folder you choose, by simply defining the full path of the file (of course the path must exist...).
 
Upvote 0
Maybe I am doing something wrong but I tried different variations of your Smyfile and they work separately but not together. EDIT: As I wrote this i found the ISSUE. See #3

1) If I put just the file name on Smyfile it populates the file name i.e. "TEST_" & Format(Now, "MMDDYY") but no folder location pre-populated
2) If I put the full location and file name it puts the folder location on the dialog box but no file name populating i.e. "C:\Windows\" & "TEST_" & Format(Now, "MMDDYY")
3) THIS WORKS, I was missing the .CSV extension: "C:\Windows\" & "TEST_" & Format(Now, "MMDDYY") & ".CSV"

Thank you consider this resolved and apologies for the extra head ache :)
 
Upvote 0
I could have seen that from your post #19 but I (also) overlooked it, so no need for apologies. Anyway, thanks for your feedback. Glad it works.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top