 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Exporting Data to Excel
I am looking for code that will allow the user to export a query to Excel, however first receive a Save As prompt. The DoCmd.TransferSpreadsheet acExport code requires a file name to export to. Is there code that will export without having to specify a file name in the code, and also prompt the user with Save As? Thanks, Matt A real simple way would be to let the user enter a filename using the InputBox function as part of the expression that provides the path and filename to the TransferSpreadsheet's argument. If you want to allow the user to see a Windows navigation browse window, that's much more complicated.  SignatureKen Snell <MS ACCESS MVP> >I am looking for code that will allow the user to export a query to Excel, > however first receive a Save As prompt. The DoCmd.TransferSpreadsheet [quoted text clipped - 5 lines] > Thanks, > Matt Hi Matt, As an alternative to TransferSpreadsheet, you can use the OutputTo method, without specifying the optional OutputFile parameter: DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryFiles", _ OutputFormat:=acFormatXLS, AutoStart:=False Note: The ObjectType parameter must match a valid ObjectName. Use acOutputTable if you wish to specify a table name as the ObjectName. Tom ________________________________ I am looking for code that will allow the user to export a query to Excel, however first receive a Save As prompt. The DoCmd.TransferSpreadsheet acExport code requires a file name to export to. Is there code that will export without having to specify a file name in the code, and also prompt the user with Save As? Thanks, Matt Tom, Your suggestion works great. The only issue is if a I click Cancel in the Output to prompt, I will get an error message stating Output to has been canceled. I am currently using On Error Resume Next code before the docmd. I try to avoid this code as much as I can, is there anothe method to use? Thanks a lot! Matt > Hi Matt, > [quoted text clipped - 18 lines] > Thanks, > Matt Hi Matt, Sure, all you need to do is trap for error 2501, and do nothing as a response. Notice that there is no message box statement in the error handler for Case 2501: Option Compare Database Option Explicit Sub ExportToExcel() On Error GoTo ProcError DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryFiles", _ OutputFormat:=acFormatXLS, AutoStart:=False ExitProc: Exit Sub ProcError: Select Case Err.Number Case 2501 ' User clicked on cancel Case Else MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure ExportToExcel..." End Select Resume ExitProc End Sub ________________________________ Tom, Your suggestion works great. The only issue is if a I click Cancel in the Output to prompt, I will get an error message stating Output to has been canceled. I am currently using On Error Resume Next code before the docmd. I try to avoid this code as much as I can, is there anothe method to use? Thanks a lot! Matt "Tom Wickerath" wrote: > Hi Matt, > [quoted text clipped - 18 lines] > Thanks, > Matt 2nd attempt to post reply (a reply posted over 30 minutes ago is still not showing up in my newsreader)... Hi Matt, Sure, all you need to do is trap for error 2501, and do nothing as a response. Notice that there is no message box statement in the error handler for Case 2501: Option Compare Database Option Explicit Sub ExportToExcel() On Error GoTo ProcError DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryFiles", _ OutputFormat:=acFormatXLS, AutoStart:=False ExitProc: Exit Sub ProcError: Select Case Err.Number Case 2501 ' User clicked on cancel Case Else MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure ExportToExcel..." End Select Resume ExitProc End Sub ________________________________ Tom, Your suggestion works great. The only issue is if a I click Cancel in the Output to prompt, I will get an error message stating Output to has been canceled. I am currently using On Error Resume Next code before the docmd. I try to avoid this code as much as I can, is there anothe method to use? Thanks a lot! Matt I am trying to find an alternative to TransferSpreadsheet that will allow me to format the Excel Data before it drops the file so that I can then use SendObject to email the Formatted Excel Workbook. Does this OutputTo method result in an Excel Object and how would I reference that in VBA? This is same issue I opened a thread on earlier this morning but am trying to resolve with research. Thanks in advance. SignatureJeff C Live Well .. Be Happy In All You Do > Hi Matt, > [quoted text clipped - 18 lines] > Thanks, > Matt OutputTo is what happens in code when you use the menu object: Tools >>> OfficeLinks >>> Analyze with Excel DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\PathToFile", True The last argument (True) will open the resulting Excel file, from which you can then do a File Send and get the Excel equivalent of SendObject. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >I am trying to find an alternative to TransferSpreadsheet that will allow >me [quoted text clipped - 35 lines] >> Thanks, >> Matt Thanks Arvin: I am looping though a large dataset and am hoping to come up with a way to format an Excel object before saving it as a file so I can attach it as a variable in the SendObject method SignatureJeff C Live Well .. Be Happy In All You Do > OutputTo is what happens in code when you use the menu object: > [quoted text clipped - 44 lines] > >> Thanks, > >> Matt |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.