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

[mayoung]

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

 

[Yongle]

Your first question

Sub SaveFileAS()
    Dim wb As Boolean
    ChDir "C:\Folder\SubFolder"
    wb = Application.Dialogs(xlDialogSaveAs).Show
End Sub

Which can be developed further for a number of formats ( find the options here )

Sub SaveFileVarious()
    Dim wb As Boolean
    ChDir "C:\Folder\SubFolder"
'binary file
    wb = Application.Dialogs(xlDialogSaveAs).Show(Arg2:=xlExcel12)
'CSV
    wb = Application.Dialogs(xlDialogSaveAs).Show(Arg2:=xlCSV)
End Sub

Other formats (such as PDF) require a different approach

Sub SaveAsPDF()
    Dim fname As String, fpath As String
    fpath = C:\Folder\SubFolder"
    fname = InputBox("Enter name for PDF", "Save as PDF")
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fpath & "\" & fname & ".pdf"
End Sub

 

[mayoung]

Greeting Yongle,

Here is the code I tried. The
Dialog box opened but the file path did not follow it? The file path was the default one to the desktop?

Sub SaveFileAS()
Dim wb As Boolean
ChDir "C:\Users\Mark\OneDrive\Documents\Excel"
wb = Application.Dialogs(xlDialogSaveAs).Show
End Sub

 

[Yongle]

Please use code tags when posting code - it makes reading code easier (click on # icon above post window and paste code between the tags)

This works perfectly for me

Sub SaveFileAS()
    Dim wb As Boolean
    ChDir "C:\Users\yongle\OneDrive\Documents\Excel"
    wb = Application.Dialogs(xlDialogSaveAs).Show
End Sub

(For issue elimination purposes) please test again but use any other drive than OneDrive and let me know - the behaviour on your PC may be OneDrive related

You could also test if the path is being changed like this

    ChDir "C:\Users\Mark\OneDrive\Documents\Excel"
    MsgBox CurDir
    ChDir "C:\Users"
    MsgBox CurDir

 

[mayoung]

I tried the code again when the saveas dialog box appears the folder path is not correct it defaults to the desktop.
I also used your file folder path checker and the path stayed constant did nit change.

Anymore suggestions?

 

[Yongle]

This method works for me and has been tested with OneDrive
- amend the folder path and it should work for you

Sub FileSaveAs()
    Const fPath = "C:\Users\yongle\OneDrive\Documents\Excel"
    Dim fileSaveName As Variant, fileExt As String, filterStr As String, fFormat
[COLOR=#000080]'build string of accceptable formats[/COLOR]
    filterStr = "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm," + "PDF (*.pdf),*.pdf," + "CSV (Comma delimited)(*.csv),*.csv,"
'[COLOR=#000080]user provides the name (exit sub if user cancels)[/COLOR]
    fileSaveName = Application.GetSaveAsFilename(InitialFileName:=fPath, fileFilter:=filterStr)
        If fileSaveName = False Then
            MsgBox "name missing"
            Exit Sub
        End If
[COLOR=#000080]'create file based on user selection[/COLOR]
    fileExt = Right(fileSaveName, Len(fileSaveName) - InStrRev(fileSaveName, "."))
    
    If fileExt = "pdf" Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName
    Else
        Select Case fileExt
            Case "csv":     fFormat = xlCSV
            Case Else:      fFormat = xlOpenXMLWorkbookMacroEnabled
        End Select
        ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=fFormat
    End If
End Sub

 

[mayoung]

Will this still open the saveas dialog box so the person can name the document?

 

[mayoung]

Also am still learning this forum? Is there a quick way for me to navigate or see all my post without searching for them??

 

[Yongle]

Will this still open the saveas dialog box so the person can name the document?

How about testing the code

 

[Yongle]

Is there a quick way for me to navigate or see all my post without searching for them??

One way:
- go to post#7 above
- left-click on your name
- select View Forum Posts