We help IT Professionals succeed at work.

Excel VBA SaveAs Dialog box

This Sub Open the SaveAS dialog box but when I click on Save, it doesn't Save.  

   fDialog.InitialFileName = "\\xx\xxxx\xxx\xxxx\Monthly_Reports\MonthlyReportDetails_"
    ret = fDialog.Show
‘Save File

Any Suggestion?


Sub myFolder()
    Dim sFolderName As String, fDialog As FileDialog, ret As Long
    Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
    
    fDialog.InitialFileName = "\\xx\xxxx\xxx\xxxx\Monthly_Reports\MonthlyReportDetails_" 
    ret = fDialog.Show
‘Save File



    Sheets("RDBMergeSheet").Select
    Sheets("RDBMergeSheet").Copy    
    ActiveSheet.Name = "Pairouts"

    fDialog.InitialFileName = "\\xx\xxxx\xxx\xxxx\Monthly_Reports\MonthlyReport_xxxxxx"
    ret = fDialog.Show]

‘Save File

End Sub

Open in new window

Comment
Watch Question

Top Expert 2011

Commented:
(1) There is an extra ] in the second fDialog.Show
(2) your 'Save file, if these are comments they need to have a '
(3) Try setting the location of the file and the main part of the filename in a variable like:

myfile = "C:\test\Monthly_Reports\MonthlyReportDetails_"
    fDialog.InitialFileName = myfile
   
LMK if these corrections sort this out

Author

Commented:
The file name will change each time.
Is this what your looking for. Make sure your macro setings are set to Medium and enable macro when prompt clcik on the command button SaveAs and check the reuslts.
gowflow
MonthlyReportDetails-.xls
Any chance you had to try this solution pls revert will feedback and will adapt if need further
gowflow

Author

Commented:
Thank you