File Save Dialog In Excel VBA

Question

I am trying to follow the tutorial here http://software-solutions-online.com/excel-vba-save-file-dialog-getsaveasfilename/ and have typed up my code to be:

varResult = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx")

Now when I compile the syntax I get an error of:

Method or Data Membor Not Found

On this specific element GetSaveAsFilename

I am running this in Access 2013 to save an Excel 2013 .xlsx - what should I change so this will prompt the user for a save name and location?

Answer 1

This particular method won't work in Access VBA. (VBA isn't 100% interchangeable between the Office products.)

Sample in Access VBA:

Sub TestFileDialog()
'requires Reference to "Microsoft Office xx.x Object Library"
    Dim strFilename As String
    With Application.FileDialog(msoFileDialogSaveAs)
        If .Show Then
            strFilename = .SelectedItems(1)
        Else
            MsgBox "No filename specified!", vbExclamation
            Exit Sub
        End If
    End With

    'do something with strFilename

End Sub

This should be adaptable to saving your Excel object from Access.