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.TransferSpreadsheet acExport, , "OpusRAMMExportTemp", strFileName, , , True
'DoCmd.OutputTo acOutputQuery, "qryOpusExportRAMMFormat", acFormatXLS, strFileName, True
MsgBox "Finished. Check spreadsheet in M:\ThamesC0901\Reports\..."