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

 

[mayoung]

I will try when I get to work. Thank You so much!

 

[mayoung]

This code worked perfectly!!! Thank You so much Yongle....

 

[Yongle]

You are welcome. Thanks for the feedback

 

[mayoung]

Yongle,
Sent You a Private Message please check..
Thanks

 

[ItalianPlatinum]

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

So Yongle this code you sent works great one variation i am looking for. Anyway to have the file name to pre-populate when I save as?

Sub sbSaveExcelDialog()

Dim wb As Boolean
ChDir MY PATH"
wb = Application.Dialogs(xlDialogSaveAs).Show(Arg2:=xlCSV)
End Sub

 

[GWteB]

Are you looking for something like this?

Sub sbSaveExcelDialog()
    Dim bResult     As Boolean
    Dim sFileName   As String
    Dim sPath       As String

    sPath = "YOUR PATH"
    sFileName = "MyFileName.csv"
    
    ChDir sPath
    bResult = Application.Dialogs(xlDialogSaveAs).Show(Arg1:=sFileName, Arg2:=xlCSV)
End Sub

 

[ItalianPlatinum]

Doesn't seem to be working

 

[GWteB]

Did you change the sPath= and sFileName= lines according to your situation? What exactly doesn't work? Are you getting an error?

 

[ItalianPlatinum]

I think its my file name. trying to have it say a fixed text and then the date so example:

TEST_032220

sFileName = "Test_" & "Format (NOW() "MMDDYY")"

 

[GWteB]

That should not be the cause of the problem, unless you did not provide a "\" between your path and your filename. Anyway, sometimes this dialog function can be doing unexpected things. The following might be a better alternative. This procedure saves the active workbook based upon the user's choice with a pre populated Save As Dialog.

Public Sub Example()

    Dim oWb         As Workbook
    Dim sMyFile     As String
    Dim sSavedFile  As String

    sMyFile = "C:\Users\ItalianPlatinum\Documents\MyData.CSV"   ' <<< change as required
   
    Set oWb = ActiveWorkbook                                    ' <<< change as required

    ' return with drive:\folder\filename.ext  of saved file
    sSavedFile = FileSaveAs(oWb, sMyFile)

    Set oWb = Nothing
End Sub


Public Function FileSaveAs(argWbk As Workbook, argFileFullName As String) As String

    Dim sFileName   As String
    Dim sFileExt    As String
    Dim sFilters    As String
    Dim iFiltIx     As Integer
    Dim vFormat     As Long

    ' compose string of used formats
    sFilters = "Excel Workbook (*.xlsx),*.xlsx," + _
               "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm," + _
               "CSV (Comma delimited)(*.csv),*.csv,"
    ' get initial file extension
    sFileExt = Right(argFileFullName, Len(argFileFullName) - InStrRev(argFileFullName, "."))
    ' set current filter accordingly
    Select Case LCase(sFileExt)
        Case "xlsx":    iFiltIx = 1
        Case "xlsm":    iFiltIx = 2
        Case "csv":     iFiltIx = 3
        Case Else:      iFiltIx = 1
    End Select
    ' user provides final folder and file name
    sFileName = Application.GetSaveAsFilename(InitialFileName:=argFileFullName, _
                                              FileFilter:=sFilters, _
                                              FilterIndex:=iFiltIx)
    If sFileName = "False" Then
        MsgBox "User has canceled.", vbExclamation, "SaveAs"
        FileSaveAs = ""
        GoTo SUB_DONE
    End If
    ' get final file extension
    sFileExt = Right(sFileName, Len(sFileName) - InStrRev(sFileName, "."))
    ' save file as requested
    Select Case LCase(sFileExt)
        Case "xlsx":    vFormat = xlOpenXMLWorkbook
        Case "xlsm":    vFormat = xlOpenXMLWorkbookMacroEnabled
        Case "csv":     vFormat = xlCSV
        Case Else:      vFormat = xlOpenXMLWorkbook
    End Select
    argWbk.SaveAs Filename:=sFileName, FileFormat:=vFormat
    FileSaveAs = sFileName
SUB_DONE:
End Function