4

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?

IcyPopTarts
  • 494
  • 1
  • 12
  • 25
  • Strange that it wouldn't work if you coped the example. Did you declare the object `varResult` (like in the example)? – ashleedawg Dec 25 '17 at 04:55
  • @ashleedawg - yes I declared it as Variant, just like the example showed. Could it be the fact I am calling this method from Access to capture the save file name/location for an Excel file? – IcyPopTarts Dec 25 '17 at 04:56

2 Answers2

2

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.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
0

Another question, “Save as…” dialog box in MSAccess vba: how? on this website has some useful pointers that could help you; I had a similar problem and this solved it for me! Like ashleedawg said, VBA syntax isn't exactly the same across different applications.

You may also try using the Application.FileDialog msoFileDialogSaveAs but make sure to include the library which contains these symbols into your project. Most likely, Access will generate a prompt informing you to add the library reference to the project. the only issue would be that you can’t apply file type filters. If that is something which is important to do, take a look at this guide on the microsoft forums. Display Open and Save As Dialog Boxes in Access with API Functions.

tanvi
  • 568
  • 2
  • 11
  • 32
  • thanks, but now I'm gonna feel like a jerk because I'm supposed to "flag" your answer... :-( Actually I'll leave it, but someone else is bound to notice that it falls into the category "not an answer"... but, you can still fix it! :-) It should either be changed to a *comment* or else [edit]ed to expand on the information. If it wasn't a S.O. link, I;d suggest copy/pasting in some information from the linked page (partly since links don't last forever), but in this case if you feel the question you linked covers the same as this question, you could flad this question as "duplicate". – ashleedawg Feb 22 '18 at 18:38
  • More info: "[mcve]" and also "[Are answers that just contain links elsewhere really “good answers”?](https://meta.stackexchange.com/a/8259/370758)". Welcome, by the way! – ashleedawg Feb 22 '18 at 18:42
  • @ashleedawg thanks for the info! I'm relatively new to the site and will keep it in mind:) – tanvi Feb 23 '18 at 14:51