Export Report to Excel (1 Viewer)

I have a report that I export to excel by using:

DoCmd.OutputTo acOutputReport, "rptletterdate", acFormatXLS, "H:\Excel\GovernmentManagedCare\rptletterdate.xls", True

This works fine, but every time I export the file, I get a msgbox in Excel that tells me "File error. Some number formats may have been lost."

I understand that the formatting does not get exported to Excel and that's fine...it's not affecting this particular set of data in any noticeable way. I just would like to turn off the annoying warning msg once Excel opens up. Isn't there a way to do this through VBA?

Thanks for any suggestions!

If you want the excel spreadsheet to not display the message on open then create a macro in excel called auto_open (similar to autoexec in access)enter the line
Application.DisplayAlerts = False
this will turn the msg off.

Be sure to turn it back on at some stage
Application.DisplayAlerts = True
othewiswe excel wont tell you when somethings wrong.Hope this helps

Thanks for the info, mcgrco. I tried to do what you said (first time using Excel VBA!). But I think there are two problems:
1) I think that Access is outputting the file as an Excel 5.0/95 Workbook, even tho' it's Access 97. When I try to save the new macro, Excel wants me to save it in a higher format.
2) I think that the file is just getting overwritten each time the report is output.

Is there any way to turn off warnings from the Access side of things? And is there any way to make Access output the report as an Excel 97 workbook?

Thanks

Have a 'master' Excel file which contains the auto_open macro.
Export your data as Excel format to another, temporary, file.
Launch Excel's master spreadsheet from Access.
The masters' auto_open macro will run and, hopefully, process the file per the advice above.
At the end of the master's VBA code put a spreadsheet close once you've tested it. Keep the master accessible but read only for your users to stop them changing it.
The auto-close stops users even being aware of the master but is awkward when you're developing because you open the macro for editing and it automatically closes its' parent spreadsheet if you enable macro's. You can still edit the macro by disabling macro's and using Excel's VBA Editor.
Some examples below for your review.

Access VBA extract
'create a filename (this will be renamed when the excel macro is run)
var_file = "c:\my documents\mgmtrpt.xls"
strExcelPath = "C:\Program Files\Microsoft Office\Office\Excel.exe"
' Name of master (template) file
strExcelFile = "l:\lr\current\managementtemplate.xls"

'Transfer the data into a temporary Excel spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qry_export", var_file

' Run Excel with the right template Tony Tibbenham 2001-03-29
Shell pathname:=strExcelPath & " " & Chr(34) & _
strExcelFile & Chr(34), windowstyle:=vbMaximizedFocus


Excel VBA extract
Sub auto_open()

On Error GoTo error_handle

Dim var_last, var_all, var_value, var_date, var_file, fname, var_old_cell, var_right, var_curr

Dim i, conSecCount As Integer
Dim conBookIn As String
Dim conBookOut As String

' Read table and section title data from sheet 1
Workbooks("managementtemplate.xls").Activate
Worksheets("Sheet1").Activate

' Names of files
conBookIn = Range("a2")
conBookOut = Range("b2")

'open the temporary file
'Note - this must match the name exported from Access!! TT
Workbooks.Open FileName:=conBookIn

' Now do what you want with the file.

Thanks, Mr. Tibbs!
I am trying to follow your advice, but am a little confused by the Excel portion of your explanation.

I am trying to create the master excel file, which I am calling reporttemplate.xls. I opened the VB window and now I'm lost! I don't understand the sections where you are specifying ranges, etc. And how do I specify the name of the file in the section:

Workbooks.Open FileName:=conBookIn

That doesn't look like a file name to me!

Sorry to be so clueless!!! I am having one of those days...

conbookin is a variable which has been assigned the value of whatever was in cell a2 (that's what conBookIn = Range("a2") does).
Cell a2 needs to contain the name of your 'temporary file' e.g. "c:\temp\testfile.xls" which you've just exported from Access.
Send me your email address and i'll send you an example spreadsheet if you like.