transferspreadsheet filename optional? 1

[1DMF]

Hi,

I want to export to XLS a query and I want the user to have the option to chose where and what to save the file as.

According to :

https://msdn.microsoft.com/en-us/library/office/aa220766(v=office.11).aspx

FileName is 'optional' and I assumed that omitting it, means you get some sort of file dialog GUI to manage the filename and path.

However, when I use

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "myQueryNamne"

I get an error

The action or method requires a File Name argument

Colour me confused, because the docs say it is optional?

What error can't I see?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

I achieved what I wanted with this...

Global Const msoFileDialogSaveAs As Integer = 2

' export query to XLS
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "myQuery", SaveFileAs, True

Public Function SaveFileAs() As String

    Dim fd As Object
    
    On Error GoTo Err_SaveFileAs
 
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
 
    If fd.Show = True Then
        fd.AllowMultiSelect = False
        If fd.SelectedItems(1) <> vbNullString Then
            SaveFileAs = fd.SelectedItems(1)
        End If
    End If
 
Exit_SaveFileAs:

    Set fd = Nothing
    Exit Function
 
Err_SaveFileAs:

    MsgBox "Error " & Err & ": " & Error(Err)
    Resume Exit_SaveFileAs
    
End Function

Though the question still stands, why do I get an error for omitting what the docs say is an optional argument?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

The documentation here is somewhat more accurate

 

[1DMF]

Thanks Mike, it clearly states it is required in those docs...

The name of the spreadsheet file to import from, export to, or link to. Include the full path. This is a required argument.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music