Export Access Report to Excel and Automatically Open

[aas1611]

Hi,

I have read thread702-587703 and found it very useful in exporting a report to Excel from Access.

Is there any way to automatically open that Excel file from Access? If any, can someone give me such codes?

Thanks.

 

[BobJacksonNCI]

I'm on my way out the door, so this is brief.
It sounds like you already have the means to transfer the data to Excel.

If so, you just need to set the autostart parameter to True.

HTH,
Bob

Include the following instruction with required parameters in event code under a button.

DoCmd.OutputTo objecttype[, objectname][, outputformat][, outputfile][, autostart][, templatefile]

The OutputTo method has the following arguments.

Argument Description

objecttype One of the following intrinsic constants:
acOutputDataAccessPage
acOutputForm
acOutputModule
acOutputQuery
acOutputReport
acOutputServerView
acOutputStoredProcedure
acOutputTable

objectname A string expression that's the valid name of an object of the type selected by the objecttype argument. If you want to output the active object, specify the object's type for the objecttype argument and leave this argument blank.
If you run Visual Basic code containing the OutputTo method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.

outputformat One of the following intrinsic constants:
acFormatASP
acFormatDAP
acFormatHTML
acFormatIIS
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS
If you leave this argument blank, Microsoft Access prompts you for the output format.

outputfile A string expression that's the full name, including the path, of the file you want to output the object to.
If you leave this argument blank, Microsoft Access prompts you for an output file name.

autostart Use True (–1) to start the appropriate Microsoft Windows–based application immediately, with the file specified by the outputfile argument loaded. Use False (0) if you don't want to start the application. This argument is ignored for Microsoft Internet Information Server (.htx, .idc) files and Microsoft ActiveX Server (*.asp) files.
If you leave this argument blank, the default (False) is assumed.

templatefile A string expression that's the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file.

 

[aas1611]

Well, I used a method that put the data one cell at a time. Is there any better way to export to excel? (I have already had the Access report for it. All I want to do is copy/export this Access report to Excel).

The Office Links - analyze with Excel step is not good enough.

Maybe you can give an example how to use DOcmd.OutputTo method.

Thanks
Andre

 

[alpder]

My method is a little cruder, but works exceptionally well.
With code in a module, I export a table or query to excel. Then I transfer control to excel and quit Access. Set the excel file to auto open.

Code:
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97, "MAINTENANCE", "P:\CUSAGENT.XLS", True

trans = Shell("C:\Program Files\Microsoft Office\office\Excel.exe P:\NDAILY\CUS.XLS", 1)
Quit acQuitSaveAll

Hope this is useful.

 

[hymn]

put the code below on a button
DoCmd.RunCommand acCmdOutputToExcel
this will send whatever records there are to excel and open excel up
use variations if you require specifics

Hope this helps
Hymn