asked on

DoCmd.TransferSpreadsheet Function

I'd like the spreadsheet to open immediately for the user once they click this button, the same as happens using Docmd.OutputTo. Is there a parameter I'm missing ?? See attached code. You'll see I've replaced the Docmd.OutputTo with DoCmd.TransferSpreadsheet. Ths is because a memo field gets truncated with the Docmd.OutputTo function if it's bigger than 255 characters, but works sweet using DoCmd.TransferSpreadsheet. So if you can enlighten me on how to automatically open the spreadsheet I'd appreciate it greatly!

Private Sub btnOpusRAMMFormat_Click()
    Dim strInput As Integer
    Dim strFileName As String
    Dim strText As String
    On Error Resume Next
    strInput = InputBox("Enter Status")
    Forms![FrmMisc]![txtStatus] = strInput
    strText = InputBox("Enter any text you might want this included in the file name, eg Nov2007.  DO NOT INCLUDE ANY BLANKS!!")
    strFileName = "M:\ThamesC0901\Reports\OpusRAMMExportStatus" & strInput & strText & ".xls"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "delOpusRammExport"
    DoCmd.OpenQuery "appOpusExport"
    DoCmd.OpenQuery "updOpusRammStep1"
    DoCmd.OpenQuery "updOpusRammStep2"
    DoCmd.OpenQuery "delOpusRammExportTemp"
    DoCmd.OpenQuery "appOpusExportTemp"
    
    DoCmd.TransferSpreadsheet acExport, , "OpusRAMMExportTemp", strFileName, , , True
   
    'DoCmd.OutputTo acOutputQuery, "qryOpusExportRAMMFormat", acFormatXLS, strFileName, True
    MsgBox "Finished. Check spreadsheet in M:\ThamesC0901\Reports\..."
    DoCmd.SetWarnings True

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

JAMcDo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

You cannot open the spreadsheet automatically with Transfer spreadsheet
(See the help files on both Methods for more info)

KarenPRuskin

ASKER

Perfect solution! Thanks so much, I'll use it again and again.