Open the "Export to Excel" window

[penfold1992]

Is there a command that I can use to export a spreadsheet to Excel...

I could use
docmd.transferspreadsheet

however that would also mean i would need an input window where users would need to manually put in the location they wish to save to...

Instead, could i not get a "SaveAs" command window or a file browser at least for them to search that way?
Alternatively, If it was possible to use VBA to pop up the "Export - Excel Spreadsheet" window, that would be just as good.

Thanks again!

 

[pr2-eugin]

penfold1992, I use the following method, to get the SaveAs dialog.. A customized Export, still using TransferSpreadsheet command..

Public Sub SPLexportXL(nameStr As String)  
[COLOR=Green]' This method is used for auto export of the data.
' To call this method simply use SPLexportXL "myFileName"
' Code Courtesy of
'  Paul Eugin[/COLOR]
On Error GoTo errHandle
    Dim fileDump As FileDialog
    Dim dd As Integer
    Set fileDump = Application.FileDialog(msoFileDialogSaveAs)
    fileDump.InitialFileName = nameStr & ".xls"
    dd = fileDump.Show
    If dd = -1 Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "yourTableName", fileDump.SelectedItems(1), 1
        MsgBox "File has been exported", vbInformation
    Else
         MsgBox "EXPORT WAS NOT SUCCESSFUL !!" & vbCrLf & vbCrLf & "Please perform a manual EXPORT", vbCritical
    End If
exitOnErr:
    Exit Sub
errHandle:
    MsgBox "Error occured, process terminated" & vbCrLf & vbCrLf & _
           "Error (" & Err.Number & ") : " & Err.Description, vbCritical
    Resume exitOnErr
End Sub

Make sure you add.. Microsoft Office X.X Object Library reference under Tools->Reference.. Where X.X represents the Version number..

 

[dfsabrown]

Hi Paul, i am using the above to transfer data from Access to Excel essentially because if i export a report fields are truncated to 255. Anyway, is it possible to do exactly the same but export the results of a union query, not a table? many thanks