KarenPRuskin
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect solution! Thanks so much, I'll use it again and again.
(See the help files on both Methods for more info)